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

richgalloway
SplunkTrust
SplunkTrust

This thread is several months old with an accepted solution so you may get better results by posting a new question.

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

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...