Important: This documentation is about an older version. It's relevant only to the release noted, many of the features and functions have been updated or replaced. Please view the current version.

Documentation Grafana documentation Data sources Microsoft SQL Server Template variables

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.

sql Copy

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.

sql Copy

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:

sql Copy

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):

sql Copy

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:

sql Copy

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:

sql Copy

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.