Calculate consumtion from several hosts

I’m trying to calculate several hosts consumtion together and visualize it in Gafana. When trying one host it works fine, but when trying 2 or more hosts I’m getting confused.
I’m new to this and learning.

host1:
SELECT last(“physical_consumed”) FROM “cpu_util” WHERE (“host” =~ /^host1$/) AND time >= 1593468000000ms GROUP BY time(1h) fill(null)
name: cpu_util
time last


1593468000000000000 0.117
1593471600000000000 0.116
1593475200000000000 0.127
1593478800000000000 0.116
1593482400000000000 0.117
1593486000000000000 0.114
1593489600000000000 0.11
1593493200000000000 0.111
1593496800000000000 0.122
1593500400000000000 0.371
1593504000000000000 0.112
1593507600000000000 0.331
1593511200000000000 0.418
1593514800000000000 0.105
1593518400000000000 0.092

host2:
SELECT last(“physical_consumed”) FROM “cpu_util” WHERE (“host” =~ /^host2$/) AND time >= 1593468000000ms GROUP BY time(1h) fill(null)
name: cpu_util
time last


1593468000000000000 3.982
1593471600000000000 3.945
1593475200000000000 3.876
1593478800000000000 0.275
1593482400000000000 0.346
1593486000000000000 0.343
1593489600000000000 0.339
1593493200000000000 0.431
1593496800000000000 1.458
1593500400000000000 2.37
1593504000000000000 1.418
1593507600000000000 0.983
1593511200000000000 0.902
1593514800000000000 0.479
1593518400000000000 0.624

host1 and host2:
SELECT last(“physical_consumed”) FROM “cpu_util” WHERE (“host” =~ /^(host1|host2)$/) AND time >= 1593468000000ms GROUP BY time(1h) fill(null)
name: cpu_util
time last


1593468000000000000 0.117
1593471600000000000 0.116
1593475200000000000 0.127
1593478800000000000 0.116
1593482400000000000 0.117
1593486000000000000 0.114
1593489600000000000 0.11
1593493200000000000 0.111
1593496800000000000 1.458
1593500400000000000 2.37
1593504000000000000 0.112
1593507600000000000 0.331
1593511200000000000 0.418
1593514800000000000 0.105
1593518400000000000 0.624

Here we can see that it takes some values from each host. And if I add a third the thirds values doesn’t show.
Can someone tell me how I can get the same amount of values from each host?
I would expect it to be 14 values of each host, if I get that when I ask one host. And not that it takes 7 from each(if 2 hosts).

I’m not sure from your question whether you have the host name as a tag on the measurement. If you do that, you can groupby() to get the data reduced by host as multiple series. I like that in a stacked area chart to see who is contributing. Like this for docker metrics.
SELECT mean("active_anon") FROM "docker_container_mem" WHERE $timeFilter GROUP BY time($__interval), "container_name" fill(null)
You’ll also want to set the alias to $tag_container_name so it shows nicely in the legend.

I understand what you mean and I’ve done like that on other querys in Grafana.
What I forgott to mention above was that I wanted it presented in as Singlestat. I’ve tried Singlestat Math as well, but there you have to do one query/host and add it in another plase.
It not that funny if you have 10+ hosts you want to calculate on.

For single stat the multiple series will get you multiple single stats, so to say. The display shows the same metrics, once as time-series line chart and the other as mean() by CPU. This is Grafana 7.0 which makes it look even slicker than before.
What I like about the InfluxDB with Grafana solution is that it will repeat for the series you happen to have in the data.

It looks great, buit I had problem when I upgraded to latest Grafana 7 and the plugins.
Need to test again.
But I like what you have above.
How can I calculate all those number and make them visable in one?
Example: If I had an applikation that use several hosts and they want to now all consumtion together shown in one single stat.

You specify the ‘repeat’ in the general options of the panel, so it will repeat the entire chart for each series returned by the query.
My data has a number of tags that indicate the source of the metric (like user, host, complex). This way I can sum() with group by user to get the summary by user on any host. Or I can sum() with group by host to have the utilization by host, ignoring the user who caused it. You can also groupby host,user but to have breakdown by combination (as long as user typically run only on a single host, it doesn’t explode in the number of series).

Thanks a lot for your answer. I think I need to upgrade Grafana to latest and then test from there. It seems to have better features.
It will take some time(vacation), I will come back if I have anything more after the upgrade.

The repeating panel is way older, but Grafana 6 improved the way they replicate. It’s typically tied to variables, but you can have the variable with ‘all’ to replicate over all series.