Splunk Search

How do I get a distinct list of users within the span of 1 day over 30 days?

jmaple
Communicator

I'm working on creating a report to monitor VPN usage based on unique user per day. I was able to get the format I want done but I'm having trouble filtering unique users by day over 30 days.

index=network sourcetype=cisco:asa device_name="*vpnfrw01" eventtype=cisco_vpn_* log_level=4 Username=* 
| dedup _time 
| rex "Duration: (?<h>\d+)h:(?<m>\d+)m:(?<s>\d+)s," 
| fillnull value=0 h m s 
| eval duration=h*(60*60)+m*60+s 
| eval end=_time 
| eval start=end-duration 
| eval start=strftime(start, "%x %X") 
| eval duration=h."h:".m."m:".s."s" 
| eval logon = if(group="piv_group", "PIV", "RSA") 
| stats count by start userIdentity logon duration userEmployeeType userDivision userNick userEmail
| table start userIdentity logon duration userEmployeeType userDivision userNick userEmail
| rename start as "Date" duration as "Total Session Time" userNick as "Account Name" logon as "Logon Type" userDivision as Division userEmail as Email userEmployeeType as "Employee Type" userIdentity as "User" 
| sort Date desc 
| search User=*

It's output looks like this:

Date                     User     Logon Type    Total Session Time   Employee Type    Division  Account Name    Email
02/14/2017 7:30         user1   PIV         0h:09m:43s          Contractor         IT       User 1  email1@gmail.com
02/14/2017 6:04         user3   PIV         0h:35m:54s          Contractor           IT     User 3  email3@gmail.com
02/13/2017 23:50        user2   PIV         0h:06m:24s          Contractor         IT       User 2  email2@gmail.com

I need to be able to only find unique users per day keeping this format.

0 Karma

gcusello
Legend

HI jmaple,
If I correctly undertood, you need a new search having as output the total duration per day of each user?
If this is your request try something like this:

index=network sourcetype=cisco:asa device_name="*vpnfrw01" eventtype=cisco_vpn_* log_level=4 Username=* 
 | dedup _time 
 | rex "Duration: (?<h>\d+)h:(?<m>\d+)m:(?<s>\d+)s," 
 | eval end=_time 
 | eval start=end-duration 
 | eval start=strftime(start, "%x %X") 
 | eval logon = if(group="piv_group", "PIV", "RSA")
 | bin start span=1d 
 | stats values(logon) AS "Logon type" values(userEmployeeType) AS "Employee Type" values(userDivision) AS Division values(userNick) AS "Account Name" values(userEmail) AS Email sum(duration) AS duration by start userIdentity 
 | eval duration=tostring(duration,"duration")
 | table start userIdentity "Logon type" duration "Employee Type" Division "Account Name" Email 
 | rename start as "Date"  userIdentity as "User" duration AS "Total Session Time"
 | sort Date desc 

I don't understand why you used dedup _time.

Bye.
Giuseppe

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!