subsearch in a stats command?

[ilhwan]

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.

[ITWhisperer]

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)`

[ilhwan]

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.

[ITWhisperer · Accepted solution]

Try something like this

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

[ilhwan]

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)`