How to have grafana show number of entries per hour

Grafana MySQL
1

Im using a MySQL data source with a table that records searches from a form, I would like to have grafana to show a line graph that plots the number of searches that were done each hour. Would anyone be able to provide an example of what the query would look like to get grafana to do that? My database has 4 fields, an auto-incrementing id, text data of what was searched for, timestamp of the search, and which site the search was from.

2

The Single State makes this easy. Assuming your data source is one search per entry (and your question seemed to indicate as much):

  • Add the Single Stat Plugin
  • Edit it’s settings
  • Select the Metrics tab
  • Select your Data Source
  • For your Query (which can be blank) change the Metric to ‘Count’
  • Group by: Date Histogram, Field=timestamp (your timestamp field)
  • Interval - this is key, set it to 1h (for events per hour)

Then, select the Options tab
Under Value,Stat, select ‘Average’

When your plugin is done refreshing, you should see events per hour.

Under Options-Value-Prefix/Postfix you can add text to dress up the value as well.

We use this to track an events per second metric, and I’m also using Elasticsearch for the Data Source, but the plug-in settings should be similar.

3

Thanks, The settings for MySQL are different and it did not work however I copied the data into elastic search and used that and it worked like a charm.

4

Hi,

Using mysql something like this should work to plot searches per site and hour on a line graph:

SELECT
  $__timeGroup(<your timestamp field>,'1h') as time_sec,
  COUNT(<your id field>) as value,
  <your site name field> as metric
FROM test_data
WHERE $__timeFilter(<your timestamp field>)
GROUP BY 1, <your site name field>
ORDER BY 1

Please note that I’m using the beta release of Grafana 5.0 and the $__timeGroup macro function is not available in the latest stable v4.6.3.

See Using MySQL in Grafana documentation for further information.

Marcus

3 Likes
5

Hi,

thx, this saved me