NOTE: I am using datasets provided by Hailie Shaw's "Splunk: Zero to Power User" Udemy course. My results will vary from your own test and live production datasets. Do not perform the queries in this article on equipment and services you do not own, have permission, or do not fully understand what the query does.

Common SPL Commands

Here are some command SPL commands that you will typically use in your queries to narrow down events in your searches.

 Source: SPL2 Command Quick Reference

stats

Calculates aggregate statistic over the incoming search results set.

eval	Calculates an expression and puts the resulting value into a search results field.

table

Displays selected fields in a table format

rex	Command to extract fields using regular expression named groups. (Can also used sed!)

where

Filters search results based on the outcome of a Boolean expression.

top

Shows most common values in a field

stats

The stats command calculates aggregate statistics, such as average, count, and sum, over the incoming search results set. In this example we are using stats to count by how many events are logged for different source IP addresses have been logged in our security index, and then sorting them from most to least. Here we can see that IP address 87[.]194[.]216[.]51 has 730 event logs.

 

We can confirm this by running a new query specifying to show us events where the source IP address is 87[.]194[.]216[.]51.

 

eval

The eval command calculates an expression and puts the resulting value into a search results field. We can use the eval command to create new fields, this is useful when you want to perform calculations based on event details and store them. For example, we want to count all failed authentications and store a value of "Failed" or "Successful".

1) Find all events in the security index that has the src field value of 87[.]194[.]216[.]51.

2) Create a new field called "authentication" that calculates if the event contains the string "Failed". If it does store the string value of "Failed" in the new authentication field. If not store the string "Successful" instead. 

3) Use the stats command to count by the source IP address and our newly created field to easily identify if an IP address had any successful or unsuccessful authentication attempts.

 

 

 We can see that IP address 87[.]194[.]216[.]51 does not have any successful authentication attempts and failed and successfully logging into our machines.

table

Displays selected fields in a table format. We can use the table command to further confirm there were no events in our data where source IP addresses gained access. In the example below we create a table showing source IP addresses, sum of failed, successful and total authentication attemps. Although tables show the same results, using the tables command allows us to easily generate reporting data to include in documents for clients or organizations.

1) Search for all events in the security index.

2) Create a new field called "authentication" that calculates if the event contains the string "Failed". If it does store the string value of "Failed" in the new authentication field. If not store the string "Successful" instead. 

3) Store the count values of our new field as a two separate new fields "Successful, and "Failed" based on the source IP address (we do not want to sum other failed or successful attempts of other IP addresses).

4)  Calculate the total of Failed and Successful and store as a new field that sums all authentication attempts.

5) Create a table that shows us the columns of the source IP address, Successful attempts, Failed attempts, and total authentication attemps

6) Sort in descending order (greatest to least) of Failed attempts.

 

rex

Extracts fields using regular expressions.This is very important when working on Splunk event logs that have a lot of content but is not supported by add-ins, and does not have useful parsing to separate event information. A real world example is our linux_secure logs indicate whether a username is valid or not in authentication attempts but does not store it as a field. This is valuable information and can reveal username enumeration via generic wordlists or leaked information if an attacker is using only valid usernames. The only fields linux_secure logs show:

Example of two events that indicate invalid and valid username authentication that have failed.

 The first event states that authentication failed attempt to invalid username mailman, while the second one shows us a failed attempt to valid username root. Lets create a new field that indicates valid or invalid usernames in our failed authentication logs. Keep in mind linux_secure logs will have logs pertaining sessions and we will want to filter it out by specifying we are only looking for failed authentication events.

1) Search in the security index, and show us only results that contain the string "Failed password" to ensure we are only looking at authentication logs.

2) Use rex to parse through the content of the event that begins with "Failed password for" and extract the text "invalid user" if it exists. Also store the username as a new field although it already exists

3) If the string "invalid user" exists make the value for the new field "valid_user" true, and if not store as false.

4) Create a table showing source IP address, host, user, username(newly created field to test extraction of username is successful), valid_user, and the raw content of the event.

Using rex we can create context based fields and values enriching our data and making it easier for analysts to read and comprehend without having to manually parse raw event logs.

where

Filters search results based on the outcome of a Boolean expression.We can use the where command to further narrow our search to only shows us events where valid user names were used.

1) Search in the security index, and show us only results that contain the string "Failed password" to ensure we are only looking at authentication logs.

2) Use rex to parse through the content of the event that begins with "Failed password for" and extract the text "invalid user" if it exists. Also store the username as a new field although it already exists

3) If the string "invalid user" exists make the value for the new field "valid_user" true, and if not store as false.

4) Create a table showing source IP address, host, user, username(newly created field to test extraction of username is successful), valid_user, and the raw content of the event.

5) Show only results where the field valid_user has the value of "true".

top 

Shows most common values in a field. In this last example we are further modifying our query to then show us what are the top usernames that were marked as valid users in failed authentication attempts.

1) Search in the security index, and show us only results that contain the string "Failed password" to ensure we are only looking at authentication logs.

2) Use rex to parse through the content of the event that begins with "Failed password for" and extract the text "invalid user" if it exists. Also store the username as a new field although it already exists

3) If the string "invalid user" exists make the value for the new field "valid_user" true, and if not store as false.

4) Create a table showing source IP address, host, user, username(newly created field to test extraction of username is successful), valid_user, and the raw content of the event.

5) Show only results where the field valid_user has the value of "true".  

6) Sort the results by user and return the percentage of results, and total sum of events they make up.