Splunk Search

Is there an effective way of merging 2 splunk searches?

varma364
Path Finder

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

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
Path Finder

kjhkja kjha x'la

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.

varma364
Path Finder

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!

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...