2 Replies Latest reply on Nov 2, 2012 10:57 AM by cvachovecj

    Reporting device moves

    jfanter-stlcc

      I am currently trialing UDT 2.5.1 and am looking to develop a report that shows all the devices (or be filtered by OUI) that have been moved, disconnected, or added in the last 24 hours.  We are looking to track our VoIP phones with this.

       

      Is this possible with the current release and how would I do this?

        • Re: Reporting device moves
          cvachovecj

          Hi,

           

          Here's what our engineering says:

           

          Attached is a sample query how to get all l2/l3 endpoint moves in the last 24 hours, this should give the idea how to make the report.

          However the question is whether to show just first or last move, or all of them (which may generate quite a big report), whether to show l2 or l3 endpoints, or both.


          -- layer2
          select E.MACAddress, P.Name, N.Caption as Node, P2E.FirstSeen, P2e.LastSeen 
          from UDT_Endpoint E
          inner join UDT_PortToEndpoint P2E on P2E.EndpointID = E.EndpointID
          inner join UDT_Port P on P.PortID = P2E.PortID
          inner join Nodes N on N.NodeID = P.NodeID
          where DATEDIFF(hour, isnull(P2E.LastSeen, GETUTCDATE()), GETUTCDATE()) < 24
          order by N.Caption, P.Name, E.MACAddress
          
          -- layer3
          select IP.IPAddress, ISNULL(P.Name, '') as Port, N.Caption as Node, IP.FirstSeen, IP.LastSeen
          from UDT_IPAddress IP
          inner join UDT_Endpoint E on E.EndpointID = IP.EndpointID
          inner join Nodes N on N.NodeID = IP.RouterNodeID
          left outer join UDT_Port P on P.PortID = IP.RouterPortID
          where IP.EndpointID not in (select EndpointID from UDT_PortToEndpoint)
          and DATEDIFF(hour, isnull(IP.LastSeen, GETUTCDATE()), GETUTCDATE()) < 24
          order by N.Caption, P.Name, IP.IPAddress
          


          Regards,

          Jiri