Security

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

splunker969
Communicator

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 ?

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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
Loves-to-Learn

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

jcourses
Loves-to-Learn

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @jcourses,

see my previous answer.

Ciao.

Giuseppe

0 Karma

jcourses
Loves-to-Learn

Hi @gcusello 

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

Any ideas?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

splunker969
Communicator

Thanks @cusello

0 Karma

dbturner18
Loves-to-Learn Lots

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

0 Karma

dbturner18
Loves-to-Learn Lots

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...