Hello,
I wonder if someone can help me with some query syntax. Hopefully what I’m trying to attempt is possible.
I’m using telegraf to ping a number of endpoints and I’d like to build a dashboard in grafana that shows a summary table of online and offline components (from the last 5 minutes) grouped by an ‘account’ tag which shows who the endpoint belongs to.
This is what I’ve come up with so far
select losscount,okcount from (select count(packetloss) as losscount from (select mean(“percent_packet_loss”) as packetloss from “ping” where time > now() - 5m group by “name”) where packetloss = 100 group by account), (select count(packetloss2) as okcount from (select mean(“percent_packet_loss”) as packetloss2 from “ping” where time > now() - 5m group by “name”) where packetloss2 < 100 group by account) group by account
Unfortunately, this results in some cases 2 row per account as follows:
time losscount okcount
1970-01-01T00:00:00Z 4
1970-01-01T00:00:00Z 13
Ideally, I am after one row instead of two.
I’ve also tried the following but it returns no data for the okcount column
select count(losscount),count(okcount) from (select packetloss as losscount from (select mean(“percent_packet_loss”) as packetloss from “ping” where time > now() - 5m group by “name”) where packetloss = 100 group by account), (select countpacketloss2 as okcount from (select mean(“percent_packet_loss”) as packetloss2 from “ping” where time > now() - 5m group by “name”) where packetloss2 < 100 group by account) group by account
Any pointers would be greatly appreciated.
Thanks
