Splunk Search

help on stats command

jip31
Motivator

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
Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

skoelpin
SplunkTrust
SplunkTrust

For a performance boost, you should use stats before lookup

0 Karma

woodcock
Esteemed Legend

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
0 Karma

jip31
Motivator

good thanks

0 Karma

jip31
Motivator

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

0 Karma

to4kawa
Ultra Champion

at the end,
table GrandTotals *

0 Karma

jip31
Motivator

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

woodcock
Esteemed Legend

Ah, I see., then do this instead:

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

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

jip31
Motivator

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
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...