Understanding rolling counts

Grafana
1

I’m trying to understand the best way to do a rolling count --where always have a count of records/measurements in the last x minutes from now (last 5 minutes, last 30 minutes, etc).

Somehow, all I’m able to do is create fixed periods and getting the count of records in those periods, rather than the actual rolling count. I’ve attached a screenshot showing the table data so you can see how it’s splitting the counts (ping.counts in this case) to 15 minute periods, rather than showing the actual value I’m looking for. I tried to include the query options and the query so that all of my selections are in the screenshot.

Any tips? Thank you!

grafana_count_issue
grafana_count_issue2718×1626 209 KB

2

Hello @fhhowdy,
What version are you using? Are you using Flux or SQL?
In Flux there are functions like:

But I don’t believe that exists for count…

In SQL you get fixed counts with:

SELECT 
    DATE_BIN(INTERVAL '5 minutes', time, TIMESTAMP '2000-01-01T00:00:00Z') AS bin_time, 
    COUNT(*) AS count
FROM ping
WHERE time > now() - INTERVAL '30 minutes'
GROUP BY bin_time
ORDER BY bin_time ASC;

But to get a rolling count you would do something like:

SELECT 
    time, 
    (SELECT COUNT(*) 
     FROM ping AS p2 
     WHERE p2.time BETWEEN p1.time - INTERVAL '5 minutes' AND p1.time
    ) AS rolling_count
FROM ping AS p1
WHERE time > now() - INTERVAL '30 minutes'
ORDER BY time ASC;

I do believe. I haven’t tested it though. This might also be a good question for the grafana community.

3

I’m using the influxdb datasource in grafana for the connection. For the backend, I’m using OSS v1, as I’m hoping upgrade to v3 and the docs say to stick to v1 for now.

I wasn’t sure if this was more of a grafana question or influxdb question, since it appears that the query was succesfully getting data, but aggregating/binning incorrectly. I’ve only used influx for time-series charts in the past, so creating a single metric is completely new to me.

4

I wanted to follow up on this, as the original reply did get me on the correct path. It took me a while to figure out I could click the pencil icon and enter in a direct SQL query. Once I got past that, the syntax didn’t exactly work for my v1 database, but the following is one of the queries I used for the finished dashboard.

SELECT count(rtt_ms) AS count
FROM ping
WHERE time > now() - 60m AND timeout = false;

Thank you Anaisdg!