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.

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