topic Re: Find lastest access data for a list of users in Splunk Search

Find lastest access data for a list of users

alucarddjin — Mon, 08 Jul 2019 14:31:22 GMT

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?

Re: Find lastest access data for a list of users

DavidHourani — Mon, 08 Jul 2019 14:49:33 GMT

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

Re: Find lastest access data for a list of users

DavidHourani — Mon, 08 Jul 2019 14:51:59 GMT

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

Re: Find lastest access data for a list of users

alucarddjin — Mon, 08 Jul 2019 15:17:52 GMT

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 🙂