Splunk Search

Searching for active, inactive users.

michaelnorup
Loves-to-Learn Everything

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

Labels (1)
Tags (2)
0 Karma

jwalthour
Communicator

How about this:

index=_audit sourcetype=audittrail CASE(action=login attempt) CASE(info=succeeded) earliest=-90d
| stats count by user
| where count=1

0 Karma

michaelnorup
Loves-to-Learn Everything

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

0 Karma

jwalthour
Communicator

Same approach:

index=windows source=WinEventLog:Security EventCode=4624 earliest=-90d

| stats count by user

| where count=1

0 Karma

michaelnorup
Loves-to-Learn Everything

Hi.

Doesnt this only find users who are inactive?

0 Karma

jwalthour
Communicator

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.

0 Karma

michaelnorup
Loves-to-Learn Everything

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

0 Karma

jwalthour
Communicator

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.

0 Karma

michaelnorup
Loves-to-Learn Everything

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. 

0 Karma

michaelnorup
Loves-to-Learn Everything

@jwalthour 

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

0 Karma

jwalthour
Communicator

What do you mean “show if changes are made”?

0 Karma

michaelnorup
Loves-to-Learn Everything

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

0 Karma

michaelnorup
Loves-to-Learn Everything

@jwalthour Hi JWalthour. Can you still assist me with this? 🙂

0 Karma

jwalthour
Communicator

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.

0 Karma

michaelnorup
Loves-to-Learn Everything

You might be right, but im not sure how to do this correctly

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!