Splunk Search

join two rex results from different host and query

x_tivity
Engager

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?

0 Karma

to4kawa
Ultra Champion
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

0 Karma

woodcock
Esteemed Legend

Show us some sample events from each sourcetype.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...