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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...