Alerting

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

cb_usps
Explorer

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.

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

cb_usps
Explorer

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...