mirror of
https://github.com/grafana/grafana.git
synced 2025-07-27 17:02:09 +08:00
115 lines
4.5 KiB
Markdown
115 lines
4.5 KiB
Markdown
---
|
|
aliases:
|
|
- ../../data-sources/mssql/template-variables/
|
|
description: Using template variables with Microsoft SQL Server in Grafana
|
|
keywords:
|
|
- grafana
|
|
- MSSQL
|
|
- Microsoft
|
|
- SQL
|
|
- Azure SQL Database
|
|
- templates
|
|
- variables
|
|
- queries
|
|
labels:
|
|
products:
|
|
- cloud
|
|
- enterprise
|
|
- oss
|
|
menuTitle: Template variables
|
|
title: Microsoft SQL Server template variables
|
|
weight: 400
|
|
refs:
|
|
variables:
|
|
- pattern: /docs/grafana/
|
|
destination: /docs/grafana/<GRAFANA_VERSION>/dashboards/variables/
|
|
- pattern: /docs/grafana-cloud/
|
|
destination: /docs/grafana/<GRAFANA_VERSION>/dashboards/variables/
|
|
variable-syntax-advanced-variable-format-options:
|
|
- pattern: /docs/grafana/
|
|
destination: /docs/grafana/<GRAFANA_VERSION>/dashboards/variables/variable-syntax/#advanced-variable-format-options
|
|
- pattern: /docs/grafana-cloud/
|
|
destination: /docs/grafana/<GRAFANA_VERSION>/dashboards/variables/variable-syntax/#advanced-variable-format-options
|
|
add-template-variables:
|
|
- pattern: /docs/grafana/
|
|
destination: /docs/grafana/<GRAFANA_VERSION>/dashboards/variables/add-template-variables/
|
|
- pattern: /docs/grafana-cloud/
|
|
destination: /docs/grafana/<GRAFANA_VERSION>/dashboards/variables/add-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](ref:variables) and [Add and manage variables](ref:add-template-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
|
|
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
|
|
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
|
|
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
|
|
SELECT hostname FROM host WHERE region IN ($region)
|
|
```
|
|
|
|
## Using variables in queries
|
|
|
|
> 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
|
|
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
|
|
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](ref:variable-syntax-advanced-variable-format-options) documentation.
|