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!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...