0 Replies Latest reply on Oct 10, 2017 1:29 PM by cwbojarski

    Need a UDT all endpoint report for MACs found in the last 24 hours

    cwbojarski

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