I have a lookup table that has the login name of customers (csusername) and a human friendly name (Customer). It looks like this:
rfrn1,Red Fern Main Office
rfrn2,Red Fern Madison
rfrn3,Red Fern Triana
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]
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
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, 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?
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.