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
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
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]
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
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
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
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
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