How do you search users who were not logged in the past 30 days?

[ruchijain]

Hi,

I am trying to search for a list of users who have not logged into the Splunk environment in the past 30 days.

Can you please look into the below query and let me know what is not correct in that?

index=_internal sourcetype=splunkd_access | eval length=len(user) | search length>1 | eval Time=strptime(_time,"%Y-%m-%d") | eval Before30days=relative_time(now(),"-30d@d") |where Time

[chandan]

Please check below query guys the best result i have got,

| `inactive_accounts(30)` | eval LastTime=strftime(lastTime,"%Y-%m-%d %H:%M:%S.%Q") | sort -_time

happy splunking!!!

[harishalipaka · Accepted solution]

hi @ruchijain

try this

index=_internal sourcetype=splunkd_ui_access user!="-"    
 | stats earliest(_time) AS StartTime latest(_time) AS EndTime count by user date_mday    
 | join type=left user         [        
      | rest /services/authentication/users                
      | rex field=id "https:\/\/127.0.0.1:8089\/(\w+\/)+(?<user>\w+)"                
      | rename realname AS Name               
      | fields user 
       ]    
 | search user=*    
 | eval         
      Duration=tostring(EndTime-StartTime,"Duration"),        
      StartTime=strftime(StartTime,"%d/%m/%Y %H.%M.%S"),        
      EndTime=strftime(EndTime,"%d/%m/%Y %H.%M.%S")    
 | sort user
 | table user StartTime EndTime Duration | dedup user

[ruchijain]

Thanks it gives the whole list....

[ruchijain]

This shows only one record with user as "testuser" which is not correct there are many users who have not logged in to the Splunk environment.
Can you please let me know what else can be used.
Or if you can let me know how we can check when each user last logged in with the help of that also we can find who all cannot logged in from past 30 days

[chrisyounger]

Here is one way to do it using the audit log

index=_audit splunk_server=local action=search user=* 
| stats latest(_time) as last_search by user 
| append 
    [| rest /services/authentication/users 
    | eval user = title 
    | fields user ] 
| stats last(*) as *
| eval days_since_last_search = round((time() - last_search) / 86400,2)

You should check how far back your audit log goes.

[ruchijain]

It only shows one result can you please check and let know.
Or if you cant let know how we can check last when each user login so that this will also provide the details to me.