This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

UDT Port resource/report (swql)

I needed a UDT searchable resource similar to the port details

pastedImage_0.png

There is a sql view which has all of the information, but the view is not in SWQL.  A simple matter of looking at the design of the view and converting it to swql did the job.

pastedImage_1.png

select DISTINCT

  1. n.Caption as [Device]

,n.DetailsUrl as [_LINKFOR_Device]

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]

,p.name as [Port]

,p.StatusLED as [_IconFor_Port]

,p.DetailsUrl as [_LinkFor_Port]

--, p.Description as [Port Name]

,dns.DNSName as [Host Name],ip.IPAddress

,e.MACAddress as [MAC]

,'/NetPerfMon/images/Vendors/' + ISNULL(v.Icon, 'Unknown.gif') AS [_IconFor_MAC]

,case p2e.VlanID when 0 then null else p2e.VlanID end as [VLAN]

--,n.NodeID, p.PortID, p2e.ConnectionType, p2e.FirstSeen

,CASE [p2e].ConnectionType WHEN 1 THEN 'D' WHEN 2 THEN 'I' ELSE 'U' END AS ConnType

--,ISNULL(m.Vendor, m.Organization) AS MACVendor

from Orion.Nodes n

inner join Orion.UDT.Port as p on p.NodeID=n.NodeID and p.Flag <>1

inner join Orion.UDT.PortToEndpointCurrent as p2e on p2e.PortID = p.PortID

inner join Orion.UDT.Endpoint as e on e.EndpointID = p2e.EndpointID

left OUTER JOIN Orion.UDT.IPAddressCurrent as ip on ip.EndpointID = e.EndpointID

left OUTER join Orion.UDT.DNSNameCurrent as dns on dns.IPAddress = ip.IPAddress

left outer join Orion.MacPrefixes as m on m.MacPrefix = SUBSTRING(e.MACAddress,1,6)

left outer join Orion.Vendors as v on v.name = m.Vendor

where n.Caption like '%${SEARCH_STRING}%'  or dns.DNSName like '%${SEARCH_STRING}%'  or ip.IPAddress like '%${SEARCH_STRING}%'  or e.MACAddress like '%${SEARCH_STRING}%'

order by [Device]