I have a fault status that I’m monitoring and recording in my InfluxDB, the Value is true or false. I want to count the number of faults during the last X amount of time, according to the time range selected in the Grafana interface. But I cannot get the Query to work in any of the Panels.
My query works in InfluxDB CLI and the data looks like this (see picture for both):
One problem is that I cannot access the Value column of the data, only DeviceId is available:
Next, I tried to enter it manually instead of using the Query wizard in the Panel Configuration, but this didn’t work either. It seems to ignore it. It looked like this:
SELECT count(“Value”) FROM “EndPositionWarning” WHERE (“Value”=true) AND $timeFilter GROUP BY time(10m) fill(null)
But this returns “No Data” in all the visualizations (Single Stat, Graph, Bar Graph, etc.)
In the end, I would like a bar graph to show different counts of different faults to see which one is worse. But I can’t get the simple query working yet.
Any ideas? Much appreciated…
It looks like “Value” is a Boolean. (You can verify this by running “SHOW FIELD KEYS” in the Influx CLI.) I created a toy database and filled it with some Boolean values. I was able to count the number of true and false occurrences (in Grafana), using a query much like yours. Thus, your query looks correct to me.
So what else might be the issue? It could be the default data source. It could be the timestamps.
Check the “default” data source. (Is the database you want to query set as the default data source?) Try explicitly selecting the intended database in the drop down menu, instead of using “default”.
If this doesn’t work, then check the timestamps.
Try this in Grafana:
Change to “FORMAT AS Table”
Manually enter the query: SELECT LAST(“Value”) FROM “EndPositionWarning”
If data is returned: Is the timestamp within the query time range?
If data is not returned, try SELECT LAST(*) FROM “EndPositionWarning”
If none of the above solve your issue, then I’m out of ideas.
I hope this helps…
I’m still not sure what is wrong here, but its doing what I want. I still have “DeviceId” in the WHERE dropdown and do not have “VALUE” as I would like. But I was able to configure the STAT Display settings to count the true values in my database. I chose “Total” on Calculation and “.count” for Fields and then is started working the way I wished that the Query I have listed above works. If anyone knows why this is the way it is, that would be very helpful to understand the system, but at least there is a way to make it work.
Value is a field. Fields won’t automatically appear in the Query Editor’s Where Clause. (The Query Editor UI is filling the drop down list with a SHOW TAG KEYS query to InfluxDB.) That is why you only see “DeviceID” in the drop down.
One can still enter a field comparison into the Where Clause of the Query Editor UI by just typing the Field Name in the text box. I know this works for numeric comparisons. I don’t recall if it works for Booleans. (The query shown in the first post looks like the correct query–it looks like Grafana kept true a Boolean and did not convert true to a string. That should have worked…)
In any event, when I tried this, I skipped the Query Wizard. I manually typed in the query (I selected the pencil icon and typed the query into the text box). That worked.
I’m not positive I follow your description of how the STAT panel is configured. However, it sounds like it is working, because Value is a field.
Did I answer the correct question?
Yes, this answers it for me. Thanks!
