I am working out a SWQL query for displaying custom poller information and getting it to line up on the associated interface. We have successfully gotten it to line up in the query below and display in a custom query widget, but now I am attempting to get it to display at the interface level by including an interface index variable. Below is the query worked out so far, but I can't seem to find a variable for the interface index:
SELECT TOP 2000 Caption,
n.Interfaces.Name,
Values.IntfQueue,
Values.MaxTxedBytes,
Values.MinTxedBytes,
Values.AvgTxedBytes
FROM Orion.Nodes n
JOIN (SELECT Top 2500
CPS.CustomPollerAssignment.NodeID,
CPS.RowID,
SUBSTRING(CPS.RowID,1,charindex('.',CPS.RowID)-1) AS IntfIndex,
CASE
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='0' THEN 'best-effort'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='1' THEN 'expedited-forwarding'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='2' THEN 'assured-forwarding'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='3' THEN 'network-control'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='4' THEN 'assured-forwarding-2'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='5' THEN 'assured-forwarding-3'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='6' THEN 'assured-forwarding-4'
WHEN SUBSTRING(CPS.RowID,charindex('.',CPS.RowID)+1,charindex('.',CPS.RowID)+1)='7' THEN 'business-bulk'
END AS IntfQueue,
MAX(abs(CPS.Status)) as MaxTxedBytes,
MIN(abs(CPS.Status)) as MinTxedBytes,
ROUND(AVG(abs(CPS.Status)),2) as AvgTxedBytes
FROM Orion.NPM.CustomPollerStatistics as CPS
WHERE CPS.CustomPollerAssignment.NodeID=${NodeID} AND CPS.CustomPollerAssignment.CustomPollerName LIKE 'jnxCosQstatTxedByteRate'
AND DATETIME>=ADDMONTH(-1,GETUTCDATE())
GROUP BY CPS.CustomPollerAssignment.NodeID,
CPS.RowID, SUBSTRING(CPS.RowID,1,charindex('.',CPS.RowID)-1))as Values ON n.NodeID=Values.NodeID AND n.Interfaces.Index=Values.IntfIndex
WHERE NodeID=${NodeID} AND n.Interfaces.Index=${Index}
ORDER BY n.Interfaces.Index, Values.IntfQueue
