Couldn't comment on the accepted answer (apparently I need more points) so I'm commenting here.
Thanks for the assistance, musskopf! With a small amount of tweaking I was able to get exactly what I wanted. Here is the search I ended up with:
sourcetype=dhcpsrvlog description=Assign OR description=Renew [|inputlookup HostWatchList] | dedup dest_host sortby -_time | join type=inner max=0 dest_ip [search sourcetype=pan_threat dst_hostname=badsite.com | rename src_ip AS dest_ip] | dedup dst_hostname sortby -_time | table _time, dest_host, dest_ip, dst_hostname
I added the dedup dst_hostname sortby -_time portion to the search you provided. This simply provides the most recent hit per destination as opposed to the entire list. I may decide it is necessary to see them all later, but for now I just want to see unique domains contacted. join worked like a champ for what I needed. Thanks again!
... View more
I'm relatively new to Splunk, so I'm pretty sure I'm going about this the wrong way but I have to think it's possible. Here goes...
I'm working with two log sources: dhcpsrvlog and web_threat . dhcpsrvlog contains two pieces of data I'm interested in; dest_ip and dest_host . The web_threat logs however do not contain a hostname, just an IP. I want to take the dest_host information for specific IPs and cross reference it with the src_ip field within the web_threat logs. Essentially, I want to leverage the DHCP data to link current IPs for hosts of interest to specific web_threat logs.
I have successfully created the search below which displays the most recent DHCP lease for host(s) of interest using a predefined lookup table. I know it's probably not optimal, but I'm working with what I know:
sourcetype=dhcpsrvlog description=Assign OR description=Renew [|inputlookup hostwatchlist] | dedup dest_host sortby -_time | table time, dest_host, dest_ip, description | rename time AS "Time of Lease" dest_host AS "Hostname" dest_ip AS "Assigned IP" description AS "Type of Lease"
This properly displays the information I need for situational awareness -- when did the host(s) I'm interested last receive/renew an IP lease and what is the IP?
Now, what I'd like to do is take the IP from the Assigned IP column for each host in the resulting table and use that data to perform a secondary search for specific types of web traffic within my web_threat logs.
For example, lets say I'm monitoring a host that was involved in a recent malware investigation, it's hostname is host.network.local which I manually input into my lookup table. I have the most recent DHCP log from my search above and it says the host's IP is 220.127.116.11 . Based on the observed malware let's assume I'm interested in outbound web activity to badsite[d]com from the affected host. In order to find any such traffic from this host I'd need to perform this search:
sourcetype=web_threat src_ip=18.104.22.168 dst_hostname=badsite.com
How can I take the results from my existing dhcpsrvlog search providing me with the Assigned IP and dynamically search the web_threat logs for this traffic as seen in the above search? I'd like the results to display the dest_host field from the DHCP logs if possible, but it's not a requirement. The primary goal is for the search to dynamically change if/when the host of interest is assigned a new IP (i.e. insert the new Assigned IP into the src_ip condition of the search).
I have considered using join in an attempt to relate the two logs using internal searches, but I haven't had any success (admittedly, join is still a bit confusing). I'm confident this can be done but I'm either missing something very simple, or what I am attempting requires tokens and/or data models, neither of which I've had time to play with.
Any assistance or recommendations are greatly appreciated!
... View more