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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...