Splunk Search

How do I edit my search to identify inactive users over the last 12 months?

IRHM73
Motivator

Hi, I wonder whether someone could help me please.

I'm trying to create a search which identifies inactive users over the last 12 months (time period selected by 'time picker').

I found the following search which returns a list of current user accounts which works:

|rest /services/authentication/users splunk_server=local 
|fields realname
|rename realname as user
|table user

I then found a search as shown below which displays the last time a user logged on, which again works.

index=_audit action="login attempt"
|stats max(timestamp) by user

So I've tried to amalgamate these so that only the users which appear in the first search, but not in the second will be reported and I came up with the following:

|rest /services/authentication/users splunk_server=local 
|fields realname
|rename realname as user
|eval user1="Y"
|table user user1
|join type=outer user[search index=_audit action="login attempt"
|stats max(timestamp) by user
|eval user2="Y"
|where user1 = "Y" AND user2 != "Y"
|table user]

The problem I have is that the list returned shows all the users irrespective of whether they have logged on in the last year.

I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

Try this instead:

| rest /services/authentication/users splunk_server=local 
| fields realname, title
| rename title as user
| join user type=left [
   | search index=_audit action="login attempt" earliest=-12mon
   | stats max(timestamp) as _time by user, sourcetype
]
| where isnull(sourcetype)
| fields - sourcetype

View solution in original post

chimell
Motivator

Hi
Try this search code

|rest /services/authentication/users splunk_server=local|rename realname as user|fields user|append [search index=_audit action="login attempt" [|rest /services/authentication/users splunk_server=local|rename realname as user| stats count by user] |stats max(timestamp) by user|fields user]

IRHM73
Motivator

Hi @chimell, thank you for taking the time to reply to my post but unfortunately this doesn't work because it returns a list of all the users.

Please see the solution from @javiergn.

Kind Regards

Chris

0 Karma

javiergn
SplunkTrust
SplunkTrust

Try this instead:

| rest /services/authentication/users splunk_server=local 
| fields realname, title
| rename title as user
| join user type=left [
   | search index=_audit action="login attempt" earliest=-12mon
   | stats max(timestamp) as _time by user, sourcetype
]
| where isnull(sourcetype)
| fields - sourcetype

IRHM73
Motivator

Hi @javiergn, thank you very much for taking the time to come back to me with this. It was interesting to see you use 'sourcetype' as your count. I hadn't thought about using another field other than the obvious 'user'.

This works great!

Kind Regards

Chris

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi IRHM73,

just had a quick look at it; your subsearch (which is executed first) uses user1="Y" but this is set in the outer search so this will not match. It will be bed time for me soon - no time to think about this further...maybe tomorrow

cheers, MuS

IRHM73
Motivator

Hi @Mus, thank you for taking the time to look at my post.

I'll take a look at the query from the comments you made and take it from there.

Many thanks and kind regards

Chris

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...