Splunk Search

Is there an effective way of merging 2 splunk searches?

varma364
Explorer

How do I merge the below 2 complex queries? Let me know if it's possible in Splunk?

Search 1: - 

 

 

 index=ABC (eventtype=X OR eventtype=Y) log_subtype=DEF field_A="*SQL*" 
| stats values(A) as A values(B) as B values(C) as C BY X, Y 
| where B > 2 
| search NOT [|inputlookup test_1.csv | fields X ] 
| search NOT [|inputlookup test_2.csv | fields X ] 
| eval name="search_1" 

 

 

 

Search 2: -

 

 

 index=ABC (log_subtype="GHI" OR log_subtype="JKL") (severity="medium" OR severity="high" OR severity="critical") action=* NOT (field_B="Unknown(5000007)" AND action="blocked") NOT dest_ip="11.22.33.44" 
| stats values(D) as D values(E) as E values(A) as A BY X, Y 
| eval name="search_2" 

 

 

 

I succeeded on merging the 2 searches up to some extent (up to stats command)

 

 

 index=ABC (log_subtype="DEF" OR log_subtype="GHI" OR log_subtype="JKL")(((eventtype=X OR eventtype=Y) field_A="*SQL*") OR ((severity="medium" OR severity="high" OR severity="critical") action=* NOT (field_B="Unknown(5000007)" AND action="blocked") NOT dest_ip="11.22.33.44" ))
| stats values(A) as A values(B) as B values(C) as C values(D) as D BY X, Y 

 

 

 

I am not sure on how I can apply where condition and exclusion lookups from search 1 while combining as they are specific to search 1 and do not want to apply those to search 2?

 

 

 

 

 

 

Labels (5)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

One way to merge the searches and preserve the condition and exclusions is with append.

 index=ABC (eventtype=X OR eventtype=Y) log_subtype=DEF field_A="*SQL*" 
| stats values(A) as A values(B) as B values(C) as C BY X, Y 
| where B > 2 
| search NOT [|inputlookup test_1.csv | fields X ] 
| search NOT [|inputlookup test_2.csv | fields X ] 
| eval name="search_1" 
| append [ search index=ABC (log_subtype="GHI" OR log_subtype="JKL") (severity="medium" OR severity="high" OR severity="critical") action=* NOT (field_B="Unknown(5000007)" AND action="blocked") NOT dest_ip="11.22.33.44" 
  | stats values(D) as D values(E) as E values(A) as A BY X, Y 
  | eval name="search_2" ]
| stats values(*) as * by X, Y

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

varma364
Explorer

NA

Tags (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

I masked the IP address in this reply.

---
If this reply helps you, Karma would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

One way to merge the searches and preserve the condition and exclusions is with append.

 index=ABC (eventtype=X OR eventtype=Y) log_subtype=DEF field_A="*SQL*" 
| stats values(A) as A values(B) as B values(C) as C BY X, Y 
| where B > 2 
| search NOT [|inputlookup test_1.csv | fields X ] 
| search NOT [|inputlookup test_2.csv | fields X ] 
| eval name="search_1" 
| append [ search index=ABC (log_subtype="GHI" OR log_subtype="JKL") (severity="medium" OR severity="high" OR severity="critical") action=* NOT (field_B="Unknown(5000007)" AND action="blocked") NOT dest_ip="11.22.33.44" 
  | stats values(D) as D values(E) as E values(A) as A BY X, Y 
  | eval name="search_2" ]
| stats values(*) as * by X, Y

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

varma364
Explorer

Thanks @richgalloway for the response. Also, is there any other way other than using append, union or map since using those impacting the search execution time?

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Let's start by noticing that you already hurt your performance badly by using wildcards at the beginning of your search term.

Also, you're using a lot of exclusions which are often way way less performant than inclusions.

Anyway, if you have a field or set of fields which distinguishes one of your "partial search" from another, you can use a condition on that field to limit the applicability of your operations to some extent.

Like

| search log_subtype!="DEF" OR (log_subtype="DEF" AND (NOT [ | inputlookup whatever ])) 

 It's also worth noticing that you're already spawning two subsearches (which again might not be the best possible way - there is another technique - use a lookup directly and check if there was a match).

0 Karma

richgalloway
SplunkTrust
SplunkTrust

There is the join command, but performance of that is even worse than append.  Multisearch would be an option if the searches didn't contain non-streaming commands (stats).

Perhaps someone else will have a suggestion.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...