topic Re: How do you search users who were not logged in the past 30 days? in Splunk Search

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

ruchijain — Mon, 04 Feb 2019 16:50:23 GMT

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

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

chrisyounger — Mon, 04 Feb 2019 18:30:41 GMT

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.

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

ruchijain — Tue, 05 Feb 2019 09:13:48 GMT

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

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

ruchijain — Tue, 05 Feb 2019 09:43:35 GMT

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.

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

harishalipaka — Tue, 05 Feb 2019 10:41:21 GMT

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

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

ruchijain — Tue, 05 Feb 2019 11:13:29 GMT

Thanks it gives the whole list....

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

chandan — Wed, 17 Feb 2021 10:36:54 GMT

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!!!