Introduction To Splunk & SPL | Portfolio (2024)

What Is Splunk?

Splunk is a highly scalable, versatile, and robust data analytics software solution known for its ability to ingest, index, analyze, and visualize massive amounts of machine data. Splunk has the capability to drive a wide range of initiatives, encompassing cybersecurity, compliance, data pipelines, IT monitoring, observability, as well as overall IT and business management.

Basic Searching

As with any language, proficiency comes with practice and experience. Find below some excellent resources to start with:

Boolean operators AND, OR, and NOT are used for more specific queries.

The search command is typically implicit at the start of each SPL query and is not usually written out. However, here's an example using explicit search syntax:

search index="main" "UNKNOWN"

By specifying the index as main, the query narrows down the search to only the events stored in the main index. The term UNKNOWN is then used as a keyword to filter and retrieve events that include this specific term.
Note: Wildcards (*) can replace any number of characters in searches and field values. Example (implicit search syntax):
```
index="main" "*UNKNOWN*"
```
This SPL query will search within the main index for events that contain the term UNKNOWN anywhere in the event data.
Fields and Comparison Operators
Splunk automatically identifies certain data as fields (like source, sourcetype, host, EventCode, etc.), and users can manually define additional fields. These fields can be used with comparison operators (=, !=, <, >, <=, >=) for more precise searches. Example:
```
index="main" EventCode!=1
```
This SPL (Splunk Processing Language) query is used to search within the main index for events that do not have an EventCode value of 1.
The fields command
The fields command specifies which fields should be included or excluded in the search results. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | fields - User
```
After retrieving all process creation events from the main index, the fields command excludes the User field from the search results. Thus, the results will contain all fields normally found in the Sysmon Event ID 1 logs, except for the user that initiated the process. Please note that utilizing sourcetype restricts the scope exclusively to Sysmon event logs.
The table command
The table command presents search results in a tabular format. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | table _time, host, Image
```
This query returns process creation events, then arranges selected fields (_time, host, and Image) in a tabular format. _time is the timestamp of the event, host is the name of the host where the event occurred, and Image is the name of the executable file that represents the process.
The rename command
The rename command renames a field in the search results. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | rename Image as Process
```
This command renames the Image field to Process in the search results. Image field in Sysmon logs represents the name of the executable file for the process. By renaming it, all the subsequent references to Process would now refer to what was originally the Image field.
The dedup command
The 'dedup' command removes duplicate events. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | dedup Image
```
The dedup command removes duplicate entries based on the Image field from the process creation events. This means if the same process (Image) is created multiple times, it will appear only once in the results, effectively removing repetition.
See Also
sql doesn't contain | Code Ease Known and fixed issues for Splunk Cloud Platform SC4S Logging and Troubleshooting Resources Red Team & Threat Actor Techniques: Exploring RID Hijacking
The sort command
The sort command sorts the search results. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | sort - _time
```
This command sorts all process creation events in the main index in descending order of their timestamps (_time), i.e., the most recent events are shown first.
The stats command
The stats command performs statistical operations. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=3 | stats count by _time, Image
```
This query will return a table where each row represents a unique combination of a timestamp (_time) and a process (Image). The count column indicates the number of network connection events that occurred for that specific process at that specific time.
However, it's challenging to visualize this data over time for each process because the data for each process is interspersed throughout the table. We'd need to manually filter by process (Image) to see the counts over time for each one.
The chart command
The chart command creates a data visualization based on statistical operations. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=3 | chart count by _time, Image
```
This query will return a table where each row represents a unique timestamp (_time) and each column represents a unique process (Image). The cell values indicate the number of network connection events that occurred for each process at each specific time.
With the chart command, you can easily visualize the data over time for each process because each process has its own column. You can quickly see at a glance the count of network connection events over time for each process.
The eval command
The eval command creates or redefines fields. Example:
```
index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | eval Process_Path=lower(Image)
```
This command creates a new field Process_Path which contains the lowercase version of the Image field. It doesn't change the actual Image field, but creates a new field that can be used in subsequent operations or for display purposes.
The rex command
The rex command extracts new fields from existing ones using regular expressions. Example:
```
index="main" EventCode=4662 | rex max_match=0 "[^%](?<guid>{.*})" | table guid
```
- index="main" EventCode=4662 filters the events to those in the main index with the EventCode equal to 4662. This narrows down the search to specific events with the specified EventCode.
- rex max_match=0 "[^%](?<guid>{.*})" uses the rex command to extract values matching the pattern from the events' fields. The regex pattern {.*} looks for substrings that begin with { and end with }. The [^%] part ensures that the match does not begin with a % character. The captured value within the curly braces is assigned to the named capture group guid.
- table guid displays the extracted GUIDs in the output. This command is used to format the results and display only the guid field.
- The max_match=0 option ensures that all occurrences of the pattern are extracted from each event. By default, the rex command only extracts the first occurrence.
This is useful because GUIDs are not automatically extracted from 4662 event logs.
The lookup command
The lookup command enriches the data with external sources. Example:
Suppose the following CSV file called malware_lookup.csv.
```
filename, is_malwarenotepad.exe, falsecmd.exe, falsepowershell.exe, falsesharphound.exe, truerandomfile.exe, true
```
This CSV file should be added as a new Lookup table as follows.
See Also
Click-to-parse productivity hack

index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | rex field=Image "(?P<filename>[^\\\]+)$" | eval filename=lower(filename) | lookup malware_lookup.csv filename OUTPUTNEW is_malware | table filename, is_malware

index="main" sourcetype="WinEventLog:Sysmon" EventCode=1: This is the search criteria. It's looking for Sysmon logs (as identified by the sourcetype) with an EventCode of 1 (which represents process creation events) in the "main" index.
| rex field=Image "(?P<filename>[^\\\]+)$": This command is using the regular expression (regex) to extract a part of the Image field. The Image field in Sysmon EventCode=1 logs typically contains the full file path of the process. This regex is saying: Capture everything after the last backslash (which should be the filename itself) and save it as filename.
| eval filename=lower(filename): This command is taking the filename that was just extracted and converting it to lowercase. The lower() function is used to ensure the search is case-insensitive.
| lookup malware_lookup.csv filename OUTPUTNEW is_malware: This command is performing a lookup operation using the filename as a key. The lookup table (malware_lookup.csv) is expected to contain a list of filenames of known malicious executables. If a match is found in the lookup table, the new field is_malware is added to the event, which indicates whether or not the process is considered malicious based on the lookup table. <-- filename in this part of the query is the first column title in the CSV.
| table filename, is_malware: This command is formatting the output to show only the fields filename and is_malware. If is_malware is not present in a row, it means that no match was found in the lookup table for that filename.

In summary, this query is extracting the filenames of newly created processes, converting them to lowercase, comparing them against a list of known malicious filenames, and presenting the findings in a table.

The inputlookup command

The inputlookup command retrieves data from a lookup file without joining it to the search results. Example:

| inputlookup malware_lookup.csv

This command retrieves all records from the malware_lookup.csv file. The result is not joined with any search results but can be used to verify the content of the lookup file or for subsequent operations like filtering or joining with other datasets.

Time Range

Every event in Splunk has a timestamp. Using the time range picker or the earliest and latest commands, you can limit searches to specific time periods. Example:

index="main" earliest=-7d EventCode!=1

By combining the index="main" condition with earliest=-7d and EventCode!=1, the query will retrieve events from the main index that occurred in the last seven days and do not have an EventCode value of 1.

The transaction command

The transaction command is used in Splunk to group events that share common characteristics into transactions, often used to track sessions or user activities that span across multiple events. Example:

index="main" sourcetype="WinEventLog:Sysmon" (EventCode=1 OR EventCode=3) | transaction Image startswith=eval(EventCode=1) endswith=eval(EventCode=3) maxspan=1m | table Image | dedup Image

index="main" sourcetype="WinEventLog:Sysmon" (EventCode=1 OR EventCode=3): This is the search criteria. It's pulling from the main index where the sourcetype is WinEventLog:Sysmon and the EventCode is either 1 or 3. In Sysmon logs, EventCode 1 refers to a process creation event, and EventCode 3 refers to a network connection event.
| transaction Image startswith=eval(EventCode=1) endswith=eval(EventCode=3) maxspan=1m: The transaction command is used here to group events based on the Image field, which represents the executable or script involved in the event. This grouping is subject to the conditions: the transaction starts with an event where EventCode is 1 and ends with an event where EventCode is 3. The maxspan=1m clause limits the transaction to events occurring within a 1-minute window. The transaction command can link together related events to provide a better understanding of the sequences of activities happening within a system.
| table Image: This command formats the output into a table, displaying only the Image field.
| dedup Image: Finally, the dedup command removes duplicate entries from the result set. Here, it's eliminating any duplicate Image values. The command keeps only the first occurrence and removes subsequent duplicates based on the Image field.

In summary, this query aims to identify sequences of activities (process creation followed by a network connection) associated with the same executable or script within a 1-minute window. It presents the results in a table format, ensuring that the listed executables/scripts are unique. The query can be valuable in threat hunting, particularly when looking for indicators of compromise such as rapid sequences of process creation and network connection events initiated by the same executable.

Subsearches

A subsearch in Splunk is a search that is nested inside another search. It's used to compute a set of results that are then used in the outer search. Example:

index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 NOT [ search index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | top limit=100 Image | fields Image ] | table _time, Image, CommandLine, User, ComputerName

In this query:

index="main" sourcetype="WinEventLog:Sysmon" EventCode=1: The main search that fetches EventCode=1 (Process Creation) events.
NOT []: The square brackets contain the subsearch. By placing NOT before it, the main search will exclude any results that are returned by the subsearch.
search index="main" sourcetype="WinEventLog:Sysmon" EventCode=1 | top limit=100 Image | fields Image: The subsearch that fetches EventCode=1 (Process Creation) events, then uses the top command to return the 100 most common Image (process) names.
table _time, Image, CommandLine, User, Computer: This presents the final results as a table, displaying the timestamp of the event (_time), the process name (Image), the command line used to execute the process (CommandLine), the user that executed the process (User), and the computer on which the event occurred (ComputerName).

Assessment:

Navigate to http://[Target IP]:8000, open the "Search & Reporting" application, and find through an SPL search against all data the account name with the highest amount of Kerberos authentication ticket requests. Enter it as your answer.

Solution:

verbose, last 2 years of logs

Windows 4768 A Kerberos authentication ticket (TGT) was requested

Search in splunk SPL: Eventcode=4768, last 5 years, verbose

Under interesting fields select account name:

Answer:

waldo count (12), 25%

Navigate to http://[Target IP]:8000, open the "Search & Reporting" application, and find through an SPL search against all 4624 events the count of distinct computers accessed by the account name SYSTEM. Enter it as your answer.

Solution:

verbose, last 2 years of logs

search SPL:

"SYSTEM" Account_Name=SYSTEM | stats dc(SYSTEM)

Under interesting fields select account name: ComputerName = 10

Answer = 10

Navigate to http://[Target IP]:8000, open the "Search & Reporting" application, and find through an SPL search against all 4624 events the account name that made the most login attempts within a span of 10 minutes. Enter it as your answer.

search SPL:

verbose, last 2 years of logs

EventCode=4625 | stats count by Account_Name, _time

Add slected fileds: failure_Reason, date_minute and Account_Name

Sort date_minute colum

There are 3 events that shows within 10 min

Account_Name = aparsa

Answer = aparsa

Introduction To Splunk & SPL | Portfolio (2024)

References