topic join two rex results from different host and query in Splunk Search

join two rex results from different host and query

x_tivity — Sat, 18 Jan 2020 12:05:18 GMT

I have two query...

index=xxx_prod host="foo.org" 5032 submit | rex "id=PO:(?<PO>\d*)" | dedup PO | table PO _time

index=xxx_prod host="bar.org" | rex "savePO.*POId=(?<PO>\d*).*\"responseCode\":200" | dedup PO | table PO _time

I want to compare both PO results from different services one is submitted, another one is saved. and I want to show as the following table

    PO  | submit_date             | save_date               | elapse_time_min | isSave   
1000001 | 2020-01-18 02:09:49.022 | 2020-01-18 02:51:51.289 | 41              | true
1000002 | 2020-01-18 03:18:25.780 | 2020-01-18 03:59:08.695 | 49              | true
1000003 | 2020-01-18 03:18:25.780 |                         |                 | false

How can I do that?

Re: join two rex results from different host and query

woodcock — Sat, 18 Jan 2020 19:28:37 GMT

Show us some sample events from each sourcetype.

Re: join two rex results from different host and query

to4kawa — Wed, 30 Sep 2020 03:45:24 GMT

index=xxx_prod (host="foo.org" 5032 submit) OR (host="bar.org")
| rex "id=PO:(?<submit_PO>\d*)"
| rex "savePO.*POId=(?<save_PO>\d*).*\"responseCode\":200"
| eval PO=coalesce(submit_PO,save_PO)
| eval flag=if(searchmatch("savePO"),"save","submit")
| stats earliest(eval(if(flag="submit",_time,NULL))) as submit_date latest(eval(if(flag="save",_time,NULL))) as save_date by PO
| eval elapse_time_min=round(save_date - submit_date / 60)
| eval isSave=if(isnull(elapse_time_min),"false","true")
| table PO submit_date save_date elapse_time_min isSave
| foreach *date
    [eval <<FIELD>>=strftime(<<FIELD>>,"%F %T.%3Q")]

Hi, @x_tivity
It would be a little easier if save and submit were determined by the host name.
Since I don't know the actual log,
First time for submit and Last time for save are counted.
how about this