Search Queries

Useful search commands

Count by tag you are searching for like "Message" will bring a count of like messages

| stats count by ip

| stats count by userName, ip

Rex is regex that you can replace a string like below replace a space with a underscore

| rex field=_raw "Account Name:\s+(?<Account_Name>[^\s]+)" | stats count by Account_Name   

Pulls data from fields and organizes it into a table view

| table UserId, SourceFileName, UserAgent, CreationTime

lookup a uploaded csv table by file name

| inputlookup <filename.csv>

Find items like

| inputlookup AllUsersMfaDetails.csv | where like(lower(DeviceName), "%ip%")

Compare IP data in file with Splunk logs

• lookup c2cisp.csv ip - calls the file and uses the ip column

• matches the ip to the the data set d_ip

• outputs the matched in a var named c2cisp

| lookup c2cisp.csv ip as d_ip OUTPUT ip as c2cisp | search c2cisp=*

How to search a lookup table to find any IP addresses that match the IPs in my firewall events?

Find IPs that are not in the csv or 123.123.123.123

| lookup IP.csv ip AS ipAdd OUTPUT ip AS match_ip
| where isnull(match_ip) | where ipAdd != "123.123.123.123"
| stats count by userDisplayName, ipAdd | sort - count

lookup Resources

For reference: the docs have a page for each command: lookup, inputlookup, and outputlookup.

In short:

• lookup adds data to each existing event in your result set based on a field existing in the event matching a value in the lookup

• inputlookup takes the the table of the lookup and creates new events in your result set (either created completely or added to a prior result set)

• outputlookup takes the current event set and writes it to a CSV or KVStore.

As an aside, when getting started with SPL commands, the Quick Reference Guide is the holy grail IMO for learning all about Splunk key concepts and common commands, along with different examples. Make sure you've got this one in your back pocket, as well as the Search Reference Docs. Yes, you can lookup two tables in the came command. You can even join the two tables together. It really depends on what you're trying to do with the lookup (whether you're trying to use multiple inputlookup calls, or multiple lookup calls).

• The former requires the use of append or join:

  | inputlookup lookup1| append [|inputlookup lookup2]| join ip [|inputlookup lookup3]

• The latter is just sequential:

  index=<index> sourcetype=<sourcetype> |lookup lookup1 ip |lookup lookup2 host OR |inputlookup3 |lookup lookup1 ip |lookup lookup2 host

Using Sort

your search....| sort -count

your search....| sort -_time

How do I sort this stats count?

Using where

 where ClientIP IN ("86.48.9.97", "92.119.17.191")

where command examples - Splunk Documentation

Using maps with Location of IP

Cluster map

| iplocation ipAdd 
| geostats latfield=lat longfield=lon count by userName

Choropleth Map

| iplocation ip
| stats count by Country 
| rename Country AS country count as numb 
| sort -numb 
| geom geo_countries featureIdField=country

To add multiple lookup files to a search, this should work for Cluster map and Choropleth Map

• You can just stack the lookups

source="activity" load=Directory  Op=Logged
| lookup Microsoft.csv subnet AS CIP OUTPUT subnet AS matched_subnet
| lookup IP.csv IP AS CIP OUTPUT IP AS matched_subnet
| where isnull(matched_subnet)
| iplocation CIP
| geostats latfield=lat longfield=lon count by UserId

IP manipulation removing port

index=your_index sourcetype=your_sourcetype
| rex field=source_IP "(?<ip>\d{1,3}(\.\d{1,3}){3})"
| stats count by ip

1. rex command:

   • Extracts the IP address portion from the field_with_ip_port field.

   • The regex (?<ip>\d{1,3}(\.\d{1,3}){3}) captures any valid IPv4 address.

2. stats count by ip:

   • Groups the results by the extracted ip field.

   • Counts the occurrences of each unique IP address.

Count the location of a IP

sourcetype="activity"
| spath Operation 
| search Operation=FileAccessed ClientIP!=123.123.123.123
| lookup MiUm.csv subnet AS ClientIP OUTPUT subnet AS matched_subnet1
| lookup GEP.csv GEIP AS ClientIP OUTPUT GEIP AS matched_subnet2 
| where isnull(matched_subnet1) AND isnull(matched_subnet2) 
| iplocation ClientIP 
| stats count by Country
| sort count