Splunk Search

How to join 2 searches using time range?

stwong
Communicator

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

0 Karma
1 Solution

493669
Super Champion

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]

View solution in original post

0 Karma

493669
Super Champion

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]
0 Karma

stwong
Communicator

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

0 Karma

493669
Super Champion

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...