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

[davidepala]

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.

[mdsnmss]

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.

[gcusello]

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