How to Summarize on distinct value?

[hvdtol]

Hello there,

I would like some help with my query.

I want to summarize 2 fields into 2 new columns

One field is unique, but the other is not
The field fhost is not unique.

I want the sum of field "cores" by unique combination of the columns    "clname" and  "fhost"

I am struggle how to do this properly and how i can use the sum unique for column "fhost"

| makeresults
| eval clname="clusterx", fhost="f-hosta", vhost="v-hosta",cores=2,cpu=1
| append [| makeresults | eval clname="clusterx", fhost="f-hosta", vhost="v-hostb" ,cores=2,cpu=1 ]
| append [| makeresults | eval clname="clusterx", fhost="f-hostb", vhost="v-hostc" ,cores=4,cpu=1 ]
| append [| makeresults | eval clname="clusterx", fhost="f-hostc", vhost="v-hostd" ,cores=6,cpu=1 ]

| eventstats sum(cpu) as total_vhost_cpus by clname

``` This is not working ```
| eventstats sum(cores) as total_fhost_cores by clname fhost

`` The output should be in table format ```

| table clname cores cpu fhost vhost  total_vhost_cpus  total_fhost_cores

Thank you in advance.

Harry

[yuanliu]

It would help if you describe what "this is not working" actually means.  What is the result you get and what is the result you expect?  What is the logic between your data and your expected result?

Using your sample data and your sample stats, this is the table

clname	cores	cpu	fhost	vhost	total_vhost_cpus	total_fhost_cores
clusterx	2	1	f-hosta	v-hosta	4	4
clusterx	2	1	f-hosta	v-hostb	4	4
clusterx	4	1	f-hostb	v-hostc	4	4
clusterx	6	1	f-hostc	v-hostd	4	6

Can you explain why this not what you expect?  What is the problem you are trying to solve using two eventstats command with raw events, not a stats?

[hvdtol]

Sorry, i was not clear. I am trying to get a sum of unique fhost by cluster.

So the outcome should be
f-hosta cores=2 f-hostb cores=4 f-hostc cores=6

2+4+6=10 in field "total_fhost_cores"

Regards, Harry

[ITWhisperer · Accepted solution]

Assuming cores relates to fhosts and cpus relates to vhosts, your data has mixed where these counts are coming from, so you need to split them out. Try something like this

| makeresults
| eval clname="clusterx", fhost="f-hosta", vhost="v-hosta",cores=2,cpu=1
| append [| makeresults | eval clname="clusterx", fhost="f-hosta", vhost="v-hostb" ,cores=2,cpu=1 ]
| append [| makeresults | eval clname="clusterx", fhost="f-hostb", vhost="v-hostc" ,cores=4,cpu=1 ]
| append [| makeresults | eval clname="clusterx", fhost="f-hostc", vhost="v-hostd" ,cores=6,cpu=1 ]
| eval fhost-{fhost}=cores
| eventstats values(fhost-*) as fhost-cores-* by clname
| eval total_fhost_cores=0
| foreach fhost-cores-*
    [| eval total_fhost_cores=total_fhost_cores + '<<FIELD>>']
| fields - fhost-*
| eventstats sum(cpu) as total_vhost_cpus by clname
| table clname cores cpu fhost vhost  total_vhost_cpus  total_fhost_cores

btw, unless you are working in base 12, 2+4+6=12 not 10!

[hvdtol]

Of course is should be 12.  I am ashamed.😏

But thank you very much

The | eval fhost-{fhost}=cores is a very nice solution
I did not know thos was possible.

Thank you for your help

Regards,

Harry