topic Re: help on stats command in Splunk Search

help on stats command

jip31 — Fri, 13 Dec 2019 08:52:19 GMT

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

Re: help on stats command

gcusello — Fri, 13 Dec 2019 09:16:39 GMT

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

Re: help on stats command

jip31 — Fri, 13 Dec 2019 09:27:57 GMT

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

Re: help on stats command

gcusello — Fri, 13 Dec 2019 10:22:45 GMT

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

Re: help on stats command

woodcock — Fri, 13 Dec 2019 10:29:59 GMT

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

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

Re: help on stats command

jip31 — Fri, 13 Dec 2019 12:02:48 GMT

good thanks

Re: help on stats command

jip31 — Fri, 13 Dec 2019 12:37:01 GMT

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

Re: help on stats command

to4kawa — Fri, 13 Dec 2019 13:11:43 GMT

at the end,
table GrandTotals *

Re: help on stats command

jip31 — Fri, 13 Dec 2019 13:20:21 GMT

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

Re: help on stats command

woodcock — Fri, 13 Dec 2019 15:02:09 GMT

Ah, I see., then do this instead:

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

Re: help on stats command

skoelpin — Fri, 13 Dec 2019 15:10:36 GMT

For a performance boost, you should use stats before lookup