LookUp Table: Show event if lookup table value is NOT in the log file

[kmattern]

I have a lookup table that has the login name of customers (cs_username) and a human friendly name (Customer). It looks like this:

cs_username,Customer

rfrn1,Red Fern Main Office

rfrn2,Red Fern Madison

rfrn3,Red Fern Triana

I want to do a search on the logs and display a list of all customers that have not accessed the system in a given time frame. So that if rfrn2 did not access my system yesterday that it will show in the output but the other two will not be listed because they did access the system. Surely there is a way to do this but I’m not getting it.

[kmattern]

Ayn, I followed up on your suggestion and have a search that works perfectly, it returns only branches that have not accessed the system. It looks like this:

| inputlookup rfrnInactive.csv
| search NOT [search sourcetype="iis" /branch*/phar*/*pdf | dedup cs_username | fields cs_username]
| fields Branch, Reason

I added a new column to the lookup called Reason. This simply tells the user why some of the rfrn branches are off line.

I needed a similar search for a different group and use a different lookup. The lookup follows the same structure and uses the same field names as the first. The second search looks like this:

| inputlookup urasInactive.csv
| search NOT [search sourcetype="iis" configs.xml | dedup cs_username |fields cs_username]
| fields Branch, Reason

The second returns branches that have accessed the system as well as those that did not. If both searches are basically the same, I cannot understand why the first one works while the second one does not. Can you explain how the NOT function works?

Thanks

[Ayn]

NOT simply negates the following search term. You can see exactly what the subsearch outputs by running it on its own and append "| format" at the end:

sourcetype="iis" configs.xml | dedup cs_username |fields cs_username | format

This will show you exactly what the subsearch text will be replaced with in the outer search when the subsearch has run.

[dwaddle]

As an add-on to Ayn's answer, if you are going to be doing this over a very large time range, it would be well suited to using a lookup table to maintain longterm state. Basically, you'd schedule a search that collects over shorter time windows (say 1 day) the LAST login time for a customer and use a combination of inputlookup, dedup, and outputlookup to incrementally update that lookup table over the very long haul. Done properly, this gives you a very quick resource to look at to know the most-recent state.

Araitz demonstrates this approach and how it relates to firewall session state in a blog post at http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/

[Ayn · Accepted solution]

First get the entries from the lookup table, then filter it based on which customers you are seeing in the system logs. Let's say your lookup table is called "lookup.csv", the relevant logs have sourcetype "systemlogs" and that the field "cs_username" exists in those log events. In that case, this search should get you going:

| inputlookup lookup.csv | search NOT [search sourcetype="systemlogs" | dedup cs_username | fields cs_username]