Need help with a Custom UDT Report

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

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.

  • 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.Name AS [Port]
    		WHEN p.Duplex = 2
    			THEN 'Half Duplex'
    		WHEN p.Duplex = 1
    			THEN 'Full Duplex'
    		WHEN p.Duplex = 0
    			THEN '--'
    		END AS [Duplex]
    		WHEN p.TrunkMode = 2
    			THEN 'Access'
    		WHEN p.TrunkMode = 1
    			THEN 'Trunk'
    		ELSE '--'
    		END AS [TrunkMode]
    	--, p.OperationalStatus
    	--, p.AdministrativeStatus
    		WHEN p.OperationalStatus IN (
    			THEN 'In Use'
    		WHEN p.OperationalStatus = 2
    			THEN 'Available'
    		WHEN p.AdministrativeStatus = 2
    			THEN 'Shut Down'
    		ELSE 'Check Port'
    		END AS [Port State]
    	,p.DetailsUrl AS [_linkfor_Port]
    FROM Orion.UDT.Port AS [p]
    	--where p.Node.CustomProperties.CPNAME = 'ABC'
    	--and p.NodeID=42