How to return 0 count of events from a lookup file?

[Hithere]

I'm trying to match all domains from a lookup file with a base search and get a count of the events for each one even if there are no events matching (0 count):

mysite.com  count 12
anothersite.com count 5
myothersite.com count 0

index=application sourcetype=mysource
| lookup myfile.csv Domain as <corelated event field>
| append [ | inputlookup myfile.csv | fields Domain]
| stats count as total by Domain
| fillnull value=0 total
| table total
| sort -total

If you can, please explain your answer - whether there is a syntax error, a keyword misuse, or conceptual error on my part. 

[gcusello]

Hi @Hithere,

please try something like this:

index=application WAFRuleMessage="Anomaly Exceeded" 
| eval Domain=lower(domain)
| stats count as ct_Domain by Domain
| append [ | inputlookup mylookupfile.csv | eval Domain=lower(domain), ct_Domain=0 | fields Domain AGS ENV ct_Domain ]
| stats sum(ct_Domain) as total by AGS ENV Domain
| sort AGS -ENV -count
| where total=0

Ciao.

Giuseppe

[Hithere]

This query retrieves all the lookup domains as desired, but all the total count of events for each domain is 0 which is not expected.

[gcusello · Accepted solution]

Hi @Hithere,

I was confused with another question, my answer is to have the domains with no logs.

If you want all the domains with status, you could use something like this:

index=application WAFRuleMessage="Anomaly Exceeded" 
| eval Domain=lower(domain)
| stats count as ct_Domain by Domain
| append [ | inputlookup mylookupfile.csv | eval Domain=lower(domain), ct_Domain=0 | fields Domain AGS ENV ct_Domain ]
| stats sum(ct_Domain) as total by AGS ENV Domain
| sort AGS -ENV -count
| eval status=if(total=0,"Not present","Present")
| table AGS ENV Domain status

Ciao.

Giuseppe