Splunk Search
Highlighted

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

Builder

I have a lookup table that has the login name of customers (csusername) 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.

Tags (1)
Highlighted

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

Legend

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]

View solution in original post

Highlighted

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

SplunkTrust
SplunkTrust

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/

Highlighted

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

Builder

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 csusername | fields csusername]
| 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 csusername |fields csusername]
| 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

0 Karma
Highlighted

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

Legend

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.