report showing when users last logged in to splunk - suspicious of results

[vincenp2]

|rest /services/authentication/users splunk_server=<hostname>
| rename title as user | join type=left user [|search index=_audit | dedup user | convert ctime(_time) as last_login | table user last_login]
| table user,email,last_login

This report brings back a list of users and their email addresses - I am wanting to use this to identify any users who have NOT logged in for more than 70 days, so that potentially their userid can be removed as no longer required. Assumption is that if the last_login field is empty these users have not logged in

I have used a number of date ranges (All, Date-Range(e.g. between 26 September and 6 December), Relative (e.g. last 70 days), but the dates in the last_login field only seem to go as far back a few weeks, when I would have expected to see a spread of dates, going back a long way if using the 'All' option for example

could anyone advise why I am only getting recent dates, or perhaps a better query to obtain the results I need please?

Thank you

[kamlesh_vaghela · Accepted solution]

Hi @vincenp2,

Can you please try this?

| rest /services/authentication/users splunk_server=<hostname> 
| rename title as user 
| join type=left user 
    [| search index=_audit 
    | dedup user 
    | rename _time as time 
    | table user time] 
| table user,email,time 
| eval diff = now() - time , secsfordays= (70 * 86400)
| where diff > secsfordays

Thanks

[vincenp2]

Many thanks - I do seem to get the same results, which perhaps suggests that these are accurate, and it is me that should trust the results 🙂

[LeeSart]

By default, data in the _audit index is only kept for 30 days. Assuming the rest API gets the data from the same source, this would explain why you're only seeing recent results.