Microsoft SQL Server template variables
Instead of hard-coding details such as server, application, and sensor names in metric queries, you can use variables. Grafana lists these variables in dropdown select boxes at the top of the dashboard to help you change the data displayed in your dashboard. Grafana refers to such variables as template variables.
For an introduction to templating and template variables, refer to the Templating and Add and manage variables documentation.
Query variable
If you add a template variable of the type Query
, you can write a MS SQL query that can
return things like measurement names, key names or key values that are shown as a dropdown select box.
For example, you can have a variable that contains all values for the hostname
column in a table if you specify a query like this in the templating variable Query setting.
SELECT hostname FROM host
A query can return multiple columns and Grafana will automatically create a list from them. For example, the query below will return a list with values from hostname
and hostname2
.
SELECT [host].[hostname], [other_host].[hostname2] FROM host JOIN other_host ON [host].[city] = [other_host].[city]
Another option is a query that can create a key/value variable. The query should return two columns that are named __text
and __value
. The __text
column value should be unique (if it is not unique then the first value is used). The options in the dropdown will have a text and value that allow you to have a friendly name as text and an id as the value. An example query with hostname
as the text and id
as the value:
SELECT hostname __text, id __value FROM host
You can also create nested variables. For example, if you had another variable named region
. Then you could have
the hosts variable only show hosts from the current selected region with a query like this (if region
is a multi-value variable, then use the IN
comparison operator rather than =
to match against multiple values):
SELECT hostname FROM host WHERE region IN ($region)
Using variables in queries
From Grafana 4.3.0 to 4.6.0, template variables are always quoted automatically so if it is a string value do not wrap them in quotes in where clauses.
From Grafana 5.0.0, template variable values are only quoted when the template variable is a
multi-value
.
If the variable is a multi-value variable then use the IN
comparison operator rather than =
to match against multiple values.
There are two syntaxes:
$<varname>
Example with a template variable named hostname
:
SELECT
atimestamp time,
aint value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in($hostname)
ORDER BY atimestamp
[[varname]]
Example with a template variable named hostname
:
SELECT
atimestamp as time,
aint as value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in([[hostname]])
ORDER BY atimestamp
Disabling Quoting for Multi-value Variables
Grafana automatically creates a quoted, comma-separated string for multi-value variables. For example: if server01
and server02
are selected then it will be formatted as: 'server01', 'server02'
. To disable quoting, use the csv formatting option for variables:
${servers:csv}
Read more about variable formatting options in the Variables documentation.
Was this page helpful?
Related resources from Grafana Labs
![Getting started with the Grafana LGTM Stack](/static/assets/featured/getting-started-with-grafana-lgtm-stack-amer_featured.png?w=200 200w, /static/assets/featured/getting-started-with-grafana-lgtm-stack-amer_featured.png?w=400 400w)
![Intro to Kubernetes monitoring in Grafana Cloud](/media/webinar/Screenshot-2024-02-12-at-4.09.30-PM.png?w=200 200w, /media/webinar/Screenshot-2024-02-12-at-4.09.30-PM.png?w=400 400w)
![Building advanced Grafana dashboards](/media/webinar/AMER_buildadv_notxt.png?w=200 200w, /media/webinar/AMER_buildadv_notxt.png?w=400 400w)