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.
EventCode=4624 | table _time, EventCode, Account_Name, ComputerName
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
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:
- 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
- Click on Lookup table files
- Click on New
- 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
- 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:
- We start by selecting all events that have EventCode 4624.
- 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.
- 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
- 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:
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.