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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...