cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Custom Poller Row Values to Columns Help

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

 

0 Kudos
3 Replies

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

 

 

- Marc Netterfield, Github
0 Kudos

Hi Marc,

Many thanks for taking the time to have a look here.

Seems there is mass duplication one the results for some reason (around 60 odd lines for a single site/ device), I guess each of the joins are duplicating the data?

SW.PNG

I added the CustomPollerID field to see if could get a better idea of what's going on, seems there are two rows with the same ID which is correct, just they seem to be duplicated for some reason.

 
 
 

gdsw_0-1595950046691.png

Essentially there should be two lines per site/device for both VPN connections (VPathName).

I have used the unique IDs in the joins as the *VPathXXXX* didn't seem to work while testing, below is the syntax I am using;

 

SELECT NCP.SWCP_Site As [Site]
, SWN.nodeName As [Node]
, NCP.SWCP_DeviceType As [DeviceType]
--, CPT.CustomPollerID
, 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 CPT
--ON
--NCP.NodeID = CPT.NodeID

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT1
ON
NCP.NodeID = CPT1.NodeID AND CPT1.CustomPollerID = '9ee3d59e-6860-42bf-91df-9b823dbd8208'
--WHERE NCP.SWCP_DeviceType = 'SD-WAN'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT2
ON
NCP.NodeID = CPT2.NodeID AND CPT2.CustomPollerID = 'e4591d5a-23c8-467e-a8b4-58947cb18eb3'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT3
ON
NCP.NodeID = CPT3.NodeID AND CPT3.CustomPollerID = '32b697a1-aee6-40ff-9b19-ab069eab3840'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT4
ON
NCP.NodeID = CPT4.NodeID AND CPT4.CustomPollerID = '4613f520-cd22-49a6-9970-ca3c3e6fefda'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT5
ON
NCP.NodeID = CPT5.NodeID AND CPT5.CustomPollerID = 'a7f62e24-b3af-4215-9d35-ae31aba9ffbe'

INNER JOIN
Orion.NPM.CustomPollerStatusOnNodeTabular CPT6
ON
NCP.NodeID = CPT6.NodeID AND CPT6.CustomPollerID = '91cd86f9-55cb-4cde-9761-0fc3f5edcfed'

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

 

 

Many thanks

-Gary

 

0 Kudos

At a glance it looks like you might need to specify a rowid. Not in front of my computer to confirm right now though.
- Marc Netterfield, Github
0 Kudos