Graphing multi-row Universal Poller rate data
Hi All,
I'm having difficulty graphing RADMUX Pseudo Wire data from Universal Pollers in a Custom Chart (and all the other methods I've tried too). The data is collected from a couple of SNMP counters - OIDs are: 1.3.6.1.4.1.164.3.1.6.7.7.1.15 and 1.3.6.1.4.1.164.3.1.6.7.7.1.16 and are collected as Frames/Second as a GET TABLE with Keep Historical Data set to yes. This returns up to 6 Rows and have been collecting for several days.
If I use the following SELECT statements in Studio I get back the data I want:
SELECT toLocal(e.DateTime) as TimeStamp, e.RowID AS RowID,
CONCAT( e.Status,' - ', (Select top 1 d.Status FROM Orion.NPM.CustomPollerStatusOnNodeTabular d where (d.RowLabel LIKE '%RADMux PW Name%' AND d.RowID=e.RowId AND d.NodeID=e.NodeID ))) As Name,
(SELECT t.Rate FROM Orion.NPM.CustomPollerStatusOnNodeTabular t Where (t.RowLabel LIKE 'RADMUX PW Tx Frames%' AND t.RowID=e.RowID AND t.NodeID=e.NodeID)) AS Tx,
(SELECT r.Rate FROM Orion.NPM.CustomPollerStatusOnNodeTabular r Where (r.RowLabel LIKE 'RADMUX PW Rx Frames%' AND r.RowID=e.RowID AND r.NodeID=e.NodeID)) AS Rx
FROM Orion.NPM.CustomPollerStatusOnNodeTabular e
WHERE e.DisplayName LIKE 'RADMux PW ID%' AND e.NodeID=${NodeID} <- I've used entity with the NodeID of 6505 to generate the following table.
So returning:
| | TimeStamp | RowID | Name | Tx | Rx |
| | 2021-03-01 14:12:47.69 | 99900001 | 1 - PW_AAE_to_SKT | 500.0078 | 500.0078 |
| | 2021-03-01 14:12:47.69 | 99900002 | 2 - PW_AAE_to_LAMB | 500.0078 | 500.0078 |
| | 2021-03-01 14:12:47.69 | 99900003 | 3 - PW_AAE_to_VICT | 500.0078 | 500.0078 |
| | 2021-03-01 14:12:47.69 | 99900004 | 4 - PW_AAE_to_HRD | 501.0078 | 500.1000 |
| ... | ... | ... | ... | ... |
Note: under normal conditions the Tx and Rx should be the same (or very nearly at 500 frames/second). It's when they're not I need to see the trends in a graph. I though, easy, I'll use a Custom Chart and life would be sweet. However, when I go to "Add Data Series" I get a "Nothing available to Select" message with the above SWQL. So I thought the data may need to be transformed in some way. If I Round() the data returned in the Rx and Tx columns they appear in the "Add Data Series" panel but adding Tx and/or Rx with TimeStamp for the "Time Column" doesn't product a graph, just a "Data Not Available" message. So the Select statements I used look like:
Round(1.0*(SELECT t.Rate FROM Orion.NPM.CustomPollerStatusOnNodeTabular t Where (t.RowLabel LIKE 'RADMUX PW Tx Frames%' AND t.RowID=e.RowID AND t.NodeID=e.NodeID)),3) AS Tx,
Round(1.0*(SELECT r.Rate FROM Orion.NPM.CustomPollerStatusOnNodeTabular r Where (r.RowLabel LIKE 'RADMUX PW Rx Frames%' AND r.RowID=e.RowID AND r.NodeID=e.NodeID)),3) AS Rx
| Data grouping |
|---|
| Group chart data by: | RowId | BROWSE... | | Legend shows: | Name | BROWSE... |
|
Several variants tried here to no avail...
Any help that saves my sanity will be gratefully accepted, especially if it generates a rate graph for the Tx and Rx values with a legend of Name
Cheers, Peter
A quick update... I came in this morning and there was a single set of points plotted at the latest DateTime. So the next question is how do I get all the data between the time ranges I have specified in the chart "Last 24 Hrs" at 5min increments? I assume I will need to rewrite my SWQL but nothing I try helps. So If someone can point me to a solution or at least what sacrifices I need to make.... I would be most grateful.
