How to get which User not logged into Splunk

[harishsplunk7]

I want to show which User not logged into Splunk for last 30 or 90days in splunk

For example: we have 300 user have access to splunk UI, I want to know who is not logged into splunk more than 7 days 

Below query will show who has logged into splunk, but i wanted to show the who is not logged and last login time information.

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

[jawahir007]

Try this:

| rest /services/authentication/users
| rename title as user
| table user realname roles email
| join type=left user 
[search index=_audit sourcetype=audittrail action=success AND info=succeeded
| stats max(_time) as last_login_time by user
| where last_login_time > relative_time(now(), "-7d")
| table user last_login_time ]
| where isnull(last_login_time) OR last_login_time < relative_time(now(), "-7d")

------

If you find this solution helpful, please consider accepting it and awarding karma points !!

[harishsplunk7]

thank you for your response, I have tried your query but not getting the user not logged in for last 7 days 30d or 90d. By selecting the time range it should automatically show the result which user not logged into splunk web UI.  For example we have 100 account in user list, only 10 users are actively login in, remaining user need to identify the when they last logged into splunk. 

[jawahir007]

@harishsplunk7  I hope this search will help you ..

| rest /services/authentication/users splunk_server=local
| table title, realname, last_successful_login
| rename title AS username | addinfo
| eval status=if(last_successful_login>info_min_time,"User logged in during the selected time range","User Not logged in during the selected time range")
| convert ctime(*_login) ctime(*_time)|fields - *_time, info_sid

------

If you find this solution helpful, please consider accepting it and awarding karma points !!

[isoutamo]

Hi
I think that you need a separate lookup file, which contains all users, which have capability to login into splunk. If user hasn't ever logged in, then (depending how you have configured your users like splunk user, LDAP user, SSO users) it's quite probably that you haven't those names on your system. For that reason rest cannot return those to you.

You need just replace that subquery [|rest....] on @richgalloway 's answer with inputlookup query for those user accounts.

r. Ismo

[richgalloway]

Finding something that is not there is not Splunk's strong suit.  See this blog entry for a good write-up on it.

https://www.duanewaddle.com/proving-a-negative/

In this case, what you have just needs a little tweaking.

index=_audit sourcetype=audittrail action=success AND info=succeeded
| eval secondsSinceLastSeen=now()-_time
| stats count, min(secondsSinceLastSeen) as secondsSinceLastSeen 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

[harishsplunk7]

thank you for your response, I have tried your query but not getting the user not logged in for last 7 days 30d or 90d. it showing total 0,i need to show by selecting the time range it should automatically show the result which user not logged into splunk web UI.  For example we have 100 account in user list, only 10 users are actively login in, remaining user need to identify the when they last logged into splunk.