SELECT N.caption as [Connected Device], CASE WHEN n.Status=1 THEN '/Orion/images/StatusIcons/Small-Up.gif' WHEN n.Status=9 THEN '/Orion/images/StatusIcons/Small-Unmanaged.gif' WHEN n.Status=0 THEN '/Orion/images/StatusIcons/Small-Unknown.gif' WHEN n.Status=12 THEN '/Orion/images/StatusIcons/Small-Unreachable.gif' WHEN n.Status=3 THEN '/Orion/images/StatusIcons/Small-Up-Warn.gif' WHEN n.Status=14 THEN '/Orion/images/StatusIcons/Small-Critical.gif' WHEN n.Status=2 THEN '/Orion/images/StatusIcons/Small-Down.gif' ELSE '/Orion/images/StatusIcons/Small-Unreachable.gif' END AS [_IconFor_Connected Device], '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a'+ToString(N.NodeID) AS [_LinkFor_Connected Device], ToString(p.name + ' - ' + i.alias) as [Connected Port], CASE WHEN I.Status=1 THEN '/Orion/images/StatusIcons/Small-Up.gif' WHEN I.Status=9 THEN '/Orion/images/StatusIcons/Small-Unmanaged.gif' WHEN I.Status=0 THEN '/Orion/images/StatusIcons/Small-Unknown.gif' WHEN I.Status=12 THEN '/Orion/images/StatusIcons/Small-Unreachable.gif' WHEN I.Status=3 THEN '/Orion/images/StatusIcons/Small-Up-Warn.gif' WHEN I.Status=14 THEN '/Orion/images/StatusIcons/Small-Critical.gif' WHEN I.Status=2 THEN '/Orion/images/StatusIcons/Small-Down.gif' WHEN I.Status=10 THEN '/Orion/images/StatusIcons/Small-Unplugged.gif' ELSE '/Orion/images/StatusIcons/Small-Unreachable.gif' END AS [_IconFor_Connected Port], '/Orion/Interfaces/InterfaceDetails.aspx?NetObject=I%3a'+ToString(I.interfaceid)+'&view=InterfaceDetails' as [_LinkFor_Connected Port], case when dns.dnsname is null then 'N/A' else dns.dnsname end as [Endpoint], ipc.ipaddress as [IP Address], IPAM.IPstatustext as [IP Status], IPAM.IPstatusicon as [_IconFor_IP Status], e.macaddress as [MAC Address], macp.organization as [Vendor] from orion.udt.endpoint E inner JOIN (select distinct endpointid, ipaddress from orion.udt.ipaddress) IPC on ipc.endpointid = e.endpointid inner join (select distinct endpointid,routernodeid, routerportid from orion.udt.ipaddress) ip on ip.endpointid = e.endpointid inner join Orion.udt.port P on p.portid = ip.routerportid inner join orion.npm.interfaces i on i.nodeid=ip.routernodeid and i.index=p.portindex inner join orion.nodes n on n.nodeid=ip.routernodeid left join orion.udt.dnsnamecurrent dns on dns.ipaddress = ipc.ipaddress left join IPAM.IPInfo IPAM on IPAM.ipaddress = ipc.IPaddress left join Orion.macprefixes macp on substring(e.macaddress,1,6) = macp.macprefix where ipc.ipaddress not in (select ipaddress from Orion.nodeipaddresses) and ipc.ipaddress not in (select ipaddress from orion.nodes) and i.typename <>'propvirtual' UNION ( SELECT N.caption as [Connected Device], CASE WHEN n.Status=1 THEN '/Orion/images/StatusIcons/Small-Up.gif' WHEN n.Status=9 THEN '/Orion/images/StatusIcons/Small-Unmanaged.gif' WHEN n.Status=0 THEN '/Orion/images/StatusIcons/Small-Unknown.gif' WHEN n.Status=12 THEN '/Orion/images/StatusIcons/Small-Unreachable.gif' WHEN n.Status=3 THEN '/Orion/images/StatusIcons/Small-Up-Warn.gif' WHEN n.Status=14 THEN '/Orion/images/StatusIcons/Small-Critical.gif' WHEN n.Status=2 THEN '/Orion/images/StatusIcons/Small-Down.gif' ELSE '/Orion/images/StatusIcons/Small-Unreachable.gif' END AS [_IconFor_Connected Device], '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a'+ToString(N.NodeID) AS [_LinkFor_Connected Device], ToString(p.name + ' - ' + i.alias) as [Connected Port], CASE WHEN I.Status=1 THEN '/Orion/images/StatusIcons/Small-Up.gif' WHEN I.Status=9 THEN '/Orion/images/StatusIcons/Small-Unmanaged.gif' WHEN I.Status=0 THEN '/Orion/images/StatusIcons/Small-Unknown.gif' WHEN I.Status=12 THEN '/Orion/images/StatusIcons/Small-Unreachable.gif' WHEN I.Status=3 THEN '/Orion/images/StatusIcons/Small-Up-Warn.gif' WHEN I.Status=14 THEN '/Orion/images/StatusIcons/Small-Critical.gif' WHEN I.Status=2 THEN '/Orion/images/StatusIcons/Small-Down.gif' WHEN I.Status=10 THEN '/Orion/images/StatusIcons/Small-Unplugged.gif' ELSE '/Orion/images/StatusIcons/Small-Unreachable.gif' END AS [_IconFor_Connected Port], '/Orion/Interfaces/InterfaceDetails.aspx?NetObject=I%3a'+ToString(I.interfaceid)+'&view=InterfaceDetails' as [_LinkFor_Connected Port], case when dns.dnsname is null then 'N/A' else dns.dnsname end as [Endpoint], ip.ipaddress as [IP Address], IPAM.IPstatustext as [IP Status], IPAM.IPstatusicon as [_IconFor_IP Status], e.macaddress as [MAC Address], macp.organization as [Vendor] from Orion.UDT.PortToEndpointCurrent PEC inner join orion.udt.ipaddress ip on ip.endpointid = pec.endpointid inner join orion.udt.endpoint e on e.endpointid = pec.endpointid left join orion.udt.dnsnamecurrent dns on dns.ipaddress = ip.ipaddress inner join orion.udt.port p on p.portid = pec.portid inner join orion.npm.interfaces i on i.nodeid=p.nodeid and i.index=p.portindex inner join orion.nodes n on n.nodeid = p.nodeid left join IPAM.IPInfo IPAM on IPAM.ipaddress = ip.IPaddress left join Orion.macprefixes macp on substring(e.macaddress,1,6) = macp.macprefix where ip.ipaddress not in (select ipaddress from Orion.nodeipaddresses) and ip.ipaddress not in (select ipaddress from orion.nodes) and i.typename <>'propvirtual' and pec.connectiontype =1 )