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 |