https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257807#M4778 <P>Try something like this. This should give you list of hosts in TeamA which have 0 events in selected time range</P> <PRE><CODE>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 </CODE></PRE> Wed, 23 Mar 2016 21:58:33 GMT somesoni2 2016-03-23T21:58:33Z https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257806#M4777 <P>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.</P> <PRE><CODE>index=os sourcetype=ps COMMAND=cron [inputlookup unix_hosts.csv AppTeam=TeamA | fields host] | stats count by host </CODE></PRE> <P>In the example, AppTeam is one of the filter fields in the lookup table.</P> <P>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.</P> <P>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.</P> Wed, 23 Mar 2016 21:33:39 GMT https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257806#M4777 cb_usps 2016-03-23T21:33:39Z https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257807#M4778 <P>Try something like this. This should give you list of hosts in TeamA which have 0 events in selected time range</P> <PRE><CODE>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 </CODE></PRE> Wed, 23 Mar 2016 21:58:33 GMT https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257807#M4778 somesoni2 2016-03-23T21:58:33Z https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257808#M4779 <P>Thank you.<BR /> 'append' is a handy tool to have <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 24 Mar 2016 14:13:39 GMT https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257808#M4779 cb_usps 2016-03-24T14:13:39Z https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257809#M4780 <P>somesoni - your answer was great and has helped me tremendously.<BR /> 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.<BR /> I also threw in a ready-to-go message.</P> <PRE><CODE>| 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 </CODE></PRE> Fri, 13 May 2016 15:59:42 GMT https://community.splunk.com/t5/Alerting/How-to-do-a-filtered-list-out-of-a-lookup-table/m-p/257809#M4780 cb_usps 2016-05-13T15:59:42Z