topic Re: subsearch in a stats command? in Splunk Search

subsearch in a stats command?

ilhwan — Tue, 17 Feb 2026 17:36:06 GMT

I'm using this command to search dhcp logs and find devices that are new in the last 30 days other than a list of expected hostnames:

index=dhcp dhcp_type IN (DHCPACK DHCPOFFER) NOT (dest_hostname IN (PC* LP* GIS* MU* iPhone* iPad*)) | stats earliest(_time) AS FirstTime by dest_mac | where FirstTime > relative_time(now(), "-30d@d") | convert ctime(FirstTime) | `LOOKUP_OUI(dest_mac)`

I would like to display the latest dest_hostname and its IP address (dest_ip) by searching again just to populate those fields. I don't want to add them to the stats statement because I don't want to differentiate when IP address or hostname change.

Re: subsearch in a stats command?

ITWhisperer — Tue, 17 Feb 2026 18:02:08 GMT

Try something like this

index=dhcp dhcp_type IN (DHCPACK DHCPOFFER) NOT (dest_hostname IN (PC* LP* GIS* MU* iPhone* iPad*)) | stats earliest(_time) AS FirstTime by dest_mac dest_hostname dest_ip | where FirstTime > relative_time(now(), "-30d@d") | stats earliest(_time) AS FirstTime earliest(dest_hostname) as dest_hostname earliest(dest_ip) as dest_ip by dest_mac | convert ctime(FirstTime) | `LOOKUP_OUI(dest_mac)`

Re: subsearch in a stats command?

ilhwan — Tue, 17 Feb 2026 18:21:17 GMT

It's close. Changing earliest(dest_hostname) and earliest(dest_ip) to latest(dest_hostname) and latest(dest_ip) gave me what I wanted in those fields, but now FirstTime is blank.

Re: subsearch in a stats command?

ITWhisperer — Tue, 17 Feb 2026 18:24:54 GMT

Try something like this

| stats latest(FirstTime) AS FirstTime latest(dest_hostname) as dest_hostname latest(dest_ip) as dest_ip by dest_mac

Re: subsearch in a stats command?

ilhwan — Tue, 17 Feb 2026 18:35:10 GMT

Thanks! That did it. I couldn't work out in my head how to make FirstTime part of the second stats output properly. This is the search now with the corrections:

index=dhcp dhcp_type IN (DHCPACK DHCPOFFER) NOT (dest_hostname IN (PC* LP* GIS* MU* iPhone* iPad*)) | stats earliest(_time) AS FirstTime BY dest_mac dest_hostname dest_ip | where FirstTime > relative_time(now(), "-30d@d") | stats latest(FirstTime) AS FirstTime latest(dest_hostname) AS dest_hostname latest(dest_ip) AS dest_ip BY dest_mac | convert ctime(FirstTime) | `LOOKUP_OUI(dest_mac)`