Trying to look for sessions with login and logout requests

[Labuser43]

Hello,

I'm just trying to learn SPL and am currently trying to find all sessions with login and logout requests, identified by the SESSION_ID field. So basically I'm trying to find all SESSION_ID values where within the session the user performs a login and logout operation. Coming from the relational database world, my first step was to write some sort of join operation but I quickly found out that joins are not the best thing to do in Splunk. 

This is what I tried:

index=allsessions "*login*" | join type=inner left=L right=R where L.SESSION_ID=R.SESSION_ID [search index=allsessions "*logout*"]

Can someone help me write a better query for the above problem? Thanks!

[gcusello · Accepted solution]

Hi @Labuser43 ,

at first Splunk isn't a database so you don't need an inner join to extract some data like the ones you want, forget all you know about databases and reset your mind (I did it 13 years ago!).

You have to correlate different events to extract e.g. the timestamps of logins and logouts and find the duration of a transaction.

So please, see my approach and adapt it to your requirements:

index=allsessions ("*login*" OR "*logout*")
| stats
    earliest(eval(if(searchmatch("*login*"),_time,"") AS earliest
    latest(eval(if(searchmatch("*logout*"),_time,"") AS latest
    BY SESSION.ID
| eval
    earliest=strgtime(earliest,"%Y-%m-%d %H:%M:%S"),
    latest=strgtime(latiest,"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe

[inventsekar]

Hi @Labuser43 

if i understand your requirement correctly, you may not need the join at all. simply try the OR option:

index=allsessions "*login*" OR "*logout*"

[Labuser43]

@inventsekar my requirement is to get SESSION_IDs where both login AND logout occur in that session. To explain more, let's use an example of a session that would fit this criteria:

OPERATION   SESSION_ID

login       1234

add_to_cart 1234

checkout    1234

logout      1234

If I use OR, I think it may return a session that only has login or logout.

[inventsekar]

Sure @Labuser43 , got it now.. pls try this inner join (or if you want to test other two joins, "type=left" or "type=outer")

index=allsessions "*login*" | join type=inner SESSION_ID [search index=allsessions "*logout*"]

still, the join can be avoided i feel. maybe pls check:
EDIT - included the OR portion

index=allsessions "*login*" OR "*logout*" | stats list(OPERATION) by SESSION_ID

OR

index=allsessions "*login*" OR "*logout*"| stats values(OPERATION) by SESSION_ID