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

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.

Parents
  • 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
    

Reply
  • 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
    

Children