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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...