Conexión a un origen de datos de Microsoft SQL Server

Utilice el origen de datos de Microsoft SQL Server (MSSQL) para consultar y visualizar datos de cualquier servidor de Microsoft SQL Server 2005 o posterior, lo que incluye Microsoft Azure SQL Database.

importante

La versión 8.0 de Grafana cambia la estructura de datos subyacente de los marcos de datos de Microsoft SQL Server, Postgres y MySQL. Como consecuencia, el resultado de una consulta de serie temporal se devuelve en un formato ancho. Para más información, consulte Wide format en la documentación de los marcos de datos de Grafana.

Para que las visualizaciones funcionen como antes, es posible que tenga que hacer algunas migraciones manuales. Una solución está documentada en GitHub en Postgres/MySQL/MSSQL: Breaking change in v8.0 related to time series queries and ordering of data column.

Adición del origen de datos

1. Elija el icono de Grafana en el encabezado superior para abrir el menú lateral.

2. En el menú lateral, en el vínculo Configuración, encontrará el enlace orígenes de datos.

3. Elija el botón + Agregar origen de datos en el encabezado superior.

4. Seleccione Microsoft SQL Server en la lista desplegable Tipo.

Opciones de origen de datos

Nombre	Descripción
Name	El nombre del origen de datos. Así es como se ve el origen de datos en los paneles y las consultas.
Default	El origen de datos predeterminado significa que se preseleccionará para los nuevos paneles.
Host	La dirección IP o el nombre de host y el puerto opcional de su instancia de MSSQL. Si se omite el puerto, se usará el puerto 1433 predeterminado.
Database	Nombre de la base de datos MSSQL.
User	Nombre de usuario o inicio de sesión de la base de datos.
Password	Contraseña del usuario de la base de datos.
Encrypt	Esta opción determina si se negociará una conexión SSL TCP/IP segura con el servidor, o en qué medida. El valor predeterminado es false (Grafana v5.4 o posteriores).
Max open	El número máximo de conexiones abiertas a la base de datos. El valor predeterminado es unlimited (Grafana v5.4 o posteriores).
Max idle	El número máximo de conexiones en el grupo de conexiones inactivas. El valor predeterminado es 2 (Grafana v5.4 o posteriores).
Max lifetime	La cantidad máxima de tiempo en segundos que se puede reutilizar. El valor predeterminado es 14400/4 horas.

Intervalo de tiempo mínimo

Un límite inferior para las variables $_interval $_interval_ms. Se recomienda que se establezca según la frecuencia de escritura, por ejemplo, 1m si los datos se escriben cada minuto. Esta opción también se puede anular o configurar en un panel, en las opciones de orígenes de datos. Este valor debe tener el formato de un número seguido de un identificador de tiempo válido, por ejemplo, 1m (1 minuto) o 30s (30 segundos). Se admiten los siguientes identificadores de tiempo.

Identificador	Descripción
y	Año
M	Mes
w	Semana
d	Día
h	Hora
m	Minuto
s	Segundo
ms	Milisegundos

Permisos de usuario de base de datos

importante

Al usuario de la base de datos que especifique al agregar el origen de datos solo se le deben conceder los permisos SELECT en la base de datos y las tablas especificadas que se quiere consultar. Grafana no valida que la consulta sea segura. La consulta puede incluir cualquier instrucción SQL. Por ejemplo, se ejecutarán instrucciones como DELETE FROM user; y DROP TABLE user;. Para evitar esto, le recomendamos crear un usuario de MSSQL específico con permisos restringidos.

El siguiente código de ejemplo muestra la creación de un usuario de MSSQL específico con permisos restringidos.

 CREATE USER grafanareader WITH PASSWORD 'password'
 GRANT SELECT ON dbo.YourTable3 TO grafanareader

Asegúrese de que el usuario no reciba ningún permiso no deseado del rol público.

Problemas conocidos

Si utiliza una versión anterior de Microsoft SQL Server, como 2008 y 2008 R2, es posible que tenga que deshabilitar el cifrado para poder conectarse. Si es posible, le recomendamos que utilice el último paquete de servicio disponible para lograr una compatibilidad óptima.

Editor de consultas

Encontrará el editor de consultas de MSSQL en la pestaña de métricas del modo de edición del panel de gráficos, de valor único o de tablas. Para entrar en el modo de edición, elija el título del panel y, a continuación, elija Editar. El editor le permite definir una consulta SQL para seleccionar los datos que se van a visualizar.

1. Seleccione Formatear como Time series (para usarlo en los paneles de gráficos o de valor único, entre otros) o Table (para usarlo en el panel de tablas, entre otros).

2. Este es el editor real en el que se escriben las consultas SQL.

3. Muestre la sección de ayuda para MSSQL debajo del editor de consultas.

4. Muestre la consulta SQL que se ejecutó. Estará disponible solo después de que se haya ejecutado correctamente la consulta.

5. Agregue una consulta adicional en la que se muestre un editor de consultas adicional.

Macros

Para simplificar la sintaxis y permitir partes dinámicas, como los filtros de intervalo de fechas, la consulta puede contener macros.

Ejemplo de macros	Descripción
$__time(dateColumn)	Se sustituirá por una expresión para cambiar el nombre de la columna a time. Por ejemplo, dateColumn as time.
$__timeEpoch(dateColumn)	Se sustituirá por una expresión para convertir un tipo de columna DATETIME en una marca de tiempo de Unix y cambiarle el nombre a time. Por ejemplo, DATEDIFF(second, “1970-01-01”, dateColumn) AS time.
$__timeFilter(dateColumn)	Se sustituirá por un filtro de intervalo de tiempo con el nombre de columna especificado. Por ejemplo, dateColumn BETWEEN "2017-04-21T05:01:17Z" AND "2017-04-21T05:06:17Z".
$__timeFrom()	Se sustituirá por el inicio de la selección de tiempo actualmente activa. Por ejemplo, “2017-04-21T05:01:17Z”.
$__timeTo()	Se sustituirá por el fin de la selección de tiempo actualmente activa. Por ejemplo, “2017-04-21T05:06:17Z”.
$__timeGroup(dateColumn,'5m'[, fillvalue])	Se sustituirá por una expresión utilizable en la cláusula AGRUPAR POR. Si se proporciona un valor fillValue NULO o flotante, se rellenarán automáticamente las series vacías en un intervalo de tiempo con ese valor. Por ejemplo, CAST(ROUND(DATEDIFF(second, “1970-01-01”, time_column)/300.0, 0) as bigint)*300.
$__timeGroup(dateColumn,'5m', 0)	Igual que el anterior, pero con un parámetro de relleno, por lo que Grafana agregará los puntos que falten en esa serie y usará el valor 0.
$__timeGroup(dateColumn,'5m', NULL)	Igual que el anterior, pero se usará NULO como valor para los puntos faltantes.
$__timeGroup(dateColumn,'5m', previous)	Igual que el anterior, pero el valor anterior de esa serie se utilizará como valor de relleno si no se ha visto ningún valor, pero se utilizará NULL (solo disponible en Grafana 5.3+).

El editor de consultas tiene un vínculo SQL generativo que aparece una vez ejecutada la consulta, en el modo de edición de panel. Si lo elige, se ampliará y mostrará la cadena SQL interpolada sin procesar que se ejecutó.

Consultas de tablas

Si la opción de consulta se establece en Formatear comotabla, básicamente puede hacer cualquier tipo de consulta SQL. El panel de tablas mostrará automáticamente los resultados de las columnas y filas que devuelva la consulta.

En el siguiente código de ejemplo se muestra una tabla de una base de datos.

CREATE TABLE [event] (
  time_sec bigint,
  description nvarchar(100),
  tags nvarchar(100),
)

CREATE TABLE [mssql_types] (
  c_bit bit, c_tinyint tinyint, c_smallint smallint, c_int int, c_bigint bigint, c_money money, c_smallmoney smallmoney, c_numeric numeric(10,5),
  c_real real, c_decimal decimal(10,2), c_float float,
  c_char char(10), c_varchar varchar(10), c_text text,
  c_nchar nchar(12), c_nvarchar nvarchar(12), c_ntext ntext,
  c_datetime datetime,  c_datetime2 datetime2, c_smalldatetime smalldatetime, c_date date, c_time time, c_datetimeoffset datetimeoffset
)

INSERT INTO [mssql_types]
SELECT
  1, 5, 20020, 980300, 1420070400, '$20000.15', '£2.15', 12345.12,
  1.11, 2.22, 3.33,
  'char10', 'varchar10', 'text',
  N'☺nchar12☺', N'☺nvarchar12☺', N'☺text☺',
  GETDATE(), CAST(GETDATE() AS DATETIME2), CAST(GETDATE() AS SMALLDATETIME), CAST(GETDATE() AS DATE), CAST(GETDATE() AS TIME), SWITCHOFFSET(CAST(GETDATE() AS DATETIMEOFFSET), '-07:00')

En el siguiente código de ejemplo se muestra una consulta.

SELECT * FROM [mssql_types]

Puede controlar el nombre de las columnas del panel Tabla mediante la sintaxis de selección de columnas de SQL AS normal, como se muestra en el siguiente código de ejemplo.

SELECT
  c_bit as [column1], c_tinyint as [column2]
FROM
  [mssql_types]

El panel de tabla resultante:

Consultas de series temporales

Si establece Formatear como en Serie temporal, para su uso en el panel de gráficos, por ejemplo, la consulta debe tener una columna con el nombre time que devuelva una fecha y hora de SQL o cualquier tipo de datos numéricos que representen la época de Unix en segundos. Puede devolver una columna con el nombre metric que se utilice como nombre de métrica para la columna de valores. Cualquier columna excepto time y metric se trata como una columna de valores. Si omite la columna metric, el nombre de la columna de valores será el nombre de la métrica. Puede seleccionar columnas de valores múltiples, cada una tendrá su nombre como métrica. Si devuelve columnas de valores múltiples y una columna denominada metric, esta columna se utilizará como prefijo para el nombre de la serie.

Los conjuntos de resultados de las consultas de serie temporal se deben ordenar por tiempo.

En el siguiente código de ejemplo se muestra una tabla de una base de datos.

CREATE TABLE [event] (
  time_sec bigint,
  description nvarchar(100),
  tags nvarchar(100),
)

CREATE TABLE metric_values (
  time datetime,
  measurement nvarchar(100),
  valueOne int,
  valueTwo int,
)

INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 12:30:00', 'Metric A', 62, 6)
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 12:30:00', 'Metric B', 49, 11)
...
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 13:55:00', 'Metric A', 14, 25)
INSERT metric_values (time, measurement, valueOne, valueTwo) VALUES('2018-03-15 13:55:00', 'Metric B', 48, 10)

El siguiente código de ejemplo muestra una columna value y una columna metric.

SELECT
  time,
  valueOne,
  measurement as metric
FROM
  metric_values
WHERE
  $__timeFilter(time)
ORDER BY 1

Si la consulta anterior se utiliza en un panel de gráficos, se generan dos series denominadas Metric A y Metric B con los valores valueOne y valueTwo representadas sobre time.

El siguiente código de ejemplo muestra varias columnas value.

SELECT
  time,
  valueOne,
  valueTwo
FROM
  metric_values
WHERE
  $__timeFilter(time)
ORDER BY 1

Si la consulta anterior se utiliza en un panel de gráficos, se generan dos series denominadas Metric A y Metric B con los valores valueOne y valueTwo representadas sobre time.

El siguiente código de ejemplo muestra el uso de la macro $__timeGroup.

SELECT
  $__timeGroup(time, '3m') as time,
  measurement as metric,
  avg(valueOne)
FROM
  metric_values
WHERE
  $__timeFilter(time)
GROUP BY
  $__timeGroup(time, '3m'),
  measurement
ORDER BY 1

Si la consulta anterior se utiliza en un panel de gráficos, se generan dos series denominadas Metric A y Metric B con los valores valueOne y valueTwo representadas sobre time. Si hay dos series sin un valor en un plazo de tres minutos, se mostrará una línea entre esas dos líneas. Se dará cuenta de que la gráfica de la derecha nunca baja a cero.

El siguiente código de ejemplo muestra el uso de la macro $__timeGroup con el parámetro de rellenado establecido en cero.

SELECT
  $__timeGroup(time, '3m', 0) as time,
  measurement as metric,
  sum(valueTwo)
FROM
  metric_values
WHERE
  $__timeFilter(time)
GROUP BY
  $__timeGroup(time, '3m'),
  measurement
ORDER BY 1

Cuando se utiliza esta consulta en un panel de gráficos, el resultado son dos series denominadas Metric A y Metric B con la suma de valueTwo representada sobre time. Cualquier serie que no tenga un valor en un plazo de 3 minutos tendrá un valor de cero, que verá representado en el gráfico de la derecha.

Plantillas

En lugar de codificar elementos como el nombre del servidor, la aplicación y el sensor en las consultas de métricas, puede utilizar variables en su lugar. Las variables se muestran como cuadros de selección desplegables en la parte superior del panel de control. Puede usar estos cuadros desplegables para cambiar los datos que se muestran en su panel de control.

Para obtener más información acerca de las plantillas y sus variables, consulte Plantillas y variables.

Variable de consulta

Si agrega una variable de plantilla del tipo Query, puede escribir una consulta de MSSQL que devuelva elementos como nombres de mediciones, nombres de clave o valores de clave que se muestran en un cuadro de selección desplegable.

Por ejemplo, puede tener una variable que contenga todos los valores de la columna hostname en una tabla si especifica una consulta como esta en la configuración de consulta de la variable de plantilla.

SELECT hostname FROM host

Una consulta puede devolver varias columnas y Grafana creará automáticamente una lista a partir de ellas. Por ejemplo, la siguiente consulta devolverá una lista con los valores de hostname y hostname2.

SELECT [host].[hostname], [other_host].[hostname2] FROM host JOIN other_host ON [host].[city] = [other_host].[city]

Otra opción es una consulta que puede crear una variable de clave-valor. La consulta debe devolver dos columnas denominadas __text y __value. El valor de la columna __text debe ser único (si no lo es, se utilizará el primer valor). Las opciones de la lista desplegable tendrán un texto y un valor que le permiten asignar un nombre descriptivo como texto y un identificador como valor. Una consulta de ejemplo con hostname como texto y id como valor:

SELECT hostname __text, id __value FROM host

También puede crear variables anidadas. Por ejemplo, si tuviera otra variable llamada region. A continuación, puede hacer que la variable de hosts muestre solo los hosts de la región actualmente seleccionada con una consulta como esta (si region es una variable de valores múltiples, utilice el operador de comparación IN en lugar de = para que coincida con valores múltiples).

SELECT hostname FROM host WHERE region IN ($region)

Uso de variables en consultas

nota

Los valores de las variables de plantilla solo se especifican entre comillas cuando la variable de plantilla es multi-value.

Si se trata de una variable de varios valores, utilice el operador de comparación IN en lugar de = para que coincida con varios valores.

Existen dos sintaxis:

Ejemplo de $<varname> con una variable de plantilla llamada hostname:

SELECT
  atimestamp time,
  aint value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in($hostname)
ORDER BY atimestamp

Ejemplo de [[varname]] con una variable de plantilla llamada hostname:

SELECT
  atimestamp as time,
  aint as value
FROM table
WHERE $__timeFilter(atimestamp) and hostname in([[hostname]])
ORDER BY atimestamp

Desactivación de las comillas para variables de varios valores

Grafana crea automáticamente una cadena entrecomillada y separada por comas para variables de varios valores. Por ejemplo, si server01 y server02 se seleccionan, se formateará como 'server01', 'server02'. Para desactivar las comillas, use la opción de formato CSV para las variables.

${servers:csv}

Para obtener más información sobre las opciones de formato de variables, consulte Plantillas y variables.

Anotaciones

Puede utilizar las anotaciones para superponer información detallada de los eventos sobre los gráficos. Las consultas de anotación se agregan a través del menú Panel o la vista Anotaciones. Para obtener más información, consulte Annotations.

Columnas:

Nombre	Descripción
time	El nombre del campo de fecha y hora. Puede ser una columna con un tipo de datos de fecha y hora nativo de SQL o un valor de época.
timeend	El nombre opcional del campo de fecha y hora de finalización. Puede ser una columna con un tipo de datos de fecha y hora nativo de SQL o un valor de época.
text	Campo de descripción del evento.
tags	Nombre de campo opcional para utilizar en las etiquetas de eventos como una cadena separada por comas.

En el siguiente código de ejemplo se muestran tablas de bases de datos.

CREATE TABLE [events] (
  time_sec bigint,
  description nvarchar(100),
  tags nvarchar(100),
)

También utilizamos la tabla de base de datos definida en Consultas de series temporales.

En el siguiente código de ejemplo, se muestra una consulta que utiliza una columna de tiempo con valores de época.

SELECT
  time_sec as time,
  description as [text],
  tags
FROM
  [events]
WHERE
  $__unixEpochFilter(time_sec)
ORDER BY 1

En el siguiente código de ejemplo, se muestra una consulta de región que utiliza columnas de tiempo y tiempo de fin con valores de época.

SELECT
  time_sec as time,
  time_end_sec as timeend,
  description as [text],
  tags
FROM
  [events]
WHERE
  $__unixEpochFilter(time_sec)
ORDER BY 1

En el siguiente código de ejemplo, se muestra una consulta que utiliza una columna de tiempo del tipo de datos de fecha y hora nativo de SQL.

SELECT
  time,
  measurement as text,
  convert(varchar, valueOne) + ',' + convert(varchar, valueTwo) as tags
FROM
  metric_values
WHERE
  $__timeFilter(time_column)
ORDER BY 1

Compatibilidad con procedimientos almacenados

Se ha comprobado que los procedimientos almacenados funcionan. Sin embargo, puede haber casos extremos en los que no funcione como cabría esperar. Los procedimientos almacenados deben ser compatibles con las consultas de tablas, series temporales y anotaciones, siempre que se utilice el mismo nombre de las columnas y se devuelvan los datos en el mismo formato descrito anteriormente en las secciones correspondientes.

Las funciones de las macros no funcionarán dentro de un procedimiento almacenado.

Ejemplos

En los ejemplos siguientes, la tabla de base de datos se define en las consultas de serie temporal. Supongamos que quiere visualizar cuatro series en un panel de gráficos, como todas las combinaciones de columnas valueOne, valueTwo y measurement. El panel de gráficos de la derecha representa lo que queremos lograr. Para resolver esto, debe utilizar dos consultas:

En el siguiente código de ejemplo se muestra la primera consulta.

SELECT
  $__timeGroup(time, '5m') as time,
  measurement + ' - value one' as metric,
  avg(valueOne) as valueOne
FROM
  metric_values
WHERE
  $__timeFilter(time)
GROUP BY
  $__timeGroup(time, '5m'),
  measurement
ORDER BY 1

En el siguiente código de ejemplo se muestra la segunda consulta.

SELECT
  $__timeGroup(time, '5m') as time,
  measurement + ' - value two' as metric,
  avg(valueTwo) as valueTwo
FROM
  metric_values
GROUP BY
  $__timeGroup(time, '5m'),
  measurement
ORDER BY 1

Procedimiento almacenado que utiliza el tiempo en formato de época

Puede definir un procedimiento almacenado que devuelva todos los datos que necesite para representar cuatro series en un panel de gráficos como el anterior. En este caso, el procedimiento almacenado acepta dos parámetros @from y @to, de los tipos de datos int, que deben ser un intervalo de tiempo (de-a) en formato de época que se utilizará para filtrar los datos que se devolverán del procedimiento almacenado.

Esto imita a $__timeGroup(time, '5m') en las expresiones seleccionar y agrupar por, de modo que se necesitan numerosas expresiones largas. Si se desea, se pueden extraer a funciones de MSSQL.

CREATE PROCEDURE sp_test_epoch(
  @from int,
  @to   int
)   AS
BEGIN
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int) as time,
    measurement + ' - value one' as metric,
    avg(valueOne) as value
  FROM
    metric_values
  WHERE
    time >= DATEADD(s, @from, '1970-01-01') AND time <= DATEADD(s, @to, '1970-01-01')
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int),
    measurement
  UNION ALL
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int) as time,
    measurement + ' - value two' as metric,
    avg(valueTwo) as value
  FROM
    metric_values
  WHERE
    time >= DATEADD(s, @from, '1970-01-01') AND time <= DATEADD(s, @to, '1970-01-01')
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, DATEADD(second, DATEDIFF(second,GETDATE(),GETUTCDATE()), time))/600 as int)*600 as int),
    measurement
  ORDER BY 1
END

A continuación, puede utilizar la siguiente consulta para el panel de gráficos.

DECLARE
  @from int = $__unixEpochFrom(),
  @to int = $__unixEpochTo()

EXEC dbo.sp_test_epoch @from, @to

Procedimiento almacenado que utiliza el tiempo en formato de fecha y hora

Puede definir un procedimiento almacenado que devuelva todos los datos que necesite para representar cuatro series en un panel de gráficos como el anterior. En este caso, el procedimiento almacenado acepta dos parámetros @from y @to, de los tipos de datos datetime, que deben ser un intervalo de tiempo (de-a) que se utilizará para filtrar los datos que se devolverán del procedimiento almacenado.

Esto imita a $__timeGroup(time, '5m') en las expresiones seleccionar y agrupar por, de modo que se necesitan numerosas expresiones largas. Si se desea, se pueden extraer a funciones de MSSQL.

CREATE PROCEDURE sp_test_datetime(
  @from datetime,
  @to   datetime
)   AS
BEGIN
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int) as time,
    measurement + ' - value one' as metric,
    avg(valueOne) as value
  FROM
    metric_values
  WHERE
    time >= @from AND time <= @to
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int),
    measurement
  UNION ALL
  SELECT
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int) as time,
    measurement + ' - value two' as metric,
    avg(valueTwo) as value
  FROM
    metric_values
  WHERE
    time >= @from AND time <= @to
  GROUP BY
    cast(cast(DATEDIFF(second, {d '1970-01-01'}, time)/600 as int)*600 as int),
    measurement
  ORDER BY 1
END

A continuación, puede utilizar la siguiente consulta para el panel de gráficos.

DECLARE
  @from datetime = $__timeFrom(),
  @to datetime = $__timeTo()

EXEC dbo.sp_test_datetime @from, @to

Alertas

Las consultas de serie temporal deben funcionar en condiciones de alertas. Las consultas con formato de tabla aún no se admiten en las condiciones de las reglas de alerta.