Splunk Search

How to edit my search for time-based correlation between two different sourcetypes (IP Attribution)?

tjr1775
Path Finder

The following search utilizes windows event security logs and produces a five column table that has the fields noted below:

Sourcetype=WinEventLog:Security  EventCode=4624 Logon_Type=3 | dedup user src_ip Workstation_Name sortby _time | table _time user ComputerName Workstation_Name src_ip | sort -_time

Because windows generates a LOT of duplicate-esque events, the dedup command here is taking only the MOST RECENT event that has occurred. You should be able to see that with the "sortby _time". This search works perfectly, no issues, and you can see it generates an "IP address history table." This is a DHCP environment.

So, let's say there's an event from another sourcetype that has the following fields, where A, B, and C are some attribute of the sourcetype like URL,URI_Query, etc:

_time src_ip fieldA fieldB fieldC

or,

sourcetype=securitytool | table _time src_ip fieldA fieldB fieldC

Now, what I want to do is take the src_ip from this second search and find out who the user is from the first search.

Manually hardcoding the IP for testing purposes, it gives me the correct answer like this:

Sourcetype=WinEventLog:Security  EventCode=4624 Logon_Type=3 src_ip=xxx | dedup user src_ip Workstation_Name sortby _time | table _time user ComputerName Workstation_Name src_ip | sort -_time | head 1

BUT- I am having trouble using join/append/subsearch or some other machination to make this work automagically. I tried some forms of subsearching to no avail... any of the subsearch shortcuts don't work because they are two different sourcetypes. I want the output to look like this:

 _time user ComputerName Workstation_Name src_ip fieldA fieldB fieldC

Thoughts? The logic here is 1) you see an event with an IP, 2) you want to go look and see who "had" that IP last to attribute the event to an actual user.

0 Karma

thilleso
Path Finder

I see two potential solutions:

1) You could try appending a search using the join command and returning only the fields you're interested in.

sourcetype=WinEventLog:Security EventCode=4624 Logon_Type=3 
| dedup user src_ip Workstation_Name sortby _time 
| join type=left src_ip [search sourcetype=securitytool | fields src_ip, fieldA, fieldB, fieldC] 
| table _time user ComputerName Workstation_Name src_ip fieldA fieldB fieldC
| sort -_time | head 1

2) Making a larger basesearch and grouping the results using the transaction command

(sourcetype=WinEventLog:Security EventCode=4624 Logon_Type=3 src_ip=*) OR (sourcetype=securitytool src_ip=*)
| dedup user src_ip Workstation_Name sortby _time 
| transaction src_ip
| table _time user ComputerName Workstation_Name src_ip fieldA fieldB fieldC
| sort -_time | head 1

Don't know how the dedup command will affect your basesearch though.

Personally I feel the second option is better, having a range of customizing options with the transaction command. Also having a larger basesearch will have a less computational impact than using a join/append alternative.

0 Karma

woodcock
Esteemed Legend

There is no way to do this all in a single search because over the span of your search (unless it is very small), the IP may have been handed off to multiple people. The best way to do it is to use the second search to generate a time-based lookup using outputlookup:

https://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Configureatime-boundedlookup

Then you simply access the lookup with _time and src_ip to get user or ComputerName or whatever you put into the lookup. You can schedule the search to run hourly or nightly and to trim itself so it doesn't grow too large.

0 Karma

tjr1775
Path Finder

I disagree with your assertion that this cannot be done in a single search- I've accounted for the handing off of the IP to multiple people by only looking for the last person to have it (i.e., "head 1") ; however, I do very much like your alternate solution and I will try that out this week and see how it goes. The main problem with your suggestion is how do I run a very historical search such as a month ago if I'm trimming the lookup daily? This is why I've avoided lookups so far, but there could be some potential here for creating a summary index each day of the matches. We'll see:)

0 Karma

woodcock
Esteemed Legend

You have to maintain the lookup to match your search timeframes; there's no way around that, as you noted.

0 Karma

koshyk
Super Champion

could you do

sourcetype=WinEventLog:Security  EventCode=4624 Logon_Type=3 src_ip=xxx| stats count by _time,user src_ip Workstation_Name | join src_ip [ search <your Second search>] 
0 Karma

tjr1775
Path Finder

I'll try this as well

0 Karma

sundareshr
Legend

Try like this

sourcetype=WinEventLog:Security OR sourcetype=securitytool | stats values(user) as user values((ComputerName) as values(Workstation_Name) as Wkstn values(fieldA) as A values(fieldB) as B  values(fieldC) as C by _time src_ip 
0 Karma

tjr1775
Path Finder

I'll try it this week and see how it goes

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...