2 Replies Latest reply on Mar 12, 2014 8:40 AM by lwri

    UDT SQL Query: Get user device switch by MAC or host name

    lwri

      We have need to perform SQL query to UDT tables database which could find nearest switch where user device is connected currently.

      Any advice what tables to use for query?

        • Re: UDT SQL Query: Get user device switch by MAC or host name
          madhavan

          Iwri,

           

          Provided below the query which gives the devices with their MAC, IP and Host name connected to a Port with Name and caption of the node and the connection type (Direct - 1 or Indirect - 2)

           

          SWQL Query in case you are using SWIS

           

           

           

           

          Select p2e.endpointid, e.MACAddress, ipc.IPAddress, dns.DNSName, P.Name, N.Caption, ConnectionType

            From Orion.UDT.PortToEndpointCurrent p2e

            left outer join Orion.UDT.Endpoint e on e.EndpointID= p2e.endpointid

            left outer join Orion.UDT.IPAddressCurrent ipc on p2e.EndpointId = ipc.EndpointID

            left outer join Orion.UDT.DNSNameCurrent dns on ipc.ipaddressID = dns.IPAddressID

            left outer join Orion.UDT.port P on P.PortID = p2e.PortID 

            left outer join Orion.Nodes n on p.Nodeid = n.Nodeid

           

          SQL Query - Direct database query

           

          Select e.MACAddress, ipc.IPAddress, dns.DNSName, P.Name, N.Caption, ConnectionType

          From UDT_PortToEndpointCurrent p2e

          left outer join UDT_Endpoint e on e.EndpointID= p2e.endpointid

          left outer join UDT_IPAddressCurrent ipc on p2e.EndpointId = ipc.EndpointID

          left outer join UDT_DNSNameCurrent dns on ipc.ipaddressID = dns.IPAddressID

          left outer join UDT_port P on P.PortID = p2e.PortID

          left outer join Nodes n on p.Nodeid = n.Nodeid