So I've been tasked with providing a report that shows all UDT endpoints and the ones that have been first discovered in the last 24 hours. In this report i need the discovery time, MAC address, IP Address, Vendor, and DNS name. I don't see any table that has all of this information so i made my own query below. However, it appears to be missing IP addresses. Has anyone had the need to run an all endpoints reports and if so what did you use?
SELECT
UDT_Endpoint.EndpointID,
UDT_Endpoint.FirstSeen,
UDT_Endpoint.MACAddress,
UDT_MacAddressInfo.EndpointID,
UDT_IPAddressCurrent.EndpointID,
UDT_IPAddressCurrent.IPAddress,
UDT_DNSNameCurrent.IPAddress,
UDT_DNSNameCurrent.DNSName
FROM
UDT_Endpoint
FULL OUTER JOIN UDT_IPAddressCurrent ON UDT_Endpoint.EndpointID = UDT_IPAddressCurrent.EndpointID
FULL OUTER JOIN UDT_MacAddressInfo ON UDT_Endpoint.EndpointID = UDT_MacAddressInfo.EndpointID
FULL OUTER JOIN UDT_DNSNameCurrent ON UDT_IPAddressCurrent.IPAddress = UDT_DNSNameCurrent.IPAddress
WHERE
UDT_Endpoint.FirstSeen > dateadd (hh,-24, getdate())
