I am trying to run a search and alert for specific field that has a count of 0 for a 24 hour period.

[babcolee]

The field abc will list servers multiple times in the realtime log file. I created a inputlookup file (abc_servers.csv) and search to compare what is in the log file to the inputlookup. The inputlookup abc_sources.csv I created has 43 entries. However, when I run the following search I may see a stats count for 41 abc and does not list the other 2 missing with a value of 0 and thus not alert me of the 0 count. I also created an outputlookup (tmp_abc.csv) to give a base line but I am not sure how to compare it to the inputlookup to determine
if there is a 0 count

sourcetype="mysourcetype:" abc= | search [inputlookup abc_servers.csv] | stats count by abc | fillnull value=0

[DalJeanis · Accepted solution]

Here's one way...

sourcetype="mysourcetype:" abc= | stats count as reccount by abc | append 
[| inputlookup abc_servers.csv | table abc | eval reccount = 0] 
| eventstats sum(reccount) as totcount by abc
| where totcount == 0 

Here's another...

| inputlookup abc_servers.csv | table abc 
| join type=left [search sourcetype="mysourcetype:" abc= | stats count as reccount by abc ]
| where isnull(reccount)

[babcolee]

Thank you for your help that is what I needed

[micahkemp]

Try something like:

<base search> | dedup abc | append [inputlookup abclookup] | stats count BY abc | search count=1

Where abclookup has just one column:

abc
abcvalue1
abcvalue2

Edited as per correction below.

[DalJeanis]

count is never going to be 0 in that scenario, since there is always a record from the csv involved. The only thing you have to change is | search count=1, since the count will be 2 if the base search returns a record and the csv adds one.

By the way, dedup is a great idea for reducing the overhead.

[micahkemp]

Oops, good catch!