Customer SQL query

rainek1

With the recent issues with Orion, I only have access to the database. I need to create a sql query to report each of my nodes, the total time they were monitored in 2020, and the uptime for the same period. For example:

Node           Total Avail     Total Uptime

Server1       8760              8672

Server2        ......               ........

From there, I can do the rest manually. A simple query that I can export to csv will do the trick, but since I don't know the database structure, I'm having issues. 

A little assistance please?

adatole

Within the database itself, you'll find a table called "ReportDefinitions"

In that table is the title of all of yout reports, along with the column "Definition". In that field is the XML information that defines the report. Scan it for the second that starts <a:CommandText>. It'll look like this:

<a:CommandText>
SELECT
Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.Caption AS NodeName,
Nodes.DetailsUrl AS NDetailsUrl,
Nodes.VendorIcon AS Vendor,
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Interfaces.DetailsUrl AS IDetailsUrl,
Maxbps_In90,
Maxbps_Out90,
Maxbps_In95,
Maxbps_Out95,
Maxbps_In99,
Maxbps_Out99
FROM Orion.Nodes
INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
SELECT
OuterInterfaceTraffic.InterfaceID,
(SELECT MAX(InAverageBps) as maxInAverageBps FROM (
SELECT TOP 90 PERCENT InAveragebps
FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime &gt;= AddDay(-7,

(and so on...)

that's your report. It is (as should be obvious) a SQL query that you can use straight in SQL Management Studio or a similar tool.