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!

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...

.conf24 | Registration Open!

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

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...