Security

How to calculate percentage of usage on VPN

rykermurdock77
Explorer

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.

Tags (2)
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
0 Karma

rykermurdock77
Explorer

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.

0 Karma

rykermurdock77
Explorer

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

0 Karma

to4kawa
Ultra Champion
(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?

0 Karma

rykermurdock77
Explorer

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma

sandeepmakkena
Contributor
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!

0 Karma

sandeepmakkena
Contributor

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

0 Karma

rykermurdock77
Explorer

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.

0 Karma

sandeepmakkena
Contributor

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

0 Karma

rykermurdock77
Explorer

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)

0 Karma

sandeepmakkena
Contributor
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.

0 Karma

sandeepmakkena
Contributor

Did you try the other query in the comment section?

0 Karma

rykermurdock77
Explorer

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

0 Karma

sandeepmakkena
Contributor

Are you getting the percentage?

0 Karma

rykermurdock77
Explorer

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

0 Karma

rykermurdock77
Explorer

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.

0 Karma

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

rykermurdock77
Explorer

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.

0 Karma

sandeepmakkena
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...