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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...