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
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)
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)
Thank you for your help that is what I needed
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.
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.
Oops, good catch!