Subquery to calculate percentage

influxdb
#1

Hy
I got two metrics out of unbound: totalqueries and cachehits
now i like to calculate the cache hit rate in percent (cachehits/totalqueries)*100

I got two working queries:

SELECT “value” FROM “check_nrpe” WHERE (“hostname” =~ /^$hostname$/ AND “service” = ‘unbound stats’ AND “metric” = ‘total.num.cachehits’)

SELECT “value” FROM “check_nrpe” WHERE (“hostname” =~ /^$hostname$/ AND “service” = ‘unbound stats’ AND “metric” = ‘total.num.queries’)

How can i merge this queries together? Is this possible with influx DB?

SELECT (“value”/(SELECT “value” FROM “check_nrpe” WHERE (“hostname” =~ /^$hostname$/ AND “service” = ‘unbound stats’ AND “metric” = ‘total.num.queries’))*100) FROM “check_nrpe” WHERE (“hostname” =~ /^$hostname$/ AND “service” = ‘unbound stats’ AND “metric” = ‘total.num.cachehits’)

Is my query wrong, or is this not implemented with influxDB?
influxdB running on version 0.10.0 - is it too old??

Thank you very much!
BR

(Carsten Köbke) #2

See the InfluxDB docs

#3

With influxdb Client, this query is working acording to your link:

select last(“value”) as qu FROM check_nrpe where hostname=‘nsr1-cbn.nts.ch’ and metric=‘total.num.queries’;select last(“value”) as cached from check_nrpe where hostname=‘nsr1-cbn.nts.ch’ and metric=‘total.num.cachehits’

Output:

name: check_nrpe

time qu
1558528795000000000 131481

name: check_nrpe

time cached
1558528795000000000 84290

Buth throws a error in Grafana:

s is undefined

does i need some variables in my query?
It looks like the result is not compatible to Grafana, and i don’t know how to fit it to Grafana Graphs
Thank you very much!

(Carsten Köbke) #4

you can do it like this:

or

#5

Hi
Thank you very much for your explanation!
Maybe i was a bit unclear, about the real purpose of the two selected values.
My queries spit out two values of a DNS resolver: total queries and cached queries
Now i try to create a pie chart with the cache ratio - that means, i need to calculate the actual value for the pie chart as follows: (cached_queries/queries)*100 -> resulting in a percentage.

I don’t know how to create such a calulation out of my two separate queries.

Thank you very much! I appreciate your answers a lot!