Reporting

How to calculate the total vpn connection time per user?

jfeitosa_real
Path Finder

Hi All!

Can anyone help with these questions below?
How to calculate the total vpn connection time per user?
The Duration field is of type string.

"Jul 10 07:14:17 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zya3, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: AnyConnect-Parent, Duration: 0h:41m:42s, Bytes xmt: 27921408, Bytes rcv: 4612882, Reason: User Requested

Jul 9 23:55:49 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zya3, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: SSL, Duration: 0h:11m:46s, Bytes xmt: 13452434, Bytes rcv: 5072740, Reason: User Requested

Jul 9 21:36:12 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zzw2, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: SSL, Duration: 14h:38m:38s, Bytes xmt: 487160561, Bytes rcv: 39385026, Reason: User Requested"

 

- Through the eval command you can concatenate the corresponding fields of the numeric type.

| transaction user endswith="duration:" keepevicted=true| eval full_duration = duration_hour.":".duration_minute.":".duration_second

@woodcock you are the man"

Thanks

Labels (2)
0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults
| eval _raw="Jul 10 07:14:17 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zya3, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: AnyConnect-Parent, Duration: 0h:41m:42s, Bytes xmt: 27921408, Bytes rcv: 4612882, Reason: User Requested

Jul 9 23:55:49 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zya3, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: SSL, Duration: 0h:11m:46s, Bytes xmt: 13452434, Bytes rcv: 5072740, Reason: User Requested

Jul 9 21:36:12 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zzw2, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: SSL, Duration: 14h:38m:38s, Bytes xmt: 487160561, Bytes rcv: 39385026, Reason: User Requested"
| multikv noheader=t
| table _raw
| kv
| rex "(?<date>\w+ \d+ \S+)"
| rex "(?<status>Session \w+)\. Session Type: (?<type>\S+), Duration: (?<duration>\S+),"
| rex field=duration "(?<hours>\d*)h:(?<minutes>\d*)m:(?<seconds>\d*)s"
| eval _time=strptime(date,"%b %d %T")
| eval duration_sec=hours * 60 * 60 + minutes * 60 + seconds
| eventstats sum(duration_sec) as total_duration by Username

View solution in original post

marshalb
New Member

A VPN just moves all your traffic through an encrypted tunnel to a different network...

0 Karma

to4kawa
Ultra Champion

I see, that's VPN.

0 Karma

to4kawa
Ultra Champion
| makeresults
| eval _raw="Jul 10 07:14:17 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zya3, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: AnyConnect-Parent, Duration: 0h:41m:42s, Bytes xmt: 27921408, Bytes rcv: 4612882, Reason: User Requested

Jul 9 23:55:49 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zya3, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: SSL, Duration: 0h:11m:46s, Bytes xmt: 13452434, Bytes rcv: 5072740, Reason: User Requested

Jul 9 21:36:12 xxx.xxx.xxx.xx %ASA-4-113019: Group = XYZ-SSL, Username = zzw2, IP = xxx.xxx.xxx.xx, Session disconnected. Session Type: SSL, Duration: 14h:38m:38s, Bytes xmt: 487160561, Bytes rcv: 39385026, Reason: User Requested"
| multikv noheader=t
| table _raw
| kv
| rex "(?<date>\w+ \d+ \S+)"
| rex "(?<status>Session \w+)\. Session Type: (?<type>\S+), Duration: (?<duration>\S+),"
| rex field=duration "(?<hours>\d*)h:(?<minutes>\d*)m:(?<seconds>\d*)s"
| eval _time=strptime(date,"%b %d %T")
| eval duration_sec=hours * 60 * 60 + minutes * 60 + seconds
| eventstats sum(duration_sec) as total_duration by Username

jfeitosa_real
Path Finder

Hello @to4kawa,

I decided to do it just by calculating the total in seconds.

index=vpn eventtype=cisco_vpn_end Username=* Group="*"
| fillnull value=0 duration_day
| multikv noheader=t
| eval total_duration=duration_day * (86400) + duration_hour * 60 * 60 + duration_minute * 60 + duration_second
| stats sum(total_duration) as Total_duration by Username Group
| eventstats sum(total_duration) as Total_duration by Username duration_day duration_hour duration_minute duration_second
| sort - Total_duration

So I did another research with more details of the navigation, making a statistic by the time and user.

index=vpn (eventtype=cisco_vpn_start OR eventtype=cisco_vpn_end) user=*
| transaction user endswith="duration:" keepevicted=true
| fillnull value=0 duration_day
| eval full_duration = duration_day."d-" .duration_hour.":" .duration_minute.":".duration_second
| eval bytesMB=round(((bytes/1024)/1024),2), bytes_inMB=round(((bytes_in/1024)/1024),2), bytes_outMB=round(((bytes_out/1024)/1024),2)
| eval Start_time=strftime(_time,"%Y/%m/%d %H:%M:%S"), End_time=(strftime(_time + duration,"%Y/%m/%d %H:%M:%S")), Total_time=if(isnull(full_duration), Start_time." --> current session",Start_time." --> ".End_time)
| mvexpand src
| iplocation src
| eval LocationIP=City.", ".Country
| stats values(Total_time) as "Session Time" values(src) as "PublicIP" values(LocationIP) as LocationIP values(IP) as "Assigned IP" values(reason) as "Termination Reason" values(bytesMB) as bytesMB values(bytes_inMB) as bytes_inMB values(bytes_outMB) as bytes_outMB values(full_duration) as Duration by _time, user
| sort -_time
| search PublicIP=*

 

Thank you so much for your help, it was awesome.

James \o/

0 Karma

jfeitosa_real
Path Finder

Hi, @to4kawa, thanks for your response.

But how to calculate the total time per user of the duration field, since this is a string type? For example, if in two events of the same user, the duration field has the values (0h: 47m: 42s and 0h: 18m: 46s) that total time would be (01h: 06m: 28s). It would be possible?

Thx.

 

 

 

0 Karma

to4kawa
Ultra Champion

try following REX

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...