Good morning all,
One of our customers recently asked me to create a view so that she can track interfaces currently dropping traffic. I wasn't able to find anything OOTB for this, so I did write a SQL script and dropped it into a custom table. Hopefully this might help someone in the future or maybe someone can suggest improvements as well. Thanks!
SELECT N.SysName as Node, I.InterfaceName as Interface, S.Value as Drops, S.StartDate, S.EndDate
FROM cbQosStats S
INNER JOIN CBQoSPolicy as P
ON (S.PolicyID = P.PolicyID)
INNER JOIN NodesData as N
ON (P.NodeID = N.NodeID)
INNER JOIN Interfaces as I
ON (N.NodeID = I.NodeID)
WHERE S.Value > 0 AND S.StatsID = 3 AND S.EndDate >= DATEADD(HOUR, -1, GETDATE())
I used SQL vs SWQL because I couldn't seem to find a corresponding entry for cbQoSStats in the SWQL schema. I started with this table, as this one contains the value and dates, but only contains a policy ID. This is where the cbQoSPolicy table comes in. From there, I am able to map the policy ID to a node ID.
From cbQoSPolicy, I can pull NodeID and InterfaceID. By joining the NodesData and Interfaces tables, I can map the NodeID and InterfaceID to names.
In the where clause, I only want to see values greater than 0 (number of drops), StatsID of 3 (drop action), and EndDate greater/equal to an hour ago. This part can be tuned by changing the DATEADD formula to your taste. I am currently still waiting on feedback from the customer to see what sort of time frame she wants to see this data for.
In my particular case, this query returns 800+ rows. All of these rows will not display on a custom table by default (100 row max). I changed the "ReportingTableViewMaxRowCount" field in SolarwindsOrion.WebSettings from 100 to 1000.
