JSON API for Statistics and Gauages

Hi, im running grafana 9.2.1 and using the JSON API data source

I have plenty of experience with using grafana but only with using the MySQL and MSSQL datasources to write queries for gauages, bar graphs etc.

Currently, I’m trying to query an API endpoint returning JSON (the web application is FreshService). The datasource works fine, and I can create tables with no problems, but I’m struggling to create statistic gauges due to the lack of functionality in the API.

Basically, I want to return a count on the number of rows a query returns into a table and then use that count as a statistic.
In SQL it’s easy to SELECT COUNT(column) from TABLE.
But with the API, I can only query records (service tickets, for example) and then apply a filter only to show what I want.
I tried to use the transform tool, but I’m a little stuck. I noticed you could SUM fields using transform, but I couldn’t find anything to count the records.

Any help would be greatly appreciated!

Wellll, think I figured it out myself after having another look.
For anyone looking to do this, you need to transform the data using the “Reduce” function.
Set the mode to “series to rows” and “calculations” to count. You can filter your Metrics using the “filter by name” function.


1 Like