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.

Pulling Data out of the Orion.NPM.CustomPollerStatistics table

I have a need to pull data out of Orion.NPM.CustomPollerStatistics using SWQL, but can't seem to get it formatted the way I want it.

Every ten mins a custom poller runs and populates a number of rows each time. The rows I'm interested in are where the Status contains 'State%' or the Status contains '---- peer%'.

This query pulls out the data I need but the format isn't right.

SELECT TOP 1000 CustomPollerAssignmentID, DateTime, Status, RowID
FROM Orion.NPM.CustomPollerStatistics
where CustomPollerAssignmentID = 'df1c8f83-7d67-4da2-b033-88ec7909d03a'
and (Status like 'State%' or Status like '----- peer%')
 

CustomPollerAssigmentID DateTime Status RowID
RedatcedAssigmentID 2022-07-13 15:49:10.157 ----- Peer 1 ----- 2
RedatcedAssigmentID 2022-07-13 15:49:10.157 ----- Peer 2 ----- 21
RedatcedAssigmentID 2022-07-13 15:49:10.157 State                         Active 22
RedatcedAssigmentID 2022-07-13 15:49:10.157 State                         Active 3
RedatcedAssigmentID 2022-07-13 15:59:24.997 ----- Peer 1 ----- 2
RedatcedAssigmentID 2022-07-13 15:59:24.997 ----- Peer 2 ----- 21
RedatcedAssigmentID 2022-07-13 15:59:24.997 State                         Active 22
RedatcedAssigmentID 2022-07-13 15:59:24.997 State                         Active 3

There are two peers each with their own State values. One peer name is on row 22, and has it's state on row 21, the one peer name is on row 3 and has it's state on row 2. One different devices the actual rows might differ but the peer state is always on a row that's one less than the row that holds the peer name.

The question is how do I query the table to get the data back in this form

CustomPollerAssigmentID DateTime PeerName RowID
RedatcedAssigmentID 13/07/2022 15:49 ----- Peer 1 ----- State                         Active
RedatcedAssigmentID 13/07/2022 15:49 ----- Peer 2 ----- State                         Active
RedatcedAssigmentID 13/07/2022 15:59 ----- Peer 1 ----- State                         Active
RedatcedAssigmentID 13/07/2022 15:59 ----- Peer 2 ----- State                         Active
Parents Reply Children
No Data