Is there an effective way of merging 2 splunk searches?

[varma364]

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?

[varma364]

NA

[richgalloway]

I masked the IP address in this reply.

[richgalloway · Accepted solution]

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

[varma364]

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?

[PickleRick]

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).

[richgalloway]

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.