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?

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