I’d like some help on the below requirement. Suppose I’d like to know the number of metrics(count) I received from a set of hosts in the last 24 hours .
select count(*) from system where time > now() - 24h group by host
This query will not return a group for those hosts that have not sent data in the last 24 hours . I’d like to know how I could use something like fill() , so that I could return a count of 0 for those “host” which did not send any data in the last 24 hours.
Hello @prashanthjbabu,
Yes, you can use fill (please reference docs here).
I believe fill(0) is what you want.
Hello @Anaisdg,
Thanks for your reply ! I noticed that fill(0) only seems to work when I group by time . Here group by is a tag which is host and whenever I do a group by tag and place a time limit ( now() - 24h) . I only get hosts that have sent data in the last 24h, other hosts do not show up with count 0
SELECT will never return series that have no data in the time range, so the count() function wouldn’t be involved.
You may query all hosts using SHOW TAG VALUES [FROM “measurement”] WITH KEY=“host”
and fill the zeros in your application (externally).
Thanks a lot for your reply ! I guess doing it at the application level is the only way . It gets a lot trickier though when you plan to support limit/offset in your application level so as to provide pagination support . Then plugging in these zero values based on a sort order become tricky and expensive!
