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

)