Executing SQL Queries on the Orion Database Manager

You do have an 'export to csv' on your Database Manager

Hi Vijay,

Yes, there exist an "export to csv" on the Orion Database Manager.

I have never used it actually. That means I should be able to view the .csv files via Excel right?

Anyway, recently I just observed that my SQL results do not display again, either via the Orion Report Writer or via Orion Database Manager.

What I get is just the column names defined in my SQL code.

Any ideas. I use an NPM 10.6.

Regards,

Please check the variables that you are using on Report Writer and please check your SQL query on Database Manager.

From the above post looks like the query or variables that are using are incorrect or that is not what you intend to do.

Yes you are correct .csv and then on you can view it as an Excel sheet. If possible please post the query that you are using thanks.

Hi Vijay,

Please see my codes below.

========================================================================================

IF (DatePart (weekday, GETDATE()) <> 7) AND (DatePart(weekday, GETDATE()) <> 1)

SELECT Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Nodes.Caption AS

NodeName, Nodes.Location AS Location, Interfaces.Caption AS InterfaceName, AVG

(InterfaceTraffic_Detail.In_Averagebps) AS Average_Receive_bps, MAX(InterfaceTraffic_Detail.In_Maxbps) AS MAX_Receive_bps,
AVG(Case InBandwidth
            When 0 Then 0
            Else (In_Averagebps/InBandwidth) * 100
            End) AS AVERAGE_of_Recv_Percent_Utilization,
AVG(InterfaceAvailability_Detail.Availability) AS AVERAGE_of_Availability

FROM dbo.InterfaceAvailability_Detail INNER JOIN dbo.InterfaceTraffic_Detail ON
(InterfaceAvailability_Detail.NodeID = InterfaceTraffic_Detail.NodeID) AND (InterfaceAvailability_Detail.InterfaceID = InterfaceTraffic_Detail.InterfaceID)

INNER JOIN

dbo.Interfaces ON

(InterfaceAvailability_Detail.NodeID = Interfaces.NodeID) AND (InterfaceAvailability_Detail.InterfaceID = Interfaces.InterfaceID)

INNER JOIN

dbo.Nodes ON

(InterfaceAvailability_Detail.NodeID = Nodes.NodeID)

WHERE

(DatePart(Hour, GETDATE()) >= 8) AND (DatePart(Hour, GETDATE()) <= 18)

AND

(
  (Nodes.Caption LIKE '%%Cisco Router%%') AND
  (Interfaces.Caption LIKE '%%Tunnel%%Spoke DMVPN%%')
  )

GROUP BY Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.Location,

Interfaces.Caption

ORDER BY 3 ASC

===============================================================================================

If you observe the codes closely, you would notice that the table names are the same as that of the table names in the Orion Database Manager, which is somewhat different from the way the Orion Report Writer is used. For example, to get the column "In_Maxbps" , the Orion Database Manager have it under the table named InterfaceTraffic_Detail, while searching for it using the Report Writer, it is simply under the table named InterfaceTraffic.

I wrote it that way because I had seen the way tables are defined in the Database Manager. That's why I wrote my SQL query to be that way. So as to enable me get the results that I desire.

Did I do anything wrong by doing that?

Regards,

Hi Vijay,

Also, I noticed that the codes of one of the legacy reports of the Orion Report Writer worked while I executed the query on the Database manager.

I exported it using the .csv tab and opened it in Excel but it did not dislay right.

Have you experienced such before?

If yes, please how did you go about it?

Regards,

Did you save the file as Filename.csv when you exported the file?

Yes I did save it as a .csv file.

Hello Guys,

Recently, I observed that while running the same code aforewritten, the SQL query code does not get executed.

Instead, I get an error message: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign key constraints."

Please take note that this code is the same that intermittently gets executed; I do not know why it's refused to run at this time.

See the screen shots below.

Any advice?

Regards,

David Ibrahim.

I am not sure if this is officially support, but since we have a quite large install of SQL servers around the datacenter, I typically use SQL Studio to run my queries. Once I have it reporting properly, Ill bring it into report writer to create the reports. This also helps me debug/troubleshoot my queries. (its free, so why not?)

Hi Dclick,

Once you are done with running the SQL queries correctly via SQL Studio, I guess you copy the codes into the Advanced SQL option of the Report Writer right?