65.9K
CodeProject is changing. Read more.
Home

Generating a Pie Chart with Grafana and MS SQL

emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0 vote)

Jun 7, 2018

CPOL
viewsIcon

14791

How to generate a pie chart with Grafana and MS SQL

I 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 aggregate - 'count' - the timestamp. And the Pie Chart should work.

It took me some time to figure that one out. Hope it helps!