topic Re: How do I edit my search to identify inactive users over the last 12 months? in Splunk Search

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

IRHM73 — Wed, 10 Feb 2016 08:32:58 GMT

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

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

MuS — Wed, 10 Feb 2016 09:00:16 GMT

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

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

IRHM73 — Wed, 10 Feb 2016 09:06:37 GMT

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

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

javiergn — Wed, 10 Feb 2016 09:08:14 GMT

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

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

chimell — Wed, 10 Feb 2016 09:53:36 GMT

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]

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

IRHM73 — Wed, 10 Feb 2016 10:01:11 GMT

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

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

IRHM73 — Wed, 10 Feb 2016 10:04:37 GMT

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