We are trying to use Grafana to develop a capacity dashboard for all of our monitoring systems. I am relatively new to SWQL and SQL. In trying to pull data from Orion, the tables are set up in such a way as to make continual graphing difficult. For example, percent utilization data seems to only be available in tables named by the day of the month, so we have to specify a table with a specific date. This seems difficult, since you would have to update Grafana daily with another query for the previous day.
Any help would be appreciated.
Solved! Go to Solution.
Outstanding! Just one more thing that I'll mention is that when you're using that view you need to be aware that, depending on how far back you go, you're going to be using data across multiple precisions (detail, hourly, and daily). To make sure calculations are accurate you'll need to consider the weight of the data point. There's a field added to each row that indicates the weight in seconds (i.e., 120, 3600, and 86400).
For example, going back 3 months is going to include all three which would look like this:
SELECT Nodes.Caption AS NodeName ,Interfaces.InterfaceID ,Interfaces.Caption AS InterfaceName ,SUM(Traffic.In_Averagebps * Traffic.Weight) / SUM(Traffic.Weight) AS Avg_In_Traffic ,SUM(Traffic.Out_Averagebps * Traffic.Weight) / SUM(Traffic.Weight) AS Avg_Out_Traffic FROM Nodes AS Nodes JOIN Interfaces AS Interfaces ON Nodes.NodeID = Interfaces.NodeID JOIN InterfaceTraffic AS Traffic ON Interfaces.InterfaceID = Traffic.InterfaceID WHERE Traffic.DateTime > DATEADD(mm, -3, GETUTCDATE()) GROUP BY Nodes.Caption, Interfaces.InterfaceID, Interfaces.Caption
If you're interested in the details, you can check this out:
It's true that the tables are date-stamped but they're consolidated nightly in a view so you can always access them. If you're talking about interface traffic the view is called InterfaceTraffic.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.