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?
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
I masked the IP address in this reply.
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
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?
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).
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.