help on stats by

[jip31]

hi

I need to understand why I execute the first search I have much more events in "Number of CPU alerts" count than in the second search?
As you can see, the first search stats the data "by host SITE" while the second stats the data only by host
What I dont understand is that every host has is proper SITE so normaly like I am doing the same kind of count i should have the same result?
Thanks for your help

 `CPU` 
| stats count(process_cpu_used_percent)  as "Number of CPU alerts" by host SITE 
 | search host=TUTU

[jip31 · Accepted solution]

In fact the problem is that in the CMDB where I pick up the field "SITE" (I use a lookup not visible in my example) there is always 3 events for the same host even if its the same SITE!
So when I am doing just "by host" I have the good results but when I am doing "by host SITE" I have three times more events!
If "by host" I have 3 events, by "host SITE" I have 9 events
So like I need to use "by host SITE" how to avoid this side effects??

[gcusello]

Hi @jip31,
at first, if the relation between host and SITE is 1:1 you don't neel to put it in the BY clause of the stats but you can put it in values(SITE) AS SITE.

Anyway, you can check it inserting dc in stats

`CPU` 
| stats dc(SITE) AS dc_SITE count(process_cpu_used_percent)  as "Number of CPU alerts" by host
| where dc_SITE>1

in this way you can check if there are hosts with more than one SITE.
Then you have to decide if it's acceptable for you to have more SITEs for each host or not.

Ciao,
Giuseppe

[gcusello]

Hi @jip31,
it would be easy for me to suggest that you update the CMDB based on the indications obtained from the search by eliminating duplicates and probably would be the best approach.
Alternatively, you need to decide which path to follow:

• BY host
• BY host SITE

Let me know if I can help you in another way.

Ciao.
Giuseppe