cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 10

Pulling data from Orion into Grafana

Jump to solution

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

Labels (1)
Tags (1)
1 Solution
Yep, @mrxinu 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.
- Marc Netterfield, Github

View solution in original post

5 Replies
Yep, @mrxinu 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.
- Marc Netterfield, Github

View solution in original post

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

0 Kudos

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

Ooops, I totally skimmed over "totally new to SWQL and SQL." Thanks for the assist @mesverrum!

0 Kudos
MVP
MVP

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.