Splunk Search

Join 2 splunk searches and get the output in table

bmanikya
Loves-to-Learn Lots

Search Query 1

bmanikya_0-1707306576366.png

 

Search Query 2

bmanikya_1-1707306604874.png

Would like to join search query 1 and 2 and get the results, but no results found.

index=imdc_gold_hadoopmon_metrics sourcetype=hadoop_resourcemanager "Allocated new applicationId"
| rex field=_raw "^(?:[^ \n]* ){4}(?P<App1>.+)"
| eval _time=strftime(_time,"%Y-%m-%d %H:%M")
| table _time, App1
| rename _time as Time1
| join type=inner App1
[ search index=imdc_gold_hadoopmon_metrics sourcetype=hadoop_resourcemanager "OPERATION=Submit Application Request"
| rex field=_raw "^(?:[^=\n]*=){6}\w+_\d+_(?P<App2>.+)"
| eval _time=strftime(_time,"%Y-%m-%d %H:%M")
| table _time, App2
| search App2=App1
| rename _time as Time2]
| table Time1, App1, Time2, App2

 

Labels (4)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @bmanikya,

at first Splunk isn't a database, so avoid to use join as usual for al of us coming from databases!

there are other more efficient methods to correlate events from two searches.

Anyway, in your search there's a thing that I don't understand:

in the second search you have:

| table _time, App2
| search App2=App1

but after the table command, you have only those two fields, so, where do you take the app1 field'

Anyway, try to redesign you searches using stats command and the join field as correlation key, something like this:

(index=imdc_gold_hadoopmon_metrics sourcetype=hadoop_resourcemanager "Allocated new applicationId") OR (index=imdc_gold_hadoopmon_metrics sourcetype=hadoop_resourcemanager "OPERATION=Submit Application Request")
| rex "^(?:[^ \n]* ){4}(?P<App1>.+)"
| rex "^(?:[^=\n]*=){6}\w+_\d+_(?P<App2>.+)"
| eval 
   Time1=if(searchmatch("Allocated new applicationId"),strftime(_time,"%Y-%m-%d %H:%M"),""),
   Time2=if(searchmatch("OPERATION=Submit Application Request"),strftime(_time,"%Y-%m-%d %H:%M"),""),
   app=coalesce(app1,app2)
| stats 
   values(Time1) AS Time1
   values(Time2) AS Time2
   BY app
| table Time1, App1, Time2, App2

Ciao.

Giuseppe

0 Karma

bmanikya
Loves-to-Learn Lots

bmanikya_0-1707448031295.png

@gcusello Results are empty for App1 and App2.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @bmanikya ,

check the regexes.

if you share some samples of your logs we could help you.

Ciao.

Giuseppe

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Ideally, you should rewrite your search to avoid using joins as they are slow.

If you want to continue with joins, you subsearch should have the same field name as the joining field. The subsearch executes before the main search so in your example App1 is not known in the subsearch (in fact, none of the fields from the main search are available to the subsearch in the join).

Try something like this

index=imdc_gold_hadoopmon_metrics sourcetype=hadoop_resourcemanager "Allocated new applicationId"
| rex field=_raw "^(?:[^ \n]* ){4}(?P<App1>.+)"
| eval _time=strftime(_time,"%Y-%m-%d %H:%M")
| table _time, App1
| rename _time as Time1
| join type=inner App1
[ search index=imdc_gold_hadoopmon_metrics sourcetype=hadoop_resourcemanager "OPERATION=Submit Application Request"
| rex field=_raw "^(?:[^=\n]*=){6}\w+_\d+_(?P<App1>.+)"
| eval _time=strftime(_time,"%Y-%m-%d %H:%M")
| rename _time as Time2
| table Time2, App1]
| table Time1, App1, Time2
0 Karma

bmanikya
Loves-to-Learn Lots

@ITWhisperer @gcusello In Hadoop ResourceManager,  Once after "Operation=Submit Application Request" resourcemanager will "Allocate New ApplicationID". I would like to see how much time difference between 2 sub searches in the splunk query. 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please share some sample anonymised events in code blocks </> to prevent reformatting and the lose of important data.

0 Karma
Get Updates on the Splunk Community!

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...