Using a lookup to search values that are NOT in logs

[aferone]

I've tried using info from the following 2 KB posts, but I am still having trouble:

http://splunk-base.splunk.com/answers/6646/search-events-against-a-lookup-table-and-show-matching-co...

http://splunk-base.splunk.com/answers/75916/using-splunk-to-find-unused-firewall-policies

I have a list of policy numbers in a lookup table. I want to be able to search a variable time of logs and find the values that do NOT show up in the logs, based off of the list in the lookup table.

I've used the examples in the other KB articles, but I can't get them to work.

"AllFirewallPolicies" is the name of the lookup.

"PolicyID" is the column in the lookup table I am comparing the logs to.

"policy_id" is the value in the logs I am searching. This is for a firewall, so any values that do NOT show up in the logs are unused policies we may want to eliminate.

Any help would be greatly appreciated.

Thanks!

[lguinn2]

I would do it this way

yoursearchhere
| eval policy_result = "Unused"
| join type=outer overwrite=t policy_id  [ | inputlookup AllFirewallPolicies 
                         | rename PolicyID as policy_id 
                         | eval policy_result="Used" 
                         | fields policy_id, policy_result 
                         | format maxresults = 2000]
| where policy_result = "Unused"

This will work fine as long as your lookup table does not have more than 2000 lines. You can change the maxresults up to 10500, but I don't think it can be larger. Still, that should be more than enough for firewall rules...

[Ayn]

Looks like format doesn't like the spaces between "maxresults" and "="? So that it should rather be format maxresults=2000.

[lguinn2]

Try

| format maxresults=2000

instead. (Take out the spaces around the equal sign.) I copied this from the manual, so it ought to work!

Also, I can't answer say why the other search doesn't work, but it could be that the subsearch is trying to return too many results.

[aferone]

I've seen this search in several other KB articles. Why isn't this working?

| inputlookup allfirewallpolicies | search NOT [search host=myfirewall | dedup policy_id | fields policy_id]

[aferone]

I get the following error:

Error in 'format' command: Option '=' is invalid.