Custom Poller Row Values to Columns Help

gdsw

Hey all,

I've had to create Universal Device Poller to dig further into our SD-WAN appliance's VPN overlay statistics. I've bene able to obtain the data I want via SWQL Studio, however the data in its current form (row data) I’d like to move to columns to neaten up the view (and prevent endless scrolling across 100+ sites).

 Below is an example of the data I am getting at the moment, where the “CustomPollerID” column has the info I need (which is readable in the “AssignmentName” column.

Site	Node	DeviceType	AssignmentName	Status	CustomPollerID
Balsall	BSHC	SD-WAN	VPathName	BSCH-RCN01	d8208
Balsall	BSHC	SD-WAN	VPathState	Good	18eb3
Balsall	BSHC	SD-WAN	VPathBytesSent	21900544	b3840
Balsall	BSHC	SD-WAN	VPathBytesRecieved	2195456	fefda
Balsall	BSHC	SD-WAN	VPathSentJitter	10	9ffbe
Balsall	BSHC	SD-WAN	VPathRecievedJitter	4	dcfed

From the above, I’d like to move the common VPathxxxx headings to columns and capture the relevant data for each item if possible?

Site	Node	DeviceType	VPathName	VPathState	VPathBytesSent	VPathBytesRecieved	VPathSentJitter	VPathRecievedJitter
Balsall	BSHC	SD-WAN	BSCH-VPN01	Good	21900544	2195456	10	4
Balsall	BSHC	SD-WAN	BSCH-VPN02	Good	21908744	2195676	5	5
Bravo	BRVO	SD-WAN	BSCH-VPN01	Good	5192	4820	6	1
Bravo	BRVO	SD-WAN	BSCH-VPN02	Bad	0	0	0	0

I am relying on CustomProperties (SWCP) for Site and DeviceType Columns along with the CustomPollerStatusOnNodeTabular tables which include the VPN Overlay stats;

SELECT NCP.SWCP_Site As [Site], SWN.nodeName As [Node], NCP.SWCP_DeviceType As [DeviceType], CPT.DisplayName, CPT.AssignmentName, CPT.Status, CPT.CustomPollerID
FROM orion.Nodes SWN

INNER JOIN
Orion.NodesCustomProperties NCP
ON
SWN.NodeID = NCP.NodeID
--WHERE NCP.SWCP_DeviceType = 'SD-WAN'
--ORDER BY NCP.SWCP_Site

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT
ON
NCP.NodeID = CPT.NodeID
--WHERE NCP.SWCP_DeviceType = 'SD-WAN'
WHERE NCP.SWCP_Site <> 'Azure'
ORDER BY NCP.SWCP_Site

mesverrum

Since SWQL doesn't have a pivot function I've done what you are asking in the past with a stack of additional joins

SELECT NCP.SWCP_Site As [Site]
, SWN.nodeName As [Node]
, NCP.SWCP_DeviceType As [DeviceType]
, CPT1.Status as VPathName
, CPT2.Status as VPathState
, CPT3.Status as VPathBytesSent
, CPT4.Status as VPathBytesRecieved
, CPT5.Status as VPathSentJitter
, CPT6.Status as VPathRecievedJitter

FROM orion.Nodes SWN

INNER JOIN
Orion.NodesCustomProperties NCP
ON
SWN.NodeID = NCP.NodeID
--WHERE NCP.SWCP_DeviceType = 'SD-WAN'
--ORDER BY NCP.SWCP_Site

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT1
ON
NCP.NodeID = CPT1.NodeID AND CPT1.AssignmentName = 'VPathName'
--WHERE NCP.SWCP_DeviceType = 'SD-WAN'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT2
ON
NCP.NodeID = CPT2.NodeID AND CPT2.AssignmentName = 'VPathState'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT3
ON
NCP.NodeID = CPT3.NodeID AND CPT3.AssignmentName = 'VPathBytesSent'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT4
ON
NCP.NodeID = CPT4.NodeID AND CPT4.AssignmentName = 'VPathBytesRecieved'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT5
ON
NCP.NodeID = CPT5.NodeID AND CPT5.AssignmentName = 'VPathSentJitter'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT6
ON
NCP.NodeID = CPT6.NodeID AND CPT6.AssignmentName = 'VPathRecievedJitter'

WHERE NCP.SWCP_Site <> 'Azure'
ORDER BY NCP.SWCP_Site