# 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.

Hi, I’m back from vacation now and been looking into it again. Your suggestion with grouping works with DB-command. I’ve tested it in the GUI and still have issues. I probably do it wrong and probably also explained it wrong.
So I will try again from beginning.
If I have a graph like following;

And the issue I have is that I under ‘Project Total CPU Cost’ want calculate hosts together from each hosts underneath. So red ring should be a sum of blue rings.
Don’t know if this made it more understandable.

I have brain fog right now, but it looks like the regular expression is not doing what you expected. Look at the actual queries generated by Grafana. I notice that your 84.55 is not 84.41 (almost).
You have the hosts hard-coded in the dashboard, that’s not what I meant. There’s the option to make the row automatically repeat for the series in your data. So you would code the clause in the panel like this (assuming the variable is “hostname” - it comes automatically in the editor pull-down)

``````where (host =~ /^\$hostname\$/)
``````

Then you set the row to repeat on “hostname” and it will do that all. For your “total” you just need to sum over all series (without the where clause).

Rob

I have repeat on the row and on “hostname”.
The issue I have is the Total on the top.
On repeat part I have following query:
SELECT last(“physical_consumed”) *\$CPUprice AS CostCPU FROM “cpu_util” WHERE (“host” =~ /^HOST/) AND timeFilter GROUP BY time(__interval) fill(none)
And as you describe I should do sum of the series without the Where-part and it’s here I don’t get it.
This doesn’t work SELECT sum(“physical_consumed”) *\$CPUprice AS CostCPU FROM “cpu_util” as it gives a number that is totally wrong.
I understand that I need to get the series in, but don’t know how to put it in the query.

So your metric is a ‘counter’ in that it adds something every interval, right? And you want the increment over the selected time period? How about this:

``````select sum(non_negative_derivative(physical_consumed, 1h)) from cpu_util
``````

It gets you the usage per hour, and then you sum those over the period you’re looking at.

If you want the total since the beginning of time, you’d do a nested query with last() like you had for the repeated panels (hope I get it right, don’t have the data to try)

``````select sum(host_total) from (select last(physical_consumed) as host_total from cpu_util group by (host))
``````

You do need to plant time ranges in, or it will be expensive to compute.

Yes, it adds something every interval. I get it correct in the field that is repeating.
I tried your example above but I don’t get it to work. Need to check with someone here that knows more about DB-querys, as I’m not a DB-person.
The issue is that I want to have one or several hosts(repeating fields) summarized in to one under Total.
I do values by day, week (and also month and year, only not included in the example above).
I tested with panel Singlestat Math and there it works, but it’s manual job to add a query per host. Want to be able to pick from a scrolling list.

I just hate when I give solutions that don’t seem to work for people

So I created some fake data like this:

`````` > select * from usage limit 20
name: usage
time                cpu disk host net
----                --- ---- ---- ---
1593561600000000000 9   28   aaa  7
1593561600000000000 8   10   ccc  9
1593561600000000000 9   18   bbb  16
1593565200000000000 18  25   ccc  19
1593565200000000000 22  39   bbb  27
1593565200000000000 17  59   aaa  16
1593568800000000000 33  57   bbb  39
1593568800000000000 30  81   aaa  42
1593568800000000000 30  39   ccc  36
1593572400000000000 40  100  aaa  54
1593572400000000000 38  48   ccc  55
1593572400000000000 42  77   bbb  52
1593576000000000000 52  77   ccc  61
1593576000000000000 56  97   bbb  75
1593576000000000000 49  118  aaa  64
1593579600000000000 65  120  bbb  88
1593579600000000000 60  135  aaa  85
1593579600000000000 63  99   ccc  73
1593583200000000000 66  145  aaa  100
1593583200000000000 69  122  ccc  85
``````

The single stat on the right has the last CPU value that host:

``````SELECT last("cpu") FROM "usage" WHERE ("host" =~ /^\$host\$/) AND \$timeFilter GROUP BY time(\$__interval) fill(null)
``````

The panel in the top row has the total over these, like this (you have to put that query in by hand):

``````select sum(total) from (SELECT last("cpu") as total FROM "usage" WHERE \$timeFilter GROUP BY  host)
``````

Oh, and the charts are with a query like this:

``````SELECT non_negative_derivative(mean("cpu"), 1h) FROM "usage" WHERE ("host" =~ /^\$host\$/) AND \$timeFilter GROUP BY time(\$__interval), "host" fill(null)
``````

I hoped it would have helped, It looked so promising. But I don’t get it to work.

Now I only have one host selected. And I don’t get it to work.
The query I put in for those that is marked with red is:
select sum(total) from (SELECT last(“physical_consumed”) AS total FROM “cpu_util” WHERE \$timeFilter GROUP BY Hostname)
For those not marked red has the old query. And that looks more correct to me.
The panel to the right should be the same as top panel if I only have one host.
And if I choose more hosts the new value doesn’t change.
I really don’t know what I do wrong.

Ah, I thought you wanted the total over all hosts, not just the selected ones… Here you go:

and for just one host:

I’ve attached the gzipped dashboard.
usage.gz (2.1 KB)

You are correct, I want it for several hosts that I can pick from a picking list. But If I choose one host it should match between lower and upper result. As you have in the last(the one you’ve zipped.
I don’t know what it is but I don’t get it to work.
I’ve attached a gzip version of my dashboard.
TEST3 Issue Cost per host-1597049998693.json.gz (3.0 KB)

There’s a mismatch in the tag names. What is the tag for your host name in the database? The variable refers to “host” but the query suggests “Hostname”. Further, the variable you define is “HOST” but the queries refer to “host” so there’s something fishy.
Also note that the query to populate the variable has the key name without quotes, like this:

``````SHOW TAG VALUES WITH KEY = Hostname where os = 'AIX'
``````

I have a feeling you’re just getting all records because the filtering is referring to something that doesn’t exist.

Thanks for finding that out. I think I’ve corrected all. But I still doesn’t get it to work.
I’ve attached a new json and hope it looks better.
TEST3 Issue Cost per host-1597066010693.json.gz (3.0 KB)

I think you’re on the wrong track with your design. When you have a “counter” value and want to show the “today” and “this week” totals, you can’t use the `last()` function as that is usage since start of time.

What I would do is to use this:

``````non_negative_derivative(sum("physical_consumed"),1h)
``````

That will give you the hourly usage for the selected host(s) over the select period. You probably would like to use that for the chart as well. -Rob
PS You didn’t fix the variable query yet, I don’t think your pull-down works like this.