Hey.
Im trying to create a search that lists users that have for example more than 90 days between the last 2 logons.
I have tried getting the last log on time with this:
index="index" sourcetype="wineventlog:security" EventCode=4624 | stats max(_time) by user
But that doesnt really work for me.
Not sure how i proceed from here however
How about this:
index=_audit sourcetype=audittrail CASE(action=login attempt) CASE(info=succeeded) earliest=-90d
| stats count by user
| where count=1
Hey. Unfortunately it is a different index than the _audit one i need to work on. I edited the original message, maybe after you saw it the first time, so the fields you are using dont all exist
It is a windows index if the eventcode didnt give that away
Thanks though
Same approach:
index=windows source=WinEventLog:Security EventCode=4624 earliest=-90d
| stats count by user
| where count=1
Hi.
Doesnt this only find users who are inactive?
Please let me know if this works for you. You'd run the first search ("index=windows ...") as a scheduled search on a regular frequency (for example, every hour or every day) looking back over a slightly longer period of time. If you ran it daily, you might set the time picker to 2 days or, if every hour, to 2 hours. It will always grab the latest login, so overlap into previous searches will not give you wrong information.
The settings in collections.conf and transforms.conf set up a kvstore collection named "last_login". You could make the same sort of lookup through Lookup Editor if you didn't have CLI access.
Finally, the second search ("|inputlookup last_login ...") is the one to run to get your report of active and inactive users.
collections.conf
[last_login]
accelerated_fields.userhash = {"user":1}
transforms.conf
[last_login]
collection = last_login
external_type = kvstore
fields_list = _key,user,last_login,hash
match_type = WILDCARD(user)
Search to schedule regularly to populate the "last_login" lookup:
index=windows source=WinEventLog:Security eventCode=4624
| stats latest(_time) as last_login by user
| eval hash=md5(user)
| lookup last_login hash OUTPUTNEW _key as _key
| fields user,last_login,hash,_key
| outputlookup last_login append=t
Search to run to get a listing of active and inactive users:
| inputlookup last_login
| eval status = if(last_login < relative_time(now(),"-90d"),"inactive","active")
| convert ctime (last_login)
| table user last_login status
That's it! If it works for you, please mark it as the solution to your question.
Hey sorry for the slow respond, other things came up. Just got back to this, and it just might work.
Which server do you put this collections.conf and transforms.conf on? Search head, indexer, or deploy/license server? This is on multiple indexes like index="*-windows"
Is it on the search head? \program files\splunk\etc\apps\search\local\ ?
Also, does the timepicker on the first search need to be 90 days?
Also, does this just give me a list of active and inactive users, or does it give me an alert if a user goes from inactive -> active?
Thanks
Hey, Michael!
All of this goes on the search head. The first search the lookup you would run “frequently,” that could be hourly, daily, whatever frequency you need. The time picker should overlap the last time you ran the search so as not to miss anything. The lookup just gives you when a user’s last login is. The last search determines “active” vs “inactive”, which I set to be 90 days—if you’ve not seen a user login in 90 days, consider them inactive.
Okay cool thanks. I have added the collections.conf and transforms.conf to the searchhead.
I have scheduled the first search as a report that runs every day, with a time picker of 2 days
Both the first and the last search gives me 0 results however.
Fixed the first search with a capital E in
EventCode
Now we just have to edit the second search to not only show the status of the users, but show if changes are made 🙂
If you edit that i will mark your answer as correct
What do you mean “show if changes are made”?
The last search just shows a list of users with a active/inactive field right?
What i am looking for is if a user goes from inactive -> active
@jwalthour Hi JWalthour. Can you still assist me with this? 🙂
True. Let’s expand the solution, then:
Instead of the “| where count=1”, you could do something like ‘| eval status=if(count>1, “active”,”inactive”)’
This, however, doesn’t completely address the issue. To get the fullest solution, you could create a lookup with columns “user” and “last_login”. Regular searches would update the lookup with the latest login time found. Then, your search would be against the lookup where “now()-last_login” > 90 days.
You might be right, but im not sure how to do this correctly