How to calculate percentage of usage on VPN

[rykermurdock77]

i'm trying to build a report of VPN users/systems that are using VPN 80% of the time.

Here is what I have so far:

index=***** sourcetype="cisco:asa" (eventtype="cisco_vpn_start" OR eventtype="cisco_vpn_end") src_ip=* user=*
|stats earliest(_time) as vpn_start,latest(_time) as vpn_end by user, src_ip
|eval diff_in_minutes=(vpn_start-vpn_end)/60

This gives me an excellent report of how long the user is on, but I could use some help with the maths on figuring what percentage they are on. It was suggested to use Kerberos entries to figure how long they are on the network vs how long they are on VPN.

New to splunk, any help is appreciated.

[woodcock · Accepted solution]

Like this:

(index=vpnindex AND sourcetype="cisco:asa" AND (eventtype="cisco_vpn_start" OR  eventtype="cisco_vpn_end") AND src_ip="*" AND user="*")
OR
(index="wineventlog" AND source="wineventlog:security" AND (EventCode="4624" OR EventCode="4634") AND NOT src_ip="::1")
| streamstats count(eval(eventtype="cisco_vpn_end")) AS sessionID4ASA count(eval(EventCode="4634")) AS sessionID4Win BY index src_ip user
| eval sessionID = coalesce(sessionID4ASA, sessionID4Win)
| stats min(_time) AS _time range(_time) AS duration values(eventtype) AS eventtype BY src_ip user sessionID index
| addinfo
| eval duration = case(
    duration == 0 AND (eventtype="cisco_vpn_start" OR EventCode=="4624"), now() - _time,
    duration == 0 AND (eventtype="cisco_vpn_end" OR EventCode=="4634"), _time - info_min_time,
    true(), duration)
| bin _time span=1d
| stats sum(duration) AS duration BY _time index src_ip user
| eval pctVPN = 100 * vpnindex / (vpnindex + wineventlog)
| where pctVPN > 80

[rykermurdock77]

Thank you. Unfortunately the search keeps timing out. I'm not finding anything at 30 min, trying up to 4 hrs, but the customer needs it to look over the space of 30 days.

[rykermurdock77]

Actually, I got it. Had to tweak a few things. Thank you for the help!

[to4kawa]

(sourcetype="cisco:asa" (eventtype="cisco_vpn_start" OR eventtype="cisco_vpn_end"))
OR (index="wineventlog" source="wineventlog:security" (EventCode=4624 OR EventCode=4634))
| stats earliest(eval(if(EventCode==4624,_time,NULL))) as user_login_time, latest(eval(if(EventCode==4634,_time,NULL))) as user_logout_time, earliest(VPN_login) as VPN_start, latest(VPN_logout) as VPN_end by user, src_ip
| eval User_On_VPN_minutes = round((VPN_end - VPN_start)/60)
| eval User_On_Network_minutes = round((user_logout_time - user_login_time)/60)
| eval perc_On_VPN = ( User_On_VPN_minutes / User_On_Network_minutes) * 100
| where perc_On_VPN > 80

Hi, Since the way of writing eval was different, it corrected.
how about it?

[rykermurdock77]

The argument 'earliest_time(eval(if(EventCode==4624,_time,NULL)))' is invalid.

[to4kawa]

thats's right.
| stats earliest(eval(if(EventCode==4624,_time,NULL))) as user_login_time, latest(eval(if(EventCode==4634,_time,NULL))) as user_logout_time, earliest(VPN_login) as VPN_start, latestVPN_logout) as VPN_end by user, src_ip

[sandeepmakkena]

index= sourcetype="cisco:asa" (eventtype="cisco_vpn_start" OR eventtype="cisco_vpn_end") src_ip= user=*    
  | stats earliest_time(user_login) as user_logout_time,latest_time(user_logout) as user_login_time, earliest_time(VPN_login) as VPN_start latest_time(VPN_logout) as VPN_end by user, src_ip
    | eval User_On_VPN_minutes = (VPN_end-VPN_start)/60
    | eval User_On_Network_minutes = (user_logout_time-user_login_time)/60
    | eval perc_On_VPN = (User_On_Network_minutes/User_On_VPN_minutes) * 100."%"
    | where perc_On_VPN > 80%

You can replace user_login, user_logout, VPN_login and VPN_logout with your specific events.
Hope this helps, Thanks!

[sandeepmakkena]

if this helped accept the answer please or comment if you have any questions.

[rykermurdock77]

It has helped, but i'm not sure if it will do exactly what I need to. I'm having some troubles getting the user login and logout to work in the search.

[sandeepmakkena]

Is there an event that is getting generated, when a user does login and logout?

[rykermurdock77]

There is, if you look at the 2nd comment, i'm using events generated from logons and logoffs

index="wineventlog" source="wineventlog:security" (EventCode=4624 OR EventCode=4634)

[sandeepmakkena]

source="wineventlog:security" action=success Logon_Type=2 (EventCode=4624 OR EventCode=4634 OR EventCode=4779 OR EventCode=4800 OR EventCode=4801 OR EventCode=4802 OR EventCode=4803 OR EventCode=4804 ) user!="anonymous logon" user!="DWM-*" user!="UMFD-*" user!=SYSTEM user!=*$ (Logon_Type=2 OR Logon_Type=7 OR Logon_Type=10)
| convert timeformat="%a %B %d %Y" ctime(_time) AS Date 
| streamstats earliest(_time) AS login, latest(_time) AS logout by Date, host
| eval session_duration=logout-login 
| eval h=floor(session_duration/3600) 
| eval m=floor((session_duration-(h*3600))/60) 
| eval SessionDuration=h."h ".m."m " 
| convert timeformat=" %m/%d/%y - %I:%M %P" ctime(login) AS login 
| convert timeformat=" %m/%d/%y - %I:%M %P" ctime(logout) AS logout 
| stats count AS auth_event_count, earliest(login) as login, max(SessionDuration) AS sesion_duration, latest(logout) as logout, values(Logon_Type) AS logon_types by Date, host, user

Try this and see.

[sandeepmakkena]

Did you try the other query in the comment section?

[rykermurdock77]

I did. with some modification it gives me the duration and users etc but it doesn't give me the 80% part

[sandeepmakkena]

Are you getting the percentage?

[rykermurdock77]

I'm not getting any percentage off of the other query

[rykermurdock77]

Still working on this, the last one you posted gives results and logon logoff duration etc. Trying to combine the one above with the previous one and I keep running into errors so can we combine the one above with

index=* sourcetype="cisco:asa" (eventtype="cisco_vpn_start" OR eventtype="cisco_vpn_end") src_ip=* user=*
| eval user_login = if(EventCode=4624,1,0)
| eval user_logout = if(EventCode=4634,1,0)
| stats earliest_time(user_login) as user_login_time, latest_time(user_logout) as user_logout_time, earliest_time(VPN_login) as VPN_start, latest_time(VPN_logout) as VPN_end by user, src_ip
| eval User_On_VPN_minutes = (VPN_end-VPN_start)/60
| eval User_On_Network_minutes = (user_logout_time-user_login_time)/60
| eval perc_On_VPN = (User_On_Network_minutes/User_On_VPN_minutes) * 100."%"
| where perc_On_VPN > 80%

I was able to put the first callouts with the one above to narrow it down to the source and index.

But getting the rest of the stats out is erroring out. Especially at this line:

| stats earliest_time(user_login) as user_login_time, latest_time(user_logout) as user_logout_time, earliest_time(VPN_login) as VPN_start, latest_time(VPN_logout) as VPN_end by user, src_ip

I'm thinking if this stats line worked, the rest would fall into place.

The error i'm getting is:

Error in 'stats' command: The argument 'earliest_time(user_login)' is invalid.

[sandeepmakkena]

earliest_time() should work with stats. I use it all the time, I am not sure why it's giving you an error.

[rykermurdock77]

I found a good search to give me the Session duration based on logon/logoff.

index="wineventlog" source="wineventlog:security" (EventCode=4624 OR EventCode=4634) earliest=-4h
| eval Date=strftime(_time, "%Y/%m/%d")
| transaction src_ip user startswith=EventCode=4624 endswith=EventCode=4634
| where duration > 60
| search NOT src_ip="::1"
| eval duration = duration/60
| eval duration=round(duration,2)
| table src_ip, user, duration, Date,TaskCategory
| rename duration as "Session_Duration_Minutes"
| sort - Date

how can I combine the two to find out who has been on vpn 80% of the time vs the network.

[sandeepmakkena]

| transaction command will take more resources, try to avoid it. It will work well with small data as the data gets big it'll slow down.