detail_query_network_interface fails when no private IP found

[getvictor]

Fleet version:
4.60.1
Web browser and operating system:

---

💥  Actual behavior

Occasional server errors on Dogfood

{"component":"service","err":"detail_query_network_interface expected single result, got 0","host":"jahziels-macbook-pro.local","method":"IngestFunc","platform":"darwin","ts":"2024-12-11T22:38:20.886418487Z"}

🧑‍💻  Steps to reproduce

1. See Dogfood logs (or other customer logs)

🕯️ More info (optional)

N/A

🛠️ To fix

The detailed query returns a private IP address. However, host may be on a network where it was assigned a non-private IP address. It should be OK for this query to return no results.

Detailed query:

SELECT
    ia.address,
    id.mac
FROM
    interface_addresses ia
    JOIN interface_details id ON id.interface = ia.interface
	-- On Unix ia.interface is the name of the interface,
	-- whereas on Windows ia.interface is the IP of the interface.
    JOIN routes r ON r.interface = ia.interface
WHERE
	-- Destination 0.0.0.0/0 or ::/0 (IPv6) is the default route on route tables.
    (r.destination = '0.0.0.0' OR r.destination = '::') AND r.netmask = 0
	-- Type of route is "gateway" for Unix, "remote" for Windows.
    AND r.type = 'gateway'
	-- We are only interested on private IPs (some devices have their Public IP as Primary IP too).
    AND (
		-- Private IPv4 addresses.
		inet_aton(ia.address) IS NOT NULL AND (
			split(ia.address, '.', 0) = '10'
			OR (split(ia.address, '.', 0) = '172' AND (CAST(split(ia.address, '.', 1) AS INTEGER) & 0xf0) = 16)
			OR (split(ia.address, '.', 0) = '192' AND split(ia.address, '.', 1) = '168')
		)
		-- Private IPv6 addresses start with 'fc' or 'fd'.
		OR (inet_aton(ia.address) IS NULL AND regex_match(lower(ia.address), '^f[cd][0-9a-f][0-9a-f]:[0-9a-f:]+', 0) IS NOT NULL)
	)
ORDER BY
    r.metric ASC,
	-- Prefer IPv4 addresses over IPv6 addresses if their route have the same metric.
	inet_aton(ia.address) IS NOT NULL DESC
LIMIT 1;