Splunk Search

Show results of stats count when result is 0

tromero3
Path Finder

I have a search using stats count but it is not showing the result for an index that has 0 results. There is two columns, one for Log Source and the one for the count.   I'd like to  show the count of EACH index, even if there is 0 result. 

example

log source  count
A                    20
B                    10
C                     0

 

index=A or index=B or index=C 
| eval "Log Source"=case(index == "A", "indexA", index == "B", "indexB", index == "C", "IndexC")
| stats count by "Log Source"

 

 

 

Labels (3)
0 Karma
1 Solution

yeahnah
Communicator

Hi @tromero3 

You can hard code the log source list to the end of your results.  Using your initial example query something like this should work.

index=A or index=B or index=C 
| eval "Log Source"=case(index == "A", "indexA", index == "B", "indexB", index == "C", "IndexC")
| stats count by "Log Source"
 `comment("# ensure all log sources listed")`
| append [| makeresults | eval indexA="", indexB="", indexC="" | table indexA indexB indexC | transpose column_name="Log Source" ]
| stats max(count) AS count BY "Log Source"
| fillnull value=0 count

 Hope this helps.

View solution in original post

jorjiana88
Path Finder

fillnull

0 Karma

tromero3
Path Finder

already tried that, it doesn't work

0 Karma

to4kawa
Ultra Champion
| tstats count where index=A OR index=B OR index=C
| append [|makeresults 
| eval index=split("ABC",""), count=0 | mvexpand index| table index count]
| dedup index
| eval "Log Source"=case(index == "A", "indexA", index == "B", "indexB", index == "C", "IndexC")
| table "Log Source" count

your sample is "index=A" , so it's difficult to use split() for actually log, I guess. you can do it.

0 Karma

tromero3
Path Finder

Im confused about the split part, is that just combining the name of each index into one? (where you put 

("ABC","")

 And why is that part needed?

My index names are actually longer of course and with dashes in the name such as "first-index", "second-index', etc 

Also the first part of my search is longer not just the individual index. But more like

(index=first-index event_type=security) OR (index=second-index rule_reason=IPblock)

and so on...

0 Karma

to4kawa
Ultra Champion

You asked what to do if you don't have index ABC, so I answered, but the conditions are totally different.

I can't answer this. Good luck.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Have you considered appending a dummy event for each log and then subtracting 1 from every count?

0 Karma

yeahnah
Communicator

Hi @tromero3 

You can hard code the log source list to the end of your results.  Using your initial example query something like this should work.

index=A or index=B or index=C 
| eval "Log Source"=case(index == "A", "indexA", index == "B", "indexB", index == "C", "IndexC")
| stats count by "Log Source"
 `comment("# ensure all log sources listed")`
| append [| makeresults | eval indexA="", indexB="", indexC="" | table indexA indexB indexC | transpose column_name="Log Source" ]
| stats max(count) AS count BY "Log Source"
| fillnull value=0 count

 Hope this helps.

tromero3
Path Finder

wow that worked perfectly, thank you! 😀

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...