grafana-simple-sql-datasource

Allows querying SQL based datasources like SQL Server.

Usage

Currently the plugin requires a proxy server running to communicate with the database.

Install sqlproxyserver

• Run npm install at the dist/serverside folder to install all dependencies
• Run npm install on the plugin directory
• Run server side code dist/serverside/sqlproxyserver.js
• Test on your browser http://myserver:port/con=mssql://user:name@server/database you must get a {"status":"sucess"} response

Add new datasource Add a new datasource to Grafana and set the url to:

http://myserver:port/con=mssql://user:name@server/database

Where:

• myserver:port : Is the server where sqlproxyserver is running
• con: Specifies the sql connection string

SQL Databases

Currently supported SQL databases

SQL Server

SQL Server connection is managed by the mssqp package https://www.npmjs.com/package/mssql

Features

Following features has been implemented

Metrics

It is possible to define two different types: timeseries and table

Annotation

Annotation querires must return the following fields:

• title: Annotation header
• text: Annotation description
• tags: Annotation tags
• time: Annotation time

Notes

Time

UTC and Localtime. Currently you must specify if time returned by the query is UTC or local. The plugin will convert localtime to UTC in order to be correctly renderer.

Template

You can use $from and $to to refer to selected time period in your queries like:

select 'Metric Name' as metric, -- Use a literal or group by a column for the labels
        count(*) as hits, -- Just counting occurrences
        ts as [timestamp]
from (
    Select dbo.scale_interval(dateColumn, '$Interval') as ts -- scale datetime to $Interval (e.g. 10m)
    from myTable
    where dateColumn >= '$from' and dateColumn < '$to'
) T
group by ts
order by ts asc

MISC

scale_interval

Simple TSQL to group series by an interval

ALTER FUNCTION scale_interval 
(
    -- Add the parameters for the function here
    @dt as datetime, @interval as varchar(100)
)
RETURNS DateTime
AS
BEGIN
    DECLARE @amount int = 10

    IF  CHARINDEX('m', @interval) <> 0
    BEGIN
        SET @amount = CAST(REPLACE(@interval, 'm', '') as int)
        return dateadd(minute, datediff(mi, 0, @dt) / @amount * @amount, 0)
    END
    IF CHARINDEX('h', @interval) <> 0
    BEGIN
        SET @amount = CAST(REPLACE(@interval, 'h', '') as int)
        return dateadd(hour, datediff(hour, 0, @dt) / @amount * @amount, 0)
    END
    IF CHARINDEX('d', @interval) <> 0
    BEGIN
        SET @amount = CAST(REPLACE(@interval, 'd', '') as int)
        return dateadd(day, datediff(day, 0, @dt) / @amount * @amount, 0) 
    END
    RETURN NULL
END
GO

Thanks to

Grafana team and @bergquist

Powered by @repofunding