Remove whitelisted events from Splunk search results

Splunk is a great tool to monitor and review many different kinds of log files. In some cases it can occur that you want to be alerted of specific types of events, but only for occurrences of the events that are not defined in an exceptions whitelist. For example: you may want to be alerted of all logons to your server by users who are not IT employees. While it is possible to enumerate all exceptions in the search query, this article shows how this can be done by storing all exceptions in a CSV-file. 


Throughout this article we'll use the example of logons to servers. Let's assume that the following initial search 

EventCode=4624 | table _time, EventCode, Account_Name, ComputerName 

returns the following results:
  
_time EventCode Account_Name ComputerName
2015-05-06 08:48:18 4624 lisa dbserver
2015-05-06 08:44:03 4624 homer fileserver
2015-05-06 08:43:57 4624 lisa ldapserver
2015-05-06 07:32:15 4624 bart webserver
2015-05-06 07:32:15 4624 marge webserver


In our example Lisa is a database administrator, so she is allowed to log on to the database server. She isn't allowed to log on to other servers however. Bart is a web developer, so he is allowed to log on to the web server. As explained in the introduction, it is possible to define these exceptions in the search query as follows: 

EventCode=4624 | where (NOT (Account_Name="lisa" ComputerName="dbserver")) OR (NOT (Account_Name="bart" ComputerName="webserver")) | table _time, EventCode, Account_Name, ComputerName 
As you can see, the query will grow significantly for any organization and the overview will be quickly lost.

So let's define all exceptions in a CSV file:

EventCode,Account_Name,ComputerName
4624,lisa,dbserver
4624,bart,webserver

In order to use this CSV file in Splunk, you have to define it as a lookup file. You have to do this as follows:

  1. Log on to the Splunk web interface, and browse to Settings and then select Lookups. The direct path is the following: https://splunkserver:8000/en-US/manager/search/lookups
  2. Click on Lookup table files
  3. Click on New
  4. As Destination app, select search. Select the CSV file that you want to upload, and type a destination filename. The destination filename will later be used in the search query. In our example, we'll upload our file as server_logon_whitelist.csv
  5. Click on Save and the CSV file will be available for use in search queries.

What we want to do now is correlate the search results with the entries in the CSV-file. We will configure our search query so that we will only see search results that are not defined in the CSV-file. The final search query will be as follows:

EventCode=4624
 | join type=left EventCode, Account_Name, ComputerName [| inputlookup server_logon_whitelist.csv append=t | eval whitelisted="true"]
 | where NOT whitelisted="true"
 | table _time, EventCode, Account_Name, ComputerName

The search query is constructed as follows:

  1. We start by selecting all events that have EventCode 4624.
  2. We will then join the search results with the CSV-file. We will do a left-join on the EventCode, Account_Name and ComputerName fields. We do a left join in order to also keep the events in the search results for which no match is found in the CSV-file. Joining with the CSV file is done with the inputlookup command.
  3. We also define a new field "whitelisted". This field is only set to "true" in search results for which a match will be found in the CSV-file. This means that our search result will be as follows:
    _time EventCode Account_Name ComputerName whitelisted
    2015-05-06 08:48:18 4624 lisa dbserver true
    2015-05-06 08:44:03 4624 homer fileserver
    2015-05-06 08:43:57 4624 lisa ldapserver
    2015-05-06 07:32:15 4624 bart webserver true
    2015-05-06 07:32:15 4624 marge webserver
  4. In order to not see the permitted (or whitelisted) results, we add a where clause that filters out the search results of which the field whitelisted is set to true.

The final search result of the query will be the following:

 
_time EventCode Account_Name ComputerName
2015-05-06 08:44:03 4624 homer fileserver
2015-05-06 08:43:57 4624 lisa ldapserver
2015-05-06 07:32:15 4624 marge webserver


If the list of permitted server logons changes in your organization, all you have to do is update the CSV-file and upload it again to Splunk. Once the file is uploaded in Splunk, you could also simply update the CSV file directly on the Splunk server without having to upload it again through the web interface. The may even allow you to re-create the CSV-file automatically by dumping some information from Active Directory.

Tags: 

You might also be interested in...