Levering Lookups and Subsearches in Splunk

---

Lookups allow you to add other fields and values to events not included in the indexed data.

• are a way to add additional fields to search results at search time
• add based on paired fields in the events
• after configuring a lookup, its fields appear in the fields list in search for use
• 4 types of lookups:
  • File-based
    • populate events with fields pulled from csv files
  • External
    • append external data to events through the use of python scripts of binary executables
  • KV store
    • access key-value pairs from a KV store collection
  • Geospatial
    • add geographic information to events by referencing a KMZ or KML file

Using Lookup Commands

• Use to lookup fields from another data source based on a shared field
• similar to XLOOKUP in Excel

inputlookup

inputlookup command is useful for searching and validating the contents of a lookup table.

• generating command
  • first command in a search
• syntax: | inputlookup [<file>]|[<lookup-definition>]

lookup

lookup command is used to invoke field-value lookups.

• used to populate fields brought into search results at search time from external lookup file
• preceded by a search
• syntax: ...| lookup <lookup-table-name> <lookup-field>
  • by default, adds all remaining fields in the lookup table to the events
• can be explicit about what set of fields to output with:
  • [OUTPUT|OUTPUTNEW] (<lookup-destfield>)
    • OUTPUT overwrites existing fields
    • OUTPUTNEW does not overwrite existing fields
  • lookup output fields only exist for the duration of the search
• Can create an automatic lookup to avoid entering a lookup command each search
• can limit distinct values
  • using the same name for lookupfield and lookupdestfield limits the number of distinct values for lookupdestfield
    • e.g., | lookup knownusers.csv user OUTPUT user
  • any events where the value of lookupdestfield did not match the lookupfield are set to NULL
  • essentially, filters the field in the event based on the vales in the field of the lookup

outputlookup

outputlookup writes results of a search to a file-based lookup (CSV) or KV Store collection.

• syntax: ...| outputlookup <filename>|<lookup-definition>
• by default, the lookup is created in the app that the search is run in
  • createinapp=fales option will save to the system lookup directory instead

Example

index=security sourcetype=linux_secure "failed password" earliest=-30d
| stats count by user
| eval daily_average = round(count/30)
| fields - count
| outputlookup averages.csv createinapp=true

• This search finds all failed password login attempts in the past 30 days
  • gets count by user
  • computes a daily average
  • writes results to a csv file, which is created in the current application
• Outputs this to create a searchable lookup
• can be saved as an alert or report
• can be run a schedule

Adding a Subsearch

Subsearch is a search that passes results to an outer search as search terms.

• uses:
  • narrow down the search
  • to use with commands to combine the results of one search to the results of another
• enclosed in [] brackets
• must begin with a generating command
  • search
  • tstats
• always executed first, before the outer search
• syntax: index=<index> sourcetype=<sourcetype> [subsearch] | additional commands
• can place a NOT in front of the subsearch

When to Use Subsearches

• each subsearch is an additional search, which can use up system resources
• have a default time limit of 60s to execute
  • after the time, it is finalized regardless of if it completes
• by default, only returns up to 10k results
• limit can be adjusted by an administrator
• subsearch should be used on a narrow set of data
  • ideally for filtering down searches more
• subsearches are slow
• better to use a combination of stats and eval
• only use a subsearch if there is no other way to retrieve the needed data
• include earliest and latest time modifiers to reduce size of search

Troubleshooting Subsearches

• ensure subsearch is wrapped in []]
• must start with a command
• run inner and outer searches independently to ensure they return results
• look at the job inspector

return

return command passes values from a subsearch to an outer search.

• syntax: ...| return [<count>] [<field>...][<alias>=<field>...][$<field>...]
• using return at end of a subsearch usually removes the need for a field, rename, or format, or head commands
• preceded by a search
• followed by a count value (int)
  • specifies how many rows of results to return
  • by default only returns first row
• can specify one or more fields to return
  • separated by spaces
• use <alias>=<field> to rename the field

Example

index=security sourcetype=linux_secure "failed password" src_ip!=10.*
| stats count by src_ip
| where count>10
| return src_ip

Returns:

src_ip="107.3.146.207"

• Putting a $ in front of the return field value ($src_ip)
  • returns just the value: 107.3.146.207
  • excludes field name
• alias renames field
  • e.g., ip=src_ip

• return vs field