Join 2 splunk searches and get the output in table

[bmanikya]

Search Query 1

Search Query 2

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

[gcusello]

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

[bmanikya]

@gcusello Results are empty for App1 and App2.

[gcusello]

Hi @bmanikya ,

check the regexes.

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

Ciao.

Giuseppe

[ITWhisperer]

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

[bmanikya]

@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. 

[ITWhisperer]

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