torsdag den 7. juni 2018

Generating a Pie Chart with Grafana and MS SQL

Struggled with this, hope it helps someone.

In Grafana, if we base a visualization on an MS SQL query, and if we set Format as to Time series, for use in Graph panel for example, then the query must return a column named time.

This can make life difficult if we need to generate a chart, for example a Pie Chart, where we don't have a need for the time-visualization as with a bar-chart, for example.

A way around this is to return an aggregate 'time' column, like in the below:


SELECT sum(exceptionCounts.hasException), exceptionCounts.title as metric, count(exceptionCounts.time) as time
FROM
(
SELECT sessionnumber, 
        operationResult, 
        startdatetime as time
  FROM  foobarDatabase.dbo.fullSessionLog
  where operationResult like '%xception%' 
    AND $__timeFilter(customDateTimeStamp)
  group by sessionnumber, operationResult
) as exceptionCounts
group by exceptionCounts.title

In the above, we execute an inner query, which returns a timestamp. In the outer query we can then aggreate - 'count' - the timestamp. And the Pie Chart should work.

Took me some time and then some to figure that one out. Hope it helps!

Buy me a coffeeBuy me a coffee

Ingen kommentarer:

Tilføj en kommentar