Add time clause on the second search of a subsearch

[mchandrasekaran]

I am writing a query to find if a account got locked out because of an attack or because of an account change that happened in the last 7 days before the account got locked out.

I am using subsearch and AD eventcodes to do this : the subsearch looks for the account that got locked out, passes the user and the time(_time). The second half filters the accounts which have had account changes. However, I am not able to add the clause to check if that password change happened seven days before the time at which the account got locked out. How do I add this? I tried the following :

• using earliest=_time - (3600*168) latest=_time at the end of the second search
• _time>-7d@d

but both doesn't work.

Can any of you think of a way to do this?

[alemarzu]

Hi @mchandrasekaran

This obviously needs hardcore improvement but it might help you.

index=wineventlog sourcetype=WinEventLog:Security EventCode=4740 
| eval Account=mvindex(Account_Name, 1) 
| regex Account!="\\$"  
| eval blockTime=_time
| eval modtime=if(blockTime > relative_time(now(), "@d"), "Today", "PreviousDays")
| search modtime=Today
| stats latest(_time) AS accblockTime, sum(eval(EventCode="4740")) AS AccBlocks by Account modtime 
| convert ctime(accblockTime)
| appendcols [search index=wineventlog sourcetype=WinEventLog:Security (EventCode=4723 OR EventCode=4724 OR EventCode=4625)
| eval Account=mvindex(Account_Name, 1) 
| regex Account!="\\$" 
| eval eventTime=_time
| eval modtime2=if(eventTime < relative_time(now(), "@d"), "PreviousDays", "Today")
| search modtime2=PreviousDays
| stats sum(eval(EventCode="4724")) AS PwdResets, sum(eval(EventCode="4723")) AS PwdChanges, sum(eval(EventCode="4625")) AS LoginFailures by Account modtime2]
| table Account AccBlocks accblockTime PwdResets PwdChanges LoginFailures

So, this will give you the amount of account blocks and the latest block's timestamp per user for the current day. After that, this will search for any password resets, password changes and login failures for each one of them during the previews days.

You can also add some case statement to alert based on certain conditions.

Hope it helps.

[mchandrasekaran]

Hi @alemarzu,

Thank you so much, but the part after the password reset did not work for me (that is the append part). However, I am thinking of using lookup table to save the account names and the dates on which they changed their password. Then search for account lockouts, and return those accounts which are not present in the lookup table. If the account is present in the lookup table, return if the date of password change is not before the date of account lockout. Do you think this is a feasible idea? or would it be better to take a different approach?

[alemarzu]

Hi there @mchandrasekaran

Thats weird, I've tested it on prod data and it's working. May I ask, why do you believe is not working after the append?

Regarding the new approach, its doable for sure. Avoid gigantic CSV's.

[mchandrasekaran]

I think It isn't working because the fields PwdResets,LoginFailures and password changes are not getting populated for any of the users. There are users that have 4625 and 4740, I checked that as well.

[alemarzu]

Thats okey then, those fields could be empty. If the user has an account block (4740) the login failures (4625) might cause that.

[mchandrasekaran]

yeah, but you aren't doing a filter on whether the account lockout is caused by the login failure or not,right?

[alemarzu]

Exactly, thats why at the end of the post I've wrote that you could add some case statements to alert when certain conditions are met.

[sundareshr]

See if this gives you what you're looking for...

source=wineventlog:security (EventCode=627) earliest=-7d@d | stats values(EventCode) as Code latest(_time) as AcctLock by user | append [ search source=wineventlog:security EventCode=4740 earliest=@d | stats values(EventCode) as Code latest(_time) as PwdChg by user ] | stats latest(AcctLock) as AcctLock latest(PwdChg) as PwdChg by user | eval days=PwChg-AcctLock | eval reason=if(days<=(86400*7), "Hacker", "PwdChg") 

[mchandrasekaran]

Thank you sundareshr, but no it is not giving the result I am looking for. It is just filtering on events that have 627. Those users don't have the eventcode 4740.

[lguinn2]

I am sure we can figure this out - but it is hard for me in the abstract. Can you post the actual search that you are using?

[mchandrasekaran]

This is the search I am using :

source=wineventlog:security "EventCode=627" OR "EventCode=4740" [|search source=wineventlog:security EventCode=4740 |  fields user,_time] | search "EventCode=627"  | table user,_time

In case using a subsearch isn't the right way to do it, I am thinking of using lookup tables to do the task.

[MuS]

Hi mchandrasekaran, take a look at this answer https://answers.splunk.com/answers/185829/how-to-create-a-custom-macro-function-inside-the-s.html where you can learn how this can be done using stats and some eval around time to get results from different time ranges in one single search without the limits of sub searches ... map reduce FTW! 🙂

[mchandrasekaran]

Thank you MuS. Yes, I will take a look at that.