User not logged into Splunk for last 30 or 90days in splunk

[harishsplunk7]

how to get the user not logged into Splunk for last 30 or 90days in splunk using audit or _internal index.

[gcusello]

Hi @harishsplunk7 ,

please try this:

index=_audit tag=authentication info=succeeded earliest=-30d@d latest=now
| stats count BY user
| append [ 
     | rest /services/authentication/current-context 
     | where NOT username="splunk-system-user" 
     | eval count=0 
     | rename username AS user 
     | fields user ]
| stats sum(count) AS total BY user
| where count=0

Ciao.

Giuseppe

[harishsplunk7]

I have tried the below query as per your suggestion, But not getting the result,

index=_audit sourcetype=audittrail action=success AND info=succeeded
| eval secondsSinceLastSeen=now()-_time
| eval timeSinceLastSeen=tostring(secondsSinceLastSeen, "duration")
| stats count BY user timeSinceLastSeen
| append
[| rest /services/authentication/users 
| rename title as user
| eval count=0
| fields user  ]
| stats sum(count) AS total BY user timeSinceLastSeen,

[ITWhisperer]

Why have you got timeSinceLastSeen in the by clause - this was not suggested by @gcusello - what do you get when you do exactly as suggested?

[harishsplunk7]

your query is showing who is successfully logged into splunk.. not the user not logged in splunk. 

[gcusello]

Hi @harishsplunk7 ,

using my original search, you are checking if the users defined on your Splunk did a login in the last 30 days are present, if not (count=0) they are listed, in other words: the users not logged in Splunk in the last 30 days.

Why my search doesn't run for you?

the only check that you can performa is if (or when) users did last login, there isn't a not login trace.

Adding timeSinceLastSeen tge added list of users isn't considered in the count so you cannot check them.

Ciao.

Giuseppe

[ITWhisperer]

It depends on the retention period of your indexes - essentially you need the latest time by user but if your retention period is not large enough you may not find the user you are looking for - all that tells you is that there is no record for the user, which may or may not be useful.

[harishsplunk7]

I am looking the for the search query to show of any of the user not logged into splunk. 

For example, we have 1500 user accounts but only 1200 user logged into splunk for last 90 days and remaining 300 user are not logged, so i want to list the 300 users. i have retention period of 1 year.