Splunk Search

Find lastest access data for a list of users

alucarddjin
Path Finder

I have a lookup list of users and I want to get that date off their last event (or empty if no event) but I keep getting an various errors. So far I have:

| inputlookup Users.csv 
    [ search sourcetype=DataSource event="GRANTED" 
    | stats max(_time) AS lastUsed by Username
    | rename Username AS samAccountName ]

So I get my lookup list of users, start the subsearch pull back a list of usernames and last event times then rename the user name to match the column in my lookup list.

My first assumption on this is that maybe you can't use the subsearch in a lookup so if this is wrong how can find the last time of each user on the list even if there is no match?

0 Karma
1 Solution

DavidHourani
Super Champion

Hi @alucarddjin,

You could do it like this :

 search sourcetype=DataSource event="GRANTED"  [| inputlookup Users.csv | rename samAccountName AS Username]
 | stats max(_time) AS lastUsed by Username

It should give you the list of users and their last time.

Cheers,
David

View solution in original post

0 Karma

DavidHourani
Super Champion

Hi @alucarddjin,

You could do it like this :

 search sourcetype=DataSource event="GRANTED"  [| inputlookup Users.csv | rename samAccountName AS Username]
 | stats max(_time) AS lastUsed by Username

It should give you the list of users and their last time.

Cheers,
David

0 Karma

DavidHourani
Super Champion

You can also keep the values that are int he CSV but not in the logs by doing the following :

  search sourcetype=DataSource event="GRANTED"  [| inputlookup Users.csv | rename samAccountName AS Username]
  | stats max(_time) AS lastUsed by Username
  | append [| inputlookup Users.csv| rename samAccountName AS Username| eval lastUsed=0  ]
  | stats max(lastUsed) AS lastUsed by Username
0 Karma

alucarddjin
Path Finder

Thanks for that David. The initial subsearch with a lookup was causing a big overhead for some reason but if I turn that into a normal lookup:

| lookup Users samAccountName AS sourceUserName OUTPUT Type AS Type

Then use your append on the bottom of it it runs a lot faster.

Thanks again 🙂

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Admins and Analyst can benefit from:  Seamlessly route data to your local file system to save on storage ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...