Using transaction or stats to do event correlation like Vlookup?

[sadkha]

Hi All,

In my scenario, I have a batch of events that are for a particular Event Code, sorted by time.
The fields included in this Event are Account Name, Computer Name, and Account Domain.

There is a separate batch of events for another Event Code, sorted by time. And these fields include Account Name, Computer Name and Client Address.

Basically I would like to run a search for the 1st scenario, however I'd also like to include the corresponding Client Address from the 2nd scenario, if the Account Name matches (for events that occurred at relatively the same time)

Any ideas or tips on how to go about this would be greatly appreciated!

[MuS]

Hi sadkha,

try something like this:

  your base search to get all fields for 1st and 2nd scenario | transaction "Account Name" | ...

or

 your base search to get all fields for 1st and 2nd scenario | stats values("Event Code") values("Computer Name") values("Account Domain") values("Client Address") by "Account Name"

streamstats or eventstats can also be used for such searches.

hope this helps ...

cheers, MuS

[MuS]

Ok, this works for me and is only an example adapt it to your needs:

 sourcetype="WinEventLog:Security" OR sourcetype="WinEventLog:security" ( EventCode=538 OR EventCode=4624 ) | streamstats current=f last(*) AS last_* | where EventCode=4624 AND last_EventCode=538 | stats dc(_time) values(EventCode) values(ComputerName) values(last_EventCode) values(last_ComputerName) by "Account_Name"

Also, watch and double check that all field names match, like EventCode is not equal to eventcode or eventCode
and start with a simple search and only search for the two EventCodes 4771 & 4740

[MuS]

I will test this tomorrow. The example was out of my head. Tomorrow I have some WinEventLog available.....

[sadkha]

I tried this:

index=xxx sourcetype="WinEventLog:Security" OR sourcetype="WinEventLog:security" (EventCode=4771 AND "Audit Failure") OR ("EventCode=672" AND Type="Failure Audit") OR "EventCode=675" OR "EventCode=4740" OR "EventCode=644" | eval "Locked_Account"=coalesce(Win2K8_acc,Target_Account_Name,Account_Name,user) | streamstats current=f last(EventCode) AS last_code | where last_code=4771 AND EventCode=4740 | stats dc(_time) values(Locked_Account) values(EventCode) values(ComputerName) values(Caller_Computer_Name) values(Client_Address) by "Account_Name"

not working. smthing wrong?

[MuS]

try something like this:

 index=xxx Code=101 OR Code=201 | streamstats current=f last(Code) AS last_code | where last_code=201 AND Code=101 | stats values(Last_code) values("Event Code") values("Computer Name") values("Account Domain") values("Client Address") by "Account Name"

based on that you should be able to work your way to the target 😉

[sadkha]

thank you MuS! i started with the 2nd approach. How can I further define the search if I want to only show the events that have both event codes?
For Example:
index=xxx Code=101 OR Code=201 | stats values("Event Code") values("Computer Name") values("Account Domain") values("Client Address") by "Account Name"

The above search appropriately aggregates the data I want, however it's also returning all of the only Code=201 events, which I want to get rid of. I would only like to display events that have both 101 and 201, sorted by account name.

Thanks so much for your help!