Calculate VPN duration per user

[ibralah93]

Dears,

I am trying to calculate how the total duration each user spends connected through VPN, their total online time.

I am using the below search, but the issue is for example in a 24 hour range if the user logged in only for 10 minutes at 1AM then again for 1 hour at 11AM the duration output will be 10 hours as it takes the very first event then the very last event.

How can I calculate based on timeslots that have events only?

index=pa src_zone="GP-VPN" src_user="*" | stats earliest(_time) AS earliest latest(_time) AS latest BY src_user | eval duration = tostring((latest-earliest)/60)

Timeline below, should be ~14hours:

Search Results, duration in minutes, resulting in 24 hours which is not correct due to gap time:

user	earliest	latest	duration
user1	1719144008.192	1719230507.192	1441.6500

[ibralah93]

@PickleRick & @ITWhisperer thank you both for the replies,

I understand there are other topics in the forum but all rely as you mentioned on a login/logout field, which is not present in my raw data, this is why I am calculating based on if there are events.

Sample of raw data:

Jun 24 15:01:20 10.50.8.100 1 2024-06-24T15:01:20+03:00 pafw01.company.com.sa - - - -  1,2024/06/24 15:01:19,007959000163983,TRAFFIC,end,2561,2024/06/24 15:01:19,192.168.44.43,10.130.11.2,0.0.0.0,0.0.0.0,GP-Access-Organization-Services-Applications,company\user1,,ssl,vsys1,GP-VPN,Trust,tunnel.21,ethernet1/4,splunk-forwarding,2024/06/24 15:01:19,1269402,1,61723,443,0,0,0x47a,tcp,allow,33254,13498,19756,210,2024/06/24 14:36:36,1454,White-List,,7352086992805546250,0x0,192.168.0.0-192.168.255.255,10.0.0.0-10.255.255.255,,105,105,tcp-rst-from-client,0,0,0,0,,pafw01,from-policy,,,0,,0,,N/A,0,0,0,0,09a8fe83-e848-4cbb-bdff-0d35a4ce96b2,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2024-06-24T15:01:20.681+03:00,,,encrypted-tunnel,networking,browser-based,4,"used-by-malware,able-to-transfer-file,has-known-vulnerability,tunnel-other-application,pervasive-use",,ssl,no,no,0

[PickleRick]

OK. So this is the second case I mentioned.

How do you decide then if it's a single session or two separate sessions? Are the events occuring repeatedly while the user is logged in?

[ibralah93]

As longs as events are present then the user is logged in, my goal is to calculate total time where there are events

[PickleRick]

Again - what do you mean by "as long as events are present"? How should Splunk know that the events are from two separate sessions? That's not me nitpicking - that's a question about how to build such search.

[ibralah93]

Sadly, no there is no field for login/logout, this is why I am trying to calculate based on if there are events or activity for each user.
Filtration is being made by source zone field.

Sample event:

	
Jun 24 15:01:20 10.50.8.100 1 2024-06-24T15:01:20+03:00 pafw01.company.com.sa - - - -  1,2024/06/24 15:01:19,007959000163983,TRAFFIC,end,2561,2024/06/24 15:01:19,192.168.44.43,10.130.11.2,0.0.0.0,0.0.0.0,GP-Access-Organization-Services-Applications,company\user1,,ssl,vsys1,GP-VPN,Trust,tunnel.21,ethernet1/4,splunk-forwarding,2024/06/24 15:01:19,1269402,1,61723,443,0,0,0x47a,tcp,allow,33254,13498,19756,210,2024/06/24 14:36:36,1454,White-List,,7352086992805546250,0x0,192.168.0.0-192.168.255.255,10.0.0.0-10.255.255.255,,105,105,tcp-rst-from-client,0,0,0,0,,pafw01,from-policy,,,0,,0,,N/A,0,0,0,0,09a8fe83-e848-4cbb-bdff-0d35a4ce96b2,0,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2024-06-24T15:01:20.681+03:00,,,encrypted-tunnel,networking,browser-based,4,"used-by-malware,able-to-transfer-file,has-known-vulnerability,tunnel-other-application,pervasive-use",,ssl,no,no,0

[PickleRick]

This is a fairly common question here. Use the search for other users' solutions.

The answer will depend on what data you have about your user - most importantly whether you have a separate "log in" and "log out" events for a user or you simply have one kind of a "presence" event which is supposed to be logged in fairly regular intervals as long as the user is logged in and its absence means that user has disconnected?

Additional caveat with the log in/log out events is - what if the user disconnected without logging out (or the log out event simply got "lost" - which can happen when receiving data via UDP syslog).

Anyway, you will need to use streamstats to either find last log in for a given log out or find the previous event to decide whether the current one is sufficiently "far back" to constitute a new session.

[ITWhisperer]

You presumably have more detail in your events which tell you that the user wasn't logged on for all this time? Please share some anonymised representative events which would allow you to determine when the user logged on and when they logged off. Alternatively, do the event have some sort of session identifier which allows you to determine how long each session lasted?