- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Actually, I got it. Had to tweak a few things. Thank you for the help!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
(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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The argument 'earliest_time(eval(if(EventCode==4624,_time,NULL)))' is invalid.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if this helped accept the answer please or comment if you have any questions.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is there an event that is getting generated, when a user does login and logout?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Did you try the other query in the comment section?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I did. with some modification it gives me the duration and users etc but it doesn't give me the 80% part
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you getting the percentage?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm not getting any percentage off of the other query
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
earliest_time() should work with stats. I use it all the time, I am not sure why it's giving you an error.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
| 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.