[quote=“pooh, post:1, topic:16463”]
q:“SELECT sum(“currentcalls”) FROM “mqtt_consumer” WHERE (“topic” =
‘PBX/Live’) AND time >= now() - 1h GROUP BY time(1h) fill(null)” [/quote]
Can you show raw query result, please?
By “raw query result”, do you mean I should run this query in the InfluxDB
command line client, or you want me to do something in Grafana (if so, what)?
I think he probably means to select the query in the editor then click Query Inspector. Make sure the refresh rate is low or it will run it again before you have time to look at the results.
[quote=“pooh, post:1, topic:16463”]
q:“SELECT sum(“currentcalls”) FROM “mqtt_consumer” WHERE (“topic” =
‘PBX/Live’) AND time >= now() - 1h GROUP BY time(1h) fill(null)” [/quote]
Can you show raw query result, please?
By “raw query result”, do you mean I should run this query in the InfluxDB
command line client, or you want me to do something in Grafana (if so,
what)?
Right, I’ve just had the opportunity where Grafan shows me “1.5”.
Someone on the Influx list asked me to run the following query in the Influx
command line client:
SELECT currentcalls FROM “mqtt_consumer” WHERE “currentcalls” != 0 and
“currentcalls” !=1 and time >= now() - 1h limit 5
This results in nothing, therefore they are saying it’s a Grafana problem
(which makes good sense to me, given that this query tells me the database
contains nothing but 0 and 1 values, therefore summing them cannot possibly
result in “1.5”).
Which panel did you use?
This query is in a singlestat panel.
Also, based on Clanlaw’s suggestion of looking at the Query Inspector, I get
the following:
xhrStatus:“complete”
request:Object
method:“GET”
url:“api/datasources/proxy/1/query”
params:Object
db:“telegraf”
q:“SELECT sum(“totalcalls”) FROM “mqtt_consumer” WHERE (“topic” = ‘PBX/Live’)
AND time >= now() - 1h GROUP BY time(1h) fill(null)”
epoch:“ms”
data:null
precision:“ms”
response:Object
results:Array[1]
0:Object
statement_id:0
series:Array[1]
0:Object
name:“mqtt_consumer”
columns:Array[2]
0:“time”
1:“sum”
values:Array[2]
0:Array[1555509600000,2]
1:Array[1555513200000,1]
Why do you need time grouping GROUP BY time(1h) fill(null), when you want just single sum value? You have two records in the query result :
0:Array[1555509600000,2]
1:Array[1555513200000,1]
+ you singlestat is configured to calculate avg value (see Stat config) (2+1)/2=1.5
The best solution - calculate full sum on the query level -> only single value must be returned by the query. Alternative solution: configure summing (Stat: Total) in the singlestat configuration, so it will sum those two records.
Why do you need time grouping GROUP BY time(1h) fill(null), when you want
just single sum value? You have two records in the query result : ```
0:Array[1555509600000,2]
1:Array[1555513200000,1]
Simple answer - I don’t know - that’s the query which Grafana created for me
when I said I wanted the sum of the previous 1 hour’s values.
+ you singlestat is configured to calculate avg value (see Stat config)
(2+1)/2=1.5
All I can say is that it is not at all obvious from the query I have created
that an average will be calculated. The only aggregation function I can see
is “sum(totalcalls)”
The best solution - calculate full sum on the query level → only single
value must be returned by the query.
So, what should I change in the query I have set up to achieve that?
Are you saying that I should simply remove the “GROUP BY” section of the
query, which then results in the Query Inspector then showing me:
SELECT “totalcalls” FROM “mqtt_consumer” WHERE (“topic” = ‘PBX/Live’) AND
$timeFilter
?
Alternative solution: configure summing (Stat: Total) in the singlestat
configuration, so it will sum those two records.
That sounds like a workaround - if there’s a better solution I’d prefer to use
it.
I’m not saying that. I’m saying remove time grouping GROUP BY time(1h) fill(null) → you may need to update query somehow then, so it may not be so simple just deleting ‘GROUP BY time(1h) fill(null)’. Please don’t expect that I provide ready query for you - please read InfluxDB manual and develop it on your own.
Are you saying that I should simply remove the “GROUP BY” section of the
query, which then results in the Query Inspector then showing me:
SELECT “totalcalls” FROM “mqtt_consumer” WHERE (“topic” = ‘PBX/Live’) AND
$timeFilter
Urgh.
It turns out that deleting the “GROUP BY” section also quietly eliminated my
“sum()” function, but it turns out that I can put that back in again without
getting the GROUP back as well. The Query Inspector now shows me:
q:“SELECT sum(“totalcalls”) FROM “mqtt_consumer” WHERE (“topic” =
‘Tikvox/Live’) AND time >= now() - 1h”
And so far this is showing me the answer I would expect.
Alternative solution: configure summing (Stat: Total) in the
singlestat configuration, so it will sum those two records.
That sounds like a workaround - if there’s a better solution I’d prefer to
use it.
I don’t seem to be having a problem with InfluxDB.
Any manual queries I perform in the Influx client give me the results I expect.
My problem seems to be finding out how to get Grafana to create the correct
query without subtly turning it into something I didn’t intend or request
(such as an average, as you pointed out previously).
However, for the time being, the following seems to be doing what I want:
q:“SELECT sum(“totalcalls”) FROM “mqtt_consumer” WHERE (“topic” =
‘PBX/Live’) AND time >= now() - 1h”
?