Need help with a Custom UDT Report

Question

Hi All, 
 I did like to extract the below fields onto a report, can someone please help me with SWQL query or SQL query ?

The below SWQL query does provide 
 SELECT U.Duplex
	,N.Caption
	,U.MACAddress
	,U.Name
	,U.PortDescription
	,U.PortID
	,U.PortType
	,U.Speed
	,U.StatusDescription
FROM Orion.UDT.Port U
INNER JOIN Orion.Nodes N ON U.NodeID = N.NodeID
WHERE N.Caption LIKE '%ABC%' 
 But this does not provide all fields though - U.Duplex provides a number 0/1, I need to print this as Full Duplex on the report rather than printing it as 1 and I also need VLAN number on this report.

cahunt · Accepted Answer

This has a few options for you on the display. An added detail in current status or use state, an option for use of Node custom Properties in the where clause or a NodeID. If you do not like the Available/In Use/Shut Down for the current state, remove the Case statement and uncomment the Admin and Oper Statuses. Cheers! 
 
 SELECT p.Node.Caption AS [Node]
	,p.NodeID
	,p.Name AS [Port]
	,p.PortDescription
	,p.PortVLANs.VlanName
	,p.PortVLANs.DisplayName
	,p.Speed
	,CASE 
 WHEN p.Duplex = 2
 THEN 'Half Duplex'
 WHEN p.Duplex = 1
 THEN 'Full Duplex'
 WHEN p.Duplex = 0
 THEN '--'
 END AS [Duplex]
	,CASE 
 WHEN p.TrunkMode = 2
 THEN 'Access'
 WHEN p.TrunkMode = 1
 THEN 'Trunk'
 ELSE '--'
 END AS [TrunkMode]
	--, p.OperationalStatus
	--, p.AdministrativeStatus
	,CASE 
 WHEN p.OperationalStatus IN (
 1
 ,3
 ,14
 )
 THEN 'In Use'
 WHEN p.OperationalStatus = 2
 THEN 'Available'
 WHEN p.AdministrativeStatus = 2
 THEN 'Shut Down'
 ELSE 'Check Port'
 END AS [Port State]
	,p.MACAddress
	,p.StatusDescription
	,p.Status
	,p.DetailsUrl AS [_linkfor_Port]
FROM Orion.UDT.Port AS [p]
	--where p.Node.CustomProperties.CPNAME = 'ABC'
	--and p.NodeID=42

Need help with a Custom UDT Report

Top Replies