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

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