How can I search for users that haven't logged into Splunk for 90+ days?

[splunker969]

Any query help Highly appreciated ? Thanks in advance !
lists accounts in Splunk that have not been used (logon) for 90 days or more .

Is splunk automatically delete user after 90 days ?

[gcusello · Accepted solution]

Hi splunker969,
try something like this

index=_audit earliest=-90d latest=now
| stats count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ] 
| stats sum(count) AS Total BY user
| where Total=0
| table user 

Anyway Splunk doesn't automatically delete users.

Bye.
Giuseppe

[jcourses]

I have used this and it works but how can I add the timestamp of their last login?

[gcusello]

Hi @jcourses,

If you don't have a login in the monitored period you cannot have the last login, if instead you want the last login of the connected users, you could try something like this:

index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ] 
| stats sum(count) latest(_time) AS latest AS Total BY user
| eval latest=if(isnull(latest),"Missing",strftime(latest,"%d-%m-%d %H:%M:%S"))
| table user latest

If you want the last login outside the monitoring period (90 days), you have to build a different monitoring approach: you should schedule a search like the following that every night writes in a lookup (calle e.g. splunk_connections.csv) the list of connected users in the previous day:

index=_audit earliest=-d@d latest=@d
| stats latest(_time) AS latest count BY user
| table user latest
| outputlookup splunk_connections.csv append=true

and then use this lookup to add the last login information to the main search:

index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | lookup splunk_connections.csv user OUTPUT latest AS old_latest | eval count=0 | fields user old_latest count ] 
| stats sum(count) latest(_time) AS latest values(old_latest) AS old_latest AS Total BY user
| eval latest=if(isnull(latest),if(isnull(old_latest),"Missing",strftime(old_latest,"%d-%m-%d %H:%M:%S"),strftime(latest,"%d-%m-%d %H:%M:%S"))
| where total=0
| table user latest

Ciao.

Giuseppe

[jcourses]

HI @gcusello 

I am getting results from: 

index=_audit earliest=-90d latest=now
| stats count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ]
| stats sum(count) AS Total BY user
| where Total=0
| table user

But I want to include the timestamp from when they last logged in within that period and then potentially include it on the dashboard we have setup.

[gcusello]

Hi @jcourses,

see my previous answer.

Ciao.

Giuseppe

[jcourses]

Hi @gcusello 

I'm getting a "Error in 'stats' command: The argument 'AS' is invalid." for both searches. 

Any ideas?

[gcusello]

Hi @jcourses,

sorry: paste error!

This is the correct first:

index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | eval count=0 | fields user count ] 
| stats sum(count) AS Total latest(_time) AS latest BY user
| eval latest=if(isnull(latest),"Missing",strftime(latest,"%d-%m-%d %H:%M:%S"))
| table user latest

this is the correct second:

index=_audit earliest=-90d latest=now
| stats latest(_time) AS latest count BY user
| append [ | rest splunk_server=local /services/authentication/users | rename title AS user | lookup splunk_connections.csv user OUTPUT latest AS old_latest | eval count=0 | fields user old_latest count ] 
| stats sum(count) AS Total latest(_time) AS latest values(old_latest) AS old_latest BY user
| eval latest=if(isnull(latest),if(isnull(old_latest),"Missing",strftime(old_latest,"%d-%m-%d %H:%M:%S"),strftime(latest,"%d-%m-%d %H:%M:%S"))
| where total=0
| table user latest

Ciao.

Giuseppe

[splunker969]

Thanks @cusello

[dbturner18]

What is the | eval count=0| portion for in the rest subsearch?

[dbturner18]

From what I can tell it just adds a value to the blank counts so they can be calculated in the stats call?
@gcusello

[gcusello]

Hi splunker969,
if you don't use count=0, you cannot aggregate and sum results from the main search and the list of all users, so the search doesn't run.
The approach is that you need to have always a value, also when there isn't any result in the main search (that's the problem solved in this way.)

Ciao and next time!
Giuseppe