Related Answers
Download topic as PDF
Use subsearch to correlate events
A subsearch takes the results from one search and uses the results in another search. This enables sequential state-like data analysis. You can use subsearches to correlate data and evaluate events in the context of the whole event set, including data across different indexes or Splunk Enterprise servers in a distributed environment.
For example, you have two or more indexes for different application logs. The event data from these logs share at least one common field. You can use the values of this field to search for events in one index based on a value that is not in another index:
sourcetype=some_sourcetype NOT [search sourcetype=another_sourcetype | fields field_val]
Note: This is equivalent to the SQL "NOT IN" functionality:
SELECT * from some_table
WHERE field_value
NOT IN (SELECT field_value FROM another_table)
|
PREVIOUS About subsearches |
NEXT Change the format of subsearch results |
This documentation applies to the following versions of Splunk® Enterprise: 6.2.0, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 6.2.9, 6.2.10, 6.2.11, 6.2.12, 6.2.13, 6.2.14, 6.2.15, 6.3.0, 6.3.1, 6.3.2, 6.3.3, 6.3.4, 6.3.5, 6.3.6, 6.3.7, 6.3.8, 6.3.9, 6.3.10, 6.3.11, 6.3.12, 6.3.13, 6.3.14, 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.8, 6.4.9, 6.4.10, 6.4.11, 6.5.0, 6.5.1, 6.5.1612 (Splunk Cloud only), 6.5.2, 6.5.3, 6.5.4, 6.5.5, 6.5.6, 6.5.7, 6.5.8, 6.5.9, 6.5.10, 6.6.0, 6.6.1, 6.6.2, 6.6.3, 6.6.4, 6.6.5, 6.6.6, 6.6.7, 6.6.8, 6.6.9, 6.6.10, 6.6.11, 6.6.12, 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11, 7.1.0, 7.1.1, 7.1.2, 7.1.3, 7.1.4, 7.1.5, 7.1.6, 7.1.7, 7.1.8, 7.2.0, 7.2.1, 7.2.2, 7.2.3, 7.2.4, 7.2.5, 7.2.6, 7.2.7, 7.3.0
Comments
Drewski3420
Thanks for sharing this. The purpose of this example is to show a basic SQL example so that everyone will understand how the SQL equates to the SPL.
The SQL comparison code snippet should also include the other (faster) syntax to perform this query that people may be searching for - I know I was:
SELECT * from some_table some
WHERE NOT EXISTS (SELECT NULL FROM another_table another where some.field_value = another.field_value)
Briancronrath
The WHERE command supports two ways to use the IN function:
...| where in(field,"value1","value2", ...)
...| where field in("value1","value2", ...)
For examples, see the documentation for the IN function here
http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/ConditionalFunctions#in.28VALUE-LIST.29
Is there an example of how to do an "IN" statement?
You probably want to use stats for large datasets since if the subsearch goes over the 10k limit (or whatever yours is set to) you can silently get incorrect results.
sourcetype=some_sourcetype NOT [search sourcetype=another_sourcetype | stats values(field_val) as field_val]
https://answers.splunk.com/answers/207150/how-to-overcome-sub-search-limitation-only-10k-rec.html#answer-577080