Windows DHCP latest renewal date filter

[herzog]

I have implemented the Windows DHCP app and it is working GREAT!

Here is what I am trying to accomplish with it. We run a static DHCP scheme where all of our workstations are manually entered when they are purchased and always get the same IP address.

Unfortunately our techs are not the most diligent at informing me when workstations are retired so our DHCP scheme becomes stale with unused reservations over time.

So I am trying to build a search that will show me any IP address that has not been renewed in "x" number of days.

I am by no means a Splunk expert so I am hoping somebody can help me. So in my mind the search would be "for every IP find all renewal dates and if the most recent date is greater than 90 days from current date show me the IP address". Now how do I format that for Splunk?

THANKS!

[herzog]

Thank you both for answering my question and providing the queries! I ran into two issues (in case anybody else is going to use these)

1) On the first query it did not like "> (86400*90)". Splunk said "Error in 'search' command: Unable to parse the search: Comparator '>' has an invalid term on the right hand side."
So I had to pull out a piece of paper and remind myself how to do complex multiplication (*mutters to self* 9 times 6, carry the 5...) and changed the query to read "> 432000"

After that it ran great!

2) When I ran the second query I get this error "Search operation 'sourcetype' is unknown. You might not have permission to run this operation." This is not a big deal because I got the first query to work and it gives me what I am looking for but the curiosity factor takes over and is driving me crazy why the second query does not work on my box?

[gpullis]

Another approach might be to make a list of all the IP addresses that have registered in the last 90 days, and exclude that list from a search over a longer time period. For example:

`WinDHCP_event` eventtype=WinDHCP_lease_renewed NOT ([search `WinDHCP_event` eventtype=WinDHCP_lease_renewed startdaysago=90 | dedup src_ip | fields + src_ip]) startdaysago=365 enddaysago=90 | dedup src_ip | sort ip(src_ip) | table src_ip

I'm using a subsearch to find the renewals in the last 90 days, and excluding the results of the subsearch from my main search that starts at a year ago and ends at 90 days ago. The last three commands (dedup | sort | table) are just formatting the output.

[gpullis]

Sorry, it was a typo on my part. The subsearch (the part in the square brackets) needs to start with the search command. I fixed the query above. I'm pretty sure Araitz's approach is more efficient. I was just trying to illustrate a different approach.

Unfortunately, I've been indexing Windows DHCP events since before Araitz wrote his app, so my field names are guesses based on Araitz's comment. Hopefully I got them right. 🙂

[araitz]

Great to hear that the app is working for you! I have a new rev of the app that is sitting on the back-burner, so maybe I can integrate this search in to the update.

To solve your immediate problem, how about this:

 `WinDHCP_event` eventtype=WinDHCP_lease_renewed 
 | stats first(_time) as most_recent_renewal by src_ip 
 | eval now=now()
 | eval time_since_last_renewal = now - most_recent_renewal
 | search time_since_last_renewal > (86400*90)

First, we narrow the base search down to Windows DHCP lease renewal events. Then, we use stats first() to calculate the most recent time that a renewal occurred by src_ip.

Using eval twice, we store the current epoch time as "now" and the difference between "now" and the most recent renewal time as "time_since_last_renewal".

Finally, we use the search operator to find differences that are greater than 90 days (86400 seconds in a day times 90 days).

[dwaddle]

Araitz's technique of keeping long-term state in a lookup table might also be of value here. You would structure the lookup to have the a single entry per address with the last time it renewed. A scheduled search maintains the state in the lookup, refreshing systems with more recent renewals on an incremental basis. http://blogs.splunk.com/2011/01/11/maintaining-state-of-the-union/