Search events against a lookup table and show matching count

[jerrad]

Hey Guys,

I am trying to figure out an approach to a problem I have, I have my firewall sending logs to splunk which include a policy ID number. I also have a lookup table which contains all of the policy ID's I have provisioned in my firewall. What I want to do is basically search my events for the policy ID's in the lookup table and report the number of times that policy ID showed up in my splunk events.

The intent here is to audit firewall policies, find out which ones are provisioned but are never used since they never generate a log entry in splunk. It's a mechanized approach to policy sanitation so we can clean up unused firewall policies.

Is this possible? A worst case scenario would be to do a search like "host=firewall policy_id=12 OR policy_id=13 policy_id=12 policy_id=13 etc.... and report which search terms came back with 0 results.

Thanks in advance

Jerrad

[gkanapathy · Accepted solution]

| inputlookup policyidlist 
| search NOT [ sourcetype=fw policy_id=* 
               | top limit=0 policy_id 
               | fields policy_id ]

will sort of work but if you have a lot of fw data, it's possible that the subsearch may time out with the default limits. You can also do:

sourcetype=fw policy_id=* 
| inputlookup append=t policyidlist 
| stats count by policy_id 
| eval count=count-1 
| sort count

and look at the ones that have a count of 0. (We subtract 1 from the count, because appending the lookup table adds one of each policy_id, assuming the lookup table contains a single row for each policy_id and the field is called policy_id.) If not:

sourcetype=fw policy_id=* 
| append  [ inputlookup policyidlist 
            | dedup pol_id
            | rename pol_id as policy_id ] 
| stats count by policy_id 
| eval count=count-1 
| sort count

is nearly the same.

[jerrad]

This worked, thank you very much, I ended up using the non subsearch method due to the high number of events.