Splunk Search

Using transaction or stats to do event correlation like Vlookup?

sadkha
Path Finder

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!

0 Karma

MuS
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

sadkha
Path Finder

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?

0 Karma

MuS
SplunkTrust
SplunkTrust

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 😉

0 Karma

sadkha
Path Finder

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!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...