show value in statistics where it doesnt exist in the logs

[msalghamdi]

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

user	count
ahmad	7
ali	4
elias	0
moayad	0

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

thanks

[richgalloway]

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/

[gcusello · Accepted solution]

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]

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