Reporting

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

vincenp2
New Member

|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

0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

vincenp2
New Member

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 🙂

0 Karma

LeeSart
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...