sqlgrafanainfluxdb

InfluxDB + Grafana: Status codes by time

I am trying to display HTTP status codes in Grafana by time. The data is stored in InfluxDB like this (simplified):

# time,status,bytesize
2024-04-27 11:24:00, 404,100
2024-04-28 01:24:00, 200,100
...

Currently I am using the SQL for querying:

SELECT $__dateBin(time) AS time,
COUNT(CASE WHEN "status" BETWEEN 200 AND 299 THEN 1 END) AS "2xx",
COUNT(CASE WHEN "status" BETWEEN 400 AND 499 THEN 1 END) AS "4xx",
COUNT(CASE WHEN "status" >= 500 THEN 1 END) AS "5xx"
FROM http_requests
GROUP BY $__dateBin(time)

This produces the following graph: enter image description here

As you can see the values are displayed correctly but points are directly connected and the Graph doesn't go back to the baseline (0).

How can I fix this?

Solution

  • I was able to achieve empty rows by utilising InfluxDB's date_bin_gapfill function. It can be used with the $__interval variable from Grafana:

    SELECT
  date_bin_gapfill($__interval, time) as _time,
  COUNT(CASE WHEN "status" BETWEEN 200 AND 299 THEN 1 END) AS "2xx",
  COUNT(CASE WHEN "status" BETWEEN 400 AND 499 THEN 1 END) AS "4xx",
  COUNT(CASE WHEN "status" >= 500 THEN 1 END) AS "5xx"
FROM "http_requests"
WHERE
    time >= $__timeFrom
    AND time <= $__timeTo
GROUP BY _time, status
ORDER BY _time

    Then I had to select the option to interpolate missing values with 0: enter image description here

    And this made the graph look as expected.