Percentage of boolean value over time - Grafana

Grafana
,
1

Hello, this might be more of a Grafana question and im a total newbie at Influxdb. Im trying to get a percentage of a boolean value over time using influxdb. Similar to this question:
(Pie Chart, SQL, Percentage of boolean Value over Time - Pie Chart Panel - Grafana Labs Community Forums)

This is the querry i am using currently, but i need to convert “reachable” from TRUE/FALSE to 0/1 to get the boolean value over time.

SELECT mean("reachable") FROM "hostalive" WHERE ("hostname" =~ /^$variablename$/) AND $timeFilter GROUP BY time($__interval), "metric" fill(none)

Any help would be appreciated.

2

not sure this will work… it also relies heavily on Grafana transformations:

  1. Get two separate values (with two different queries, you may need to use the manual editor)
    1.1 Query to get amount of false values SELECT count ("reachable") AS "Offline" FROM "hostalive" WHERE "reachable" = 0 AND {....}
    1.2 Query to get amount of true values SELECT count("reachable") AS "Online" FROM "hostalive" WHERE "reachable" = 1 AND {....}
  2. Apply the merge Transform to obtain one single series (teh timestamp must coincide, is the “join” key
  3. use the add column from calculation transform (multiple times)
    3.1 Calculate the total of the interval (Online + Offline)
    3.2 Calculate the two percentages
  4. Plot the values

Note that if you are creating a pie chart or KPI there is no need to group by time, as there is no time axis… just ensure the “time” column of both queries has the same value so the merge can work, without time grouping it should be the 0 date (1970-01-01 …)

3

hello if we the counting false value, no satisfied situation, we get "no result " in grafana, normally we should get “0” in this case, we can not show the percentage as your method, do you know how to solve this, really appreciated.

4

@0803xiaoqiao

If you are using Flux, do any of these solutions help?

Count how long a value is 1 or 0 (on or off)
Calculating total ON or OFF time for a period (day, month, etc.).
Computing duration of a state using events.duration function