Get count of days where value is greater than zero

Grafana InfluxDB
1

Hello everyone,

I record in a Grafana database how many updates are not installed on our servers.

I would now like to know in a period of e.g. 30 days on how many days the value was not 0.

I already have a query which gives me a value for each day.
SELECT max("value") FROM "Invoke-IcingaCheckUpdates" WHERE ("hostname"::tag =~ /^$hostname$/ AND "metric"::tag = 'security::ifw_updates::count' AND value > 0 AND time > now() - 30d) GROUP BY time(1d)

I then get the following table:

UpdateData
UpdateData278Ɨ521 3.7 KB

However, I now lack the idea of how I can count the days, does anyone of you have an idea?

Regards
Marco

2

welcome @marcok

what is your datasource?

3

Oh sorry, I forgot to add that.

Iā€™m using InfluxDB 1.6.7~rc0 and Grafana in Version v10.4.2.

4

Use subquery - you already have list of days, so now you can just sum max field (or count max field values) and you will have that count.

5

how many days = count.

but you have max which is not count. max is biggest value

your issue is more influxql query than grafana

6

Thanks that was the solution I did not realize that InfluxDB also supports subquerys.

SELECT count(max) 
FROM (
  SELECT max("value") 
  FROM "Invoke-IcingaCheckUpdates" 
  WHERE 
    "hostname"::tag =~ /^$hostname$/ AND 
    "metric"::tag = 'security::ifw_updates::count' AND
    "value"::field > 0 AND 
    time > now() - 30d 
  GROUP BY time(1d)
)

Thanks

1 Like