Splunk Search

Find lastest access data for a list of users

Explorer

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

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

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

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

Explorer

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!