The Splunk Search Language (SPL)

Search Terms: see Searching in Splunk

Commands: tell Splunk what we want to do with the search result

Charts
Computing statistics
Formatting

Functions: explain how we want to chart, compute and evaluate the results

Arguments: variables we apply to the functions

Clauses: grouping and definition of results

Separator

Use pipes (|) to separate the components of the search language. The result of the component on the left is passed to the next component, no more data is read.

sourcetype=access_combined | top age | fields name

Editor features

Color coding
- orange: Boolean operators and command modifiers
- blue: commands
- green: command arguments
- purple: functions
If the cursor id behind a parenthesis, the matching parenthesis is highlighted
Hotkeys
- Move each pipe to a new line: ⌘-\ (Mac) , ctrl-\ (Windows)

Commands

fields

Include and exclude fields from the search result. Separate the fields with space or comma.

Include fields. Happens before field extraction, can improve performance.

sourcetype=access_combined | fields status, clientip

Exclude fields (use negative sign after the word fields). It only affects the displayed result, no benefit to performance.

sourcetype=access_combined | fields - status, clientip

table

Retains the data in a tabulated format. Separate the fields with a comma.

Field names are the table column headers.

sourcetype=access_combined | table status, clientip

rename

Renames table fields fo display. Use space to separate the fields.

Wrap the name in quotes if the name contains space,

sourcetype=access_combined
| table status, clientip
| rename clientip as "IP Address"
status as "Status"

In subsequent components, we need to use the new name of the field, because that is passed forward by the pipe separator.

sourcetype=access_combined
| table status, clientip
| rename clientip as "IP Address"
| fields - "IP Address"

dedup

Removes duplicate events that share common values. Separate the fields with space.

sourcetype=access_combined
| dedup first_name last_name 
| table first_name last_name

sort

Ascending or descending order of the results.

Ascending order. The default order is ascending, the plus sign (+) also causes ascending sort.

sourcetype=access_combined
| table first_name last_name
| sort first_name last_name

Descending order
- If there is a space between the minus sign and the field name, the descending order applies to all specified fields:
```
sourcetype=access_combined
| table first_name last_name
| sort - age wage
```
- If there is no space between the minus sign and the field name, the descending order only applies to that field:
```
sourcetype=access_combined
| table first_name last_name
| sort -age wage
```

limit argument

To limit the number of events returned, use the limit argument.

sourcetype=access_combined
| table first_name last_name
| sort -age wage limit=10

top

Finds the most common values of the given fields in the result set. Used to render the result in graphs.

sourcetype=vendor_sales
| top Vendor

Automatically provides the data in tabular form and displays the count and percent columns, and limits the results to 10.

limit clause

Set the desired number or results.

sourcetype=vendor_sales
| top Vendor limit=20

To get all results, use limit=0

sourcetype=vendor_sales
| top Vendor limit=0

You can add more fields to the list separated by space or comma.

index=main sourcetype=access_combined_wcookie 
| top JSESSIONID, file

Change the title of the count and percentage columns.

index=main sourcetype=access_combined_wcookie 
| top JSESSIONID file countfield = "Product count" percentfield = "Product percent"

Control the visibility of the count and percent fields.

index=main sourcetype=access_combined_wcookie 
| top JSESSIONID file showcount = True/False showperc = True/False

Add count and percent numbers for not within the limit.

index=main sourcetype=access_combined_wcookie 
| top JSESSIONID file useother = True/False

Specify the display value of the OTHER row:

index=main sourcetype=access_combined_wcookie 
| top JSESSIONID file otherstr = "Total count"

by clause

Top three product sold by each vendor

sourcetype=vendor_sales
| top product_name by Vendor limit=3

rare

Shows the least common values of the field set.

Has the same options as the top command.

stats

Produces statistics of the search results.

Stats functions

count

The number of events matching the search criteria.

index=main sourcetype=access_combined_wcookie 
| stats

To rename the “count” header us “as”

index=main sourcetype=access_combined_wcookie 
| stats count as "Total files"

Use “by” to group the result

index=main sourcetype=access_combined_wcookie 
| stats count as "Total files" by file

Add more fields with comma

index=main sourcetype=access_combined_wcookie 
| stats count as "Total files" by file, productId

Add a field to the count function to count events where the field is present

index=main sourcetype=access_combined_wcookie 
| stats count(file) as "Total files"

Compare the count to the total number of events

index=main sourcetype=access_combined_wcookie 
| stats count(file) as "Total files", count as "Total events"

distinct_count or dc

Count of unique values for a field.

index=main sourcetype=access_combined_wcookie 
| stats distinct_count(file) as "Total files"

index=main sourcetype=access_combined_wcookie 
| stats distinct_count(file) as "Total files" by productId

sum

Returns the sum of the numerical values.

index=main sourcetype=access_combined_wcookie 
| stats sum(bytes)

Count the events and sum the value

index=main sourcetype=access_combined_wcookie 
| stats count(file) as "Total files" sum(bytes)

Group the sum and count values by a field. These must be within the same pipe to work on the same set of data.

index=main sourcetype=access_combined_wcookie 
| stats count(file) as "Total files" sum(bytes) by productId

avg

Returns the average of numerical values.

index=main sourcetype=access_combined_wcookie 
| stats avg(bytes) as "Average bytes"

Group the values by a field

index=main sourcetype=access_combined_wcookie 
| stats avg(bytes) as "Average bytes" by productId

Add count to the table

index=main sourcetype=access_combined_wcookie 
| stats count as "Number of files" avg(bytes) as "Average bytes" by productId

list

Lists all values of a given field.

index=main sourcetype=access_combined_wcookie 
| stats list(file) as "Files"

Group the list of values by another field, but lists all repeated values.

index=main sourcetype=access_combined_wcookie 
| stats list(file) as "Files" by productId

values

Works like the list function, but returns the unique values of a given field.

index=main sourcetype=access_combined_wcookie 
| stats values(file) as "Unique Files"

Group the unique values by another field

index=main sourcetype=access_combined_wcookie 
| stats values(file) as "Unique Files" by productId

Leave a comment

Cancel reply