How to get accurate transaction results for VPN Sessions which start or end at either side of the search time boundaries?

[soniquella]

Good morning. I hope you can help.

I have been tasked with creating a chart for the top 25 users who spend the longest accumulated duration on our VPN sessions over a timespan of 7 days.

host=hostname* sourcetype="WinEventLog:System" EventCode=20272  | rex field=Message ": The user (?<user>\S+)"| rex field=_raw  " The user was active for (?<sess_len_min>\d+) minutes (?<sess_len_secs>\d+)" | eval Total_Sess_Len = sess_len_min*60 + sess_len_secs |  stats sum(Total_Sess_Len) as TotalSession by user | sort 25 -TotalSession | eval TotalSession = tostring(TotalSession, "duration")

The problem that I have is that if I search this over a period of 60 minutes, then a user's session may have STARTED before the timespan search and therefore returns a duration of longer than 60 minutes which based over a week's search would not give accurate results.

I understand that you can keep orphan events, but again, this would not give an accurate reflection of all session durations based over a set time period if sessions start or end either side of the time boundaries.

Any help that you could offer would be greatly appreciated.

Cheers,

Rob.

[javiergn · Accepted solution]

Let me see if I get this right:

• UserA starts a session on "December 15th 08:00:00" and finalises that session 5 hours later "December 15th 13:00:00"
• Now you run your search for the last 24 hours: "December 15th 11:00:00" - "December 16th 11:00:00"
• Your eventlog returns 5 hours (300 minutes) for UserA
• But the output should be 2 hours (5 minus 3) because the search started 3 hours after UserA logged in

If that's what you are trying to achieve then you will need to:

• Store search start time in variable: MySearchStartTime
• Calculate when the VPN session started by subtracting the length of your session (minutes+seconds) from your event to your event Timestamp. Store this on MyVPNStartTime
• If MyVPNStartTime < MySearchStartTime => MySessionTime = "NumberOfMinutesSeconds" - (MySearchStartTime - MyVPNStartTime)

Hope that's clear enough

[javiergn]

By the way, if you want to use the start time and end time of your search take a look at this

[soniquella]

That is exactly it! Thank you so much for your help Javier. Very much appreciated.

Cheers,

Rob.