topic Re: How to get accurate transaction results for VPN Sessions which start or end at either side of the search time boundaries? in Splunk Search

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

soniquella — Wed, 16 Dec 2015 11:33:41 GMT

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.

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

javiergn — Wed, 16 Dec 2015 12:20:24 GMT

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

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

javiergn — Wed, 16 Dec 2015 12:24:17 GMT

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

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

soniquella — Wed, 16 Dec 2015 14:31:51 GMT

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

Cheers,

Rob.