10 Replies Latest reply on Aug 14, 2014 1:11 PM by sgenius

    Executing SQL Queries on the Orion Database Manager

    sgenius


      Hi Guys,

       

      Please, has anyone executed SQL queries directly on the Orion Database Manager?

      Not using the Orion Report Writer's Advanced SQL report but directly on the Orion Database Manager.

       

      If so, I just want to ask how to export the results from the SQL query.

       

      Regards,

        • Re: Executing SQL Queries on the Orion Database Manager
          Vinay BY

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

            • Re: Executing SQL Queries on the Orion Database Manager
              sgenius

              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,

            • Re: Executing SQL Queries on the Orion Database Manager
              Vinay BY

              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.

                • Re: Executing SQL Queries on the Orion Database Manager
                  sgenius

                  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,


                • Re: Executing SQL Queries on the Orion Database Manager
                  Vinay BY

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

                  • Re: Executing SQL Queries on the Orion Database Manager
                    sgenius


                    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.

                    OrionDB1.jpg

                     

                    Any advice?

                     

                    Regards,

                     

                    David Ibrahim.

                    • Re: Executing SQL Queries on the Orion Database Manager
                      dclick

                      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?)