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