topic Re: Searching for active, inactive users. in Splunk Search

Searching for active, inactive users.

michaelnorup — Thu, 19 Aug 2021 09:04:15 GMT

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

Re: Searching for active, inactive users.

jwalthour — Thu, 19 Aug 2021 10:37:52 GMT

How about this:

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

Re: Searching for active, inactive users.

michaelnorup — Thu, 19 Aug 2021 09:56:52 GMT

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

Re: Searching for active, inactive users.

jwalthour — Mon, 08 Nov 2021 11:52:58 GMT

Same approach:

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

| stats count by user

| where count=1

Re: Searching for active, inactive users.

michaelnorup — Thu, 19 Aug 2021 11:13:50 GMT

Hi.

Doesnt this only find users who are inactive?

Re: Searching for active, inactive users.

jwalthour — Thu, 19 Aug 2021 11:21:49 GMT

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.

Re: Searching for active, inactive users.

michaelnorup — Thu, 19 Aug 2021 11:49:35 GMT

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

Re: Searching for active, inactive users.

jwalthour — Thu, 19 Aug 2021 12:32:34 GMT

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:

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.

Re: Searching for active, inactive users.

michaelnorup — Thu, 28 Oct 2021 09:25:19 GMT

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

Re: Searching for active, inactive users.

michaelnorup — Thu, 04 Nov 2021 09:39:38 GMT

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

Re: Searching for active, inactive users.

jwalthour — Thu, 04 Nov 2021 11:10:06 GMT

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.

Re: Searching for active, inactive users.

michaelnorup — Thu, 04 Nov 2021 14:40:22 GMT

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.

Re: Searching for active, inactive users.

michaelnorup — Mon, 08 Nov 2021 07:36:49 GMT

@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

Re: Searching for active, inactive users.

jwalthour — Mon, 08 Nov 2021 11:56:33 GMT

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

Re: Searching for active, inactive users.

michaelnorup — Mon, 08 Nov 2021 11:59:41 GMT

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