How to calculate the total vpn connection time per user?

[jfeitosa_real]

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

[marshalb]

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

[to4kawa]

I see, that's VPN.

[to4kawa · Accepted solution]

| 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]

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/

[jfeitosa_real]

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.

[to4kawa]

try following REX