Need help with a Custom UDT Report

vinay.by

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

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