help on stats command

[jip31]

hello

I use the search below in order to count a number of events by SITE
If I search a specific site (example | search SITE= TUTU) it works
If I search with star (example SITE=T*) it works too but I have to add | stats sum after | stats dc(host) by SITE in order to agregare the count of the different sites
My probleme is here because when I am doing stats sum, I lose the field SITE while I need to keep it
What I have to do for not having this problem please?

index="toto"
| lookup it.csv HOSTNAME as host output SITE 
| search SITE=T* 
| stats dc(host) by SITE 
| fields - SITE

[skoelpin]

For a performance boost, you should use stats before lookup

[woodcock · Accepted solution]

Like this:

index="toto"
| lookup it.csv HOSTNAME as host output SITE 
| search SITE=T* 
| stats count dc(host) AS hosts BY SITE

Then either:

| eventstats sum(count) AS TotalCount sum(hosts) AS TotalHosts

OR

| addtotals row=f col=t
| fillnull value="GrandTotals" SITE

[jip31]

good thanks

[jip31]

last question
I want to display only "GrandTotals" in a single panel value
what I have to do please??

[to4kawa]

at the end,
table GrandTotals *

[jip31]

Its what I have done but every count for every site are always displayed

[woodcock]

Ah, I see., then do this instead:

| addtotals row=t col=t fieldname="Grand Total"
| fillnull value="Grand Total"
| tail 1
| table "Grand Total"

[gcusello]

Hi @jip31,
you have to define the exact use case:
if you wanto to display the full number of sites, e.g. in a single value panel, you can use something like this:

 index="toto"
 | lookup it.csv HOSTNAME as host output SITE 
 | search SITE=T* 
 | stats dc(SITE) AS SITE 

If instead you need to display the list of sites and the cont of different hosts for each one you can use the first stats command and add at the end the command addtotals, something like this:

index="toto"
| lookup it.csv HOSTNAME as host output SITE 
| search SITE=T* 
| stats dc(host) AS hosts by SITE 
| addtotals labelfield=hosts hosts

Ciao.
Giuseppe

[jip31]

Hi Giuseppe
The first solution is not useful because I want to count the host by SITE and not to count the number of SITE...
And the second solution is not good, its the same result that in my example
I need to keep the field SITE available even if I am doing :

| stats dc(host) by SITE 
| stats sum

[gcusello]

Hi @jip31,
sorry but I don't understand:
you want the list of SITEs and for ech the number of distinct hosts
then in the last row, you want the sum of hosts (that comes from the dc option in stats command), what do you want in addition? the number of SITEs?
if this is your need, try something like this:

index="toto"
 | lookup it.csv HOSTNAME as host output SITE 
 | search SITE=T* 
 | stats dc(host) AS hosts by SITE 
 | addtotals labelfield=hosts label="Total" hosts
 | eventstats dc(SITE) AS dcSITE
| eval SITE=if(SITE="Total","Total: ".dcSITE,SITE)
| fields - dcSITE

Ciao.
Giuseppe