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

[tjr1775]

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.

[thilleso]

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.

[woodcock]

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.

[tjr1775]

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:)

[woodcock]

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

[koshyk]

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

[tjr1775]

I'll try this as well

[sundareshr]

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 

[tjr1775]

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