topic Re: Join 2 splunk searches and get the output in table in Splunk Search

Join 2 splunk searches and get the output in table

bmanikya — Wed, 07 Feb 2024 11:52:54 GMT

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

Re: Join 2 splunk searches and get the output in table

ITWhisperer — Wed, 07 Feb 2024 12:04:43 GMT

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

Re: Join 2 splunk searches and get the output in table

gcusello — Wed, 07 Feb 2024 12:06:30 GMT

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

Re: Join 2 splunk searches and get the output in table

bmanikya — Fri, 09 Feb 2024 02:45:47 GMT

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

Re: Join 2 splunk searches and get the output in table

bmanikya — Fri, 09 Feb 2024 03:07:57 GMT

@gcusello Results are empty for App1 and App2.

Re: Join 2 splunk searches and get the output in table

gcusello — Fri, 09 Feb 2024 08:28:38 GMT

Hi @bmanikya ,

check the regexes.

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

Ciao.

Giuseppe

Re: Join 2 splunk searches and get the output in table

ITWhisperer — Fri, 09 Feb 2024 08:45:25 GMT

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