How to do a filtered list out of a lookup table?

[cb_usps]

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.

[cb_usps]

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

[somesoni2 · Accepted solution]

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

[cb_usps]

Thank you.
'append' is a handy tool to have 🙂