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.
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.
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