How to display count as zero when no events are returned

[493600]

I have three lookup files and I am trying to find out which one has a zero count.

Below is the query I am using.

| inputlookup file_intel
| inputlookup append=true ip_intel
| inputlookup append=true http_intel
| search threat_key=*risklist_hrly*
| stats count by threat_key

I want to know which threat_key has a zero count for threat_key=*risklist_hrly*. I have tried fillnull, its not working.

I can only see the one that has count. I want to get the one that has zero count.

[493600 · Accepted solution]

Hello,

I have got the solution to this. We need to first create results and initialize the count as 0. it will create one table with 4 rows. Then join that with the other lookup files. Below is the query that I have used:

| makeresults
| eval threat_key="p_default_domain_risklist_hrly"
| eval count=0
| append
[| makeresults
| eval threat_key="p_default_hash_risklist_hrly"
| eval count=0 ]
| append
[| makeresults
| eval threat_key="p_default_ip_risklist_hrly"
| eval count=0]
| append
[| makeresults
| eval threat_key="p_default_url_risklist_hrly"
| eval count=0 ]
| fields - _time
| append
[| inputlookup ip_intel
| search threat_key=*risklist_hrly*
| stats count by threat_key ]
| append
[| inputlookup file_intel
| search threat_key=*risklist_hrly*
| stats count by threat_key
]
| append
[| inputlookup http_intel
| search threat_key=*risklist_hrly*
| stats count by threat_key ]
| stats sum(count) as count by threat_key
| search count=0

[gcusello]

Hi @493600 ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[493600]

@gcusello 

Thanks for your response but this doesn't work. Its making all the count 0 when we add | eval count=0

[ITWhisperer]

| inputlookup file_intel
| stats count BY threat_key
| eval lookup=1
| append [ | inputlookup ip_intel | stats count BY threat_key | eval lookup=2 | fields threat_key lookup ]
| append [ | inputlookup http_intel | stats count BY threat_key | eval lookup=4 | fields threat_key lookup ]
| stats sum(lookup) AS mask BY threat_key
| search threat_key=*risklist_hrly*

if mask is 7 the key is in all files

if it is 6 it is in ip_intel and http_intel

if it is 5 it is in file_intel and http_intel

etc.

[gcusello]

Hi @493600,

please try this:

| inputlookup file_intel
| stats count BY threat_key
| eval count=0
| append [ | inputlookup ip_intel | eval count=0 | fields threat_key count ]
| append [ | inputlookup http_intel | eval count=0 | fields threat_key count ]
| stats sum(count) AS total BY threat_key
| search threat_key=*risklist_hrly*

if in all lookups there's a field called "threat_key".

Ciao.

Giuseppe