select total cpu collectd multiple hosts

Question

I am using collectd and influx for monitoring a cluster of 200 cores.

I would like to create a gauge in grafana which is adding all load_shortterm value of all hosts to see the total usage of the cluster.

My structure looks like this :

name: load_shortterm
time                host             metric type value
----                ----             ------ ---- -----
1601891780201909599 cpu007.cluster          load 0
1601891790145618383 cpu001.cluster          load 2
1601891790163106767 cpu002.cluster          load 0.03
1601891790167701326 cpu009.cluster          load 0

So I want to have a request which will answer 2.03 in this case.

I don't understand how to get last values for each host and sum it. I tried this :

select sum(*) from load_shortterm where "host" =~ /^*.cluster/

But it returns a sum of all values.

Can you please help me ?

Thanks,

RB

Answer 1

SUM all LAST values with subquery, e.g.:

SELECT SUM(last) 
FROM (
  SELECT LAST(value) 
  FROM load_shortterm
  WHERE "host" =~ /^*.cluster/
)

Answer 2

Thanks for the reply, the subquery returns the last value and not last values for each host :

> select last(value) from load_shortterm where "host" =~ /^*.cluster/
name: load_shortterm
time                last
----                ----
1605863555866457205 0

Answer 3

With the full query it is the same result, it seems it does not take all last values but only the last one :

> select sum(last) from (select last(value) from load_shortterm where "host" =~ /^*.cluster/)

name: load_shortterm
time                last
----                ----
1605863555866457205 0