Detect /list Active VPN sessions

[riqbal47010]

Frm F5 VPN logs, i can easily determine the VPN duration by using transaction command. The working query for me is :
startswith= "New Connection on ip: "
Endswith= "session statistics: bytes IN:"

BUT
how can i detect active VapN sessions during last two hours means the users who are connected since two hours and still con ected.
One thought is to use eval in endswith that no such event with statistics.
But how to write the query ?

2ndly use the stats command where i can say "new connection" AND NOT "session staristics"
And use earliest(_time) as "session_start"to get session start time and then use
Now() - session_start.

Any thoughts ...?

[richgalloway]

Your second idea seems better. The approach I would take is to search for start and end events and dedup them by user. That leaves the most recent event for each user. Filter out the end events and you have all those who are still connected.

index=foo ("new connection" OR "session statistics") 
| dedup user
| where searchmatch("new connection")
| eval connectionLength = tostring(now() - _time, "duration")

[riqbal47010]

hi rich,

below is my final working query:

  index=f5 partition="abc" earliest=-2h@h latest=-1h@h | transaction session_id .| eval Test=if(match(message,"Session statistics"),"session_closed","active_session")
     | search Test="active_session" | where isnotnull(user) AND searchmatch("New session")
     | eval connectionLength = tostring(now() - _time, "duration") | stats values(host) as host,earliest(_time) as session_starttime, values(connectionLength) as connectionLength,values(user) as user,values(src) as src by session_id | convert ctime(session_starttime) as session_starttime

===============================
and below query to validate the session_id( the resutles comes from above query)

index=f5 partition="abc-*" session_id=xxxx | stats values(host) as host, earliest(_time) as session_starttime,values(_time) as time,values(user) as user, values(message) as message by session_id | eval Test=if(match(message,"Session statistics"),"session_closed","active_session") | search Test="active_session" | eval connectionLength = tostring(now() - time, "duration") | where isnotnull(user) AND searchmatch("New session") | convert ctime(session_starttime) as session_starttime, ctime(time) as time

the only missing part for me that connectionLentgh is not apearing in results.

[to4kawa]

values(_time) → min(_time)