Splunk Search

How to extract a different file from a search in two indexes when the events have a common field value?

Path Finder

Hi guys
I need to extract two different fields from two different events in two different index only if these two events have a common field value and occur in a specific time range. For example:

EVENT1:
index SRV
_time 10:49:01
username bilbo.baggins
exchangecookie 123456789

EVENT2:

index LB
_time 10:49:00
srcip 123.45.67.89
exchangecookie 123456789

EVENT 3

index LB
_time 10:49:00
srcip 123.45.67.89
exchangecookie abcdefghi

EVENT 4

index LB
_time 10:51:00
srcip 123.45.67.89
exchangecookie 123456789

I want to draw a table with this info: username from index SRV, srcip and _time from index LB only if exchangecookie is the same in both events and the time spwn is less than 5 sec. In this case the output wil be:

10.49.00     bilbo.baggins      123.45.67.89

Time and src IP from EVENT 1 and username from EVENT 2. EVENT 3 must be ignored because have a different exchangecookie and EVENT 4 was indexed too late.

I've read about join but I don't think it's the solution.

0 Karma

SplunkTrust
SplunkTrust
index=SRV | fields username, srcip, _time, exchangecookie | JOIN exchangecookie [index=LB | eval lb_time=_time] | eval spwn=abs(lb_time-_time) | search spwn<5 | table username, srcip, _time

You may need to do some conversions depending on time format. If you have lb_time and _time in epoch that should give you the answer.

0 Karma

SplunkTrust
SplunkTrust

Hi davidepala,
see transaction command at https://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Transaction

Anyway try something like this

index=LB OR index=SRV
| transaction exchangecookie maxspan=5s
| table _time username srcip

Transaction isn't a performant command, you could also try something like this

index=LB OR index=SRV
| bin _time span=5s
| stats values(_time) AS _time values(username) AS username values(srcip) AS srcip count BY  exchangecookie

Bye.
Giuseppe

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!