Correlating dhcp assignment to hostname

[johnblakley]

We have pretty short lease times for dhcp clients. I'm running the getwatchlist addon for Splunk, and what I'm wanting to do is to correlate who had an address during the time of the "bad" query. I have two indexes: dnsserver and dhcp. DNSServer is queried with the lookup table malwaredomains. If it sees a host query for a bad domain that's in the malwaredomains list, it flags it with "isbad=true". This works fine, but what I've noticed is that DNS doesn't log the hostname at the time of the query. In order to get this information, I'm receiving dhcp logs and modified my lookup to do a join operation:

index=dnsserver | lookup malwaredomains domain | search isbad=true | rename srcIP as assignedIP | join host,assignedIP [ search index=dhcp status="assign" OR status="renew"] | fields assignedIP,hostname,status,domain | table hostname, assignedIP, status, domain

My result for the above query would be something like below:

client.domain.name x.x.x.x Renew dl2.v47installer.com

The problem that I'm running into is that there could potentially be more than one host that may have been associated to the x.x.x.x address above in one day. I know our lease times are 4 hours. I think I'm looking for a way to make the above query do the following:

1. Find the bad domain queries
2. Associate the IP address that does the query to an IP address in DHCP
3. Report on the hostname and IP that falls within the range of time.

Let's say that I have 3 hostnames associated to the IP address for that day. If I do the above query, it can show multiple hostnames or a single hostname. The query simply isn't accurate though. For example, the above shows one host with an IP address. If I break that query up, that single host had 3 different IP addresses: one on wireless, one wired, and one on guest wireless. The time the query was done was at 1:25PM, but the update from the IP address above was at 1:54PM with the prior being around 9:54AM and a different address altogether.

In short (I know this wasn't), we'd like to know what hostname could be infected when the query is made for a bad domain. I'm close, but I don't think this is accurate, and I'm not entirely sure I'm going to be able to make it 100% accurate.

Thanks!
John

[woodcock]

Try this:

index=dnsserver | lookup malwaredomains domain | search isbad=true | rename srcIP as assignedIP | map search = "earliest= _time-14400 latest=_time+14400 index=dhcp assignedIP=$assignedIP$ host=$host$ status=\"assign\" OR status=\"renew\"" | table hostname, assignedIP, status, domain

[spj2]

I am having a similar issue. John, did you find a solution to your problem? If so, please share it.

Thanks.
SPJ

[derekarnold]

Is the ip address of interest going to be the first, last, middle, or don't know? I am trying to think if you could exclude the ones that you don't think it will be, but it's hard for me to know if you're detection is going to have the latest time stamp.