Splunk Search

show value in statistics where it doesnt exist in the logs

msalghamdi
Path Finder

hello Splunkers

i have a requirement where i need to show values in statistics even if it doesn't exist, for example here's my search:

index=brandprotection name IN (ali, ahmad, elias,moayad) | stats count by brand

however sometimes in the logs Elias and Moayad names isn't there but i need to have it in the table, so i need the output to be like this

 

usercount
ahmad7
ali4
elias0
moayad0

 

i need a search that would show the results like the table above.

 

 

thanks

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @msalghamdi ,

if you have the list of names to check, you can put them in a lookup (called e.g. names.csv and with one field "name") and run a search like the following:

index=brandprotection name IN (ali, ahmad, elias,moayad) 
| stats count BY name
| append [ | inputlookup names.csv | eval count=0 | fields name count ]
| stats sum(count) AS count BY name

Ciao.

Giuseppe

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Finding something that is not there is not Splunk's strong suit.  See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

---
If this reply helps you, Karma would be appreciated.
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @msalghamdi ,

if you have the list of names to check, you can put them in a lookup (called e.g. names.csv and with one field "name") and run a search like the following:

index=brandprotection name IN (ali, ahmad, elias,moayad) 
| stats count BY name
| append [ | inputlookup names.csv | eval count=0 | fields name count ]
| stats sum(count) AS count BY name

Ciao.

Giuseppe

emlin_charly
Explorer

I think @gcusello 's answer is the best scalable approach. If it's something quick and small, you could do something like this to keep it all in the SPL without needing a lookup:

 

index=brandprotection name IN (ali, ahmad, elias, moayad)
| stats count BY name
| append
    [| makeresults
    | eval name="ali, ahmad, elias, moayad"
    | eval name=split(name, ", ")
    | mvexpand name
    | eval count=0
    | fields name count]
| stats sum(count) AS count BY name

 

Get Updates on the Splunk Community!

SOC4Kafka - New Kafka Connector Powered by OpenTelemetry

The new SOC4Kafka connector, built on OpenTelemetry, enables the collection of Kafka messages and forwards ...

Your Voice Matters! Help Us Shape the New Splunk Lantern Experience

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Building Momentum: Splunk Developer Program at .conf25

At Splunk, developers are at the heart of innovation. That’s why this year at .conf25, we officially launched ...