topic Re: How do you correlate events from two different indexes by date? in Splunk Search

How do you correlate events from two different indexes by date?

ADRIANODL — Thu, 07 Mar 2019 23:45:36 GMT

Hi folks,

I have 2 indexes containing information as below:

index ABC

_time                   sessionkey                  name     action
06/03/2019 01:15:20   XfRtG5R3FR$Er                John       Login
06/03/2019 01:18:25   XfRtG5R3FR$Er                John       Logout
06/03/2019 03:28:10   FFT$WFTFETR%                 John       Login
06/03/2019 03:31:56   FFT$WFTFETR%                 John       Logout

index XYZ

  _time                    name     CRUD
06/03/2019 01:16:22        John     Update

So, unfortunately, I don't have a session key in both indexes to tie in these two events.

How can I correlate these 2 logs by Name AND date range? i.e.

TimeSessionStarted  TimeSessionFinished  sessionkey   name  CRUD
06/03/2019 01:15:20 06/03/2019 01:18:25 XfRtG5R3FR$Er John Update

Cheers,

Re: How do you correlate events from two different indexes by date?

chrisyounger — Thu, 07 Mar 2019 23:53:44 GMT

Well as long as the times are exactly the same, this should work:

index ABC OR index XYZ
|stats latest(CRUD) as CRUD latest(action) as action latest(sessionkey) as sessionkey by _time  name

Re: How do you correlate events from two different indexes by date?

ADRIANODL — Thu, 07 Mar 2019 23:59:37 GMT

Unfortunately the times are not the same. The time on index XYZ falls under a session of index ABC though.

Re: How do you correlate events from two different indexes by date?

mayurr98 — Fri, 08 Mar 2019 00:41:48 GMT

Can you try something like this ?

index="ABC" OR index="XYZ" 
| stats latest(eval(case(index="ABC" AND action="Login",_time))) as login_time latest(eval(case(index="ABC" AND action="Logout",_time))) as logout_time latest(eval(case(index="XYZ",_time))) as compare_time latest(CRUD) as CRUD latest(sessionkey) as sessionkey by name 
| where login_time<compare_time AND logout_time>compare_time

let me know if this helps!

Re: How do you correlate events from two different indexes by date?

ADRIANODL — Fri, 08 Mar 2019 02:19:07 GMT

Hi mayurr98,
Apologies but I didn't explain the whole thing: the login/logout words don't always appear, so I should rather use the session key as what defines a session.
Does that make sense?