Splunk DHCP Lookup Search

gistfile1.txt

# Assumes the presence of SA-Netops (the normalize macro).
# Configured for Stream data as written

index=dhcp chaddr=* ciaddr=* NOT (ciaddr="0.0.0.0" OR ciaddr=169.254.*) 
| streamstats  earliest(_time) as earliest_time latest(_time) as latest_time latest(chaddr) as latest_mac latest(ciaddr) as latest_ip by chaddr ciaddr reset_on_change=true 
| stats  min(earliest_time) as start_time max(latest_time) as end_time by latest_mac latest_ip
| rename latest_mac AS mac latest_ip as ip
| `normalize_mac_address(mac)`
| inputlookup dhcp_lookup append=t
| stats dc(mac) as mac_count values(*) as *  by ip
| where start_time>relative_time(now(),"-45d@d")
| sort - mac_count
| table mac ip start_time end_time
| outputlookup dhcp_lookup