We have a complex host lookup table which has many filtering fields in it. This lookup table is also updated daily as our hosts change.
index=os sourcetype=ps COMMAND=cron [inputlookup unix_hosts.csv AppTeam=TeamA | fields host] | stats count by host
In the example, AppTeam is one of the filter fields in the lookup table.
The ultimate goal here is to Alert when there is a host with a count of 0 for the given process, but we need to filter down the search to a specific App Team. The process being monitored is not always ubiquitous like cron is.
We do have the lookup table set up as an automatic lookup, so AppTeam is a searchable field, but the list of hosts for 'TeamA' needs to be generated independent of any of the indexed events.
Try something like this. This should give you list of hosts in TeamA which have 0 events in selected time range
index=os sourcetype=ps COMMAND=cron [inputlookup unix_hosts.csv | search AppTeam="TeamA" | fields host] | stats count by host | append [| inputlookup unix_hosts.csv | search AppTeam="TeamA" | fields host | eval count=0 ] | stats max(count) as count by host | where count=0
somesoni - your answer was great and has helped me tremendously.
I've learned a new trick now, and the following search runs slightly faster. Beginning with the inputlookup and negating the hosts with matching events in the index produces the availability alert in a fashion easier to understand for newbies.
I also threw in a ready-to-go message.
| inputlookup unix_hosts.csv | search AppTeam="TeamA" | search NOT [search index=os sourcetype=ps USER=root AND COMMAND=cron earliest=-2m@m latest=-1m@m | fields host] | eval minus_1=tostring(strftime(relative_time(now(),"-1m@m"),"%+")) | eval message=replace("cron (root) not running at minus_1","minus_1",minus_1) | fields host message
Try something like this. This should give you list of hosts in TeamA which have 0 events in selected time range
index=os sourcetype=ps COMMAND=cron [inputlookup unix_hosts.csv | search AppTeam="TeamA" | fields host] | stats count by host | append [| inputlookup unix_hosts.csv | search AppTeam="TeamA" | fields host | eval count=0 ] | stats max(count) as count by host | where count=0
Thank you.
'append' is a handy tool to have 🙂