topic Re: How to join 2 searches using time range? in Splunk Search

How to join 2 searches using time range?

stwong — Wed, 14 Feb 2018 18:26:11 GMT

Hi all,

We're trying to combine 2 searches:

Search 1:  application transaction log
 ...| transaction connId | eval start=_time | eval end=_time+duration | table start, end, connId, clientIP

Search 2:  VPN log
...| transaction Acct_Session_Id | eval start1=_time | eval end1=_time+duration | table start1, end1, Acct_Session_Id, NAS_IP_Address, UserName

We hope to combine the search by:

start<=start1 end>=end1 clientIP=NAS_IP_Address

Giving a table of:

start, end, connid, start1, end1, clientIP, start1, end1, Acct_Session_id, NAS_IP_Address, UserName

Tried with left join like "search1 | eval NAS_IP_Address = clientIP | join type=left usetime=true earlier=false NAS_IP_Address [search 2]" but failed.

Would anyone please advise?

Thanks and rgds.
/ST Wong

Re: How to join 2 searches using time range?

493669 — Wed, 14 Feb 2018 18:40:42 GMT

have you tried this:

...| transaction connId | eval start=_time | eval end=_time+duration | table start, end, connId, clientIP|join type=left usetime=true earlier=false clientIP[search......| transaction Acct_Session_Id | eval start1=_time | eval end1=_time+duration |rename NAS_IP_Address as clientIP| table start1, end1, Acct_Session_Id, NAS_IP_Address, UserName]

Re: How to join 2 searches using time range?

stwong — Tue, 29 Sep 2020 18:06:01 GMT

Hi, thanks for your help.

The first part of the output table (start, end connId, clientIP) gives 9 lines from Search 1.
The second part of the output table (start1, end1, Acct_Session_Id, NAS_IP_Address, User_Name) returns identical rows, i.e. the same set of values repeated 9 times. I wonder if the usetime option works for transactions in the 2 searches.

I tried using map and seems it works as expected.

Thanks again.
Rgds

Re: How to join 2 searches using time range?

493669 — Tue, 20 Feb 2018 10:46:25 GMT

@stwong,
if it works for you then please accept the answer.