This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Pulling data from Orion into Grafana

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.

Thanks.

Steve

  • 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.

  • Yep, has it. Since you say you are new to SQL a view is functionally pretty similar to a table but it's actually built by combining data from multiple tables with the goal of saving you from having to do all the legwork of joining all those bits together by hand every time you need to do a report. There is a view already built for almost any common reporting use case. If you are using SSMS to work with the data then just expand the section for views instead of tables to see what's available, if you are using the database manager it already shows views and tables together.
  • Ooops, I totally skimmed over "totally new to SWQL and SQL." Thanks for the assist !

  • Thanks to both of you for the solution! I can now look at data going back 6 months (or more).

  • 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:

    https://en.wikipedia.org/wiki/Weighted_arithmetic_mean