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
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.
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
(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.
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
Then you simply access the lookup with
src_ip to get
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.
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:)
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