1 Reply Latest reply on Apr 8, 2015 3:29 PM by derhally

    Assistance in converting SQL to SWQL

    cgregors

      I have the following SQL query that runs on one Orion instance. 

       

      SELECT

        Containers_AlertsAndReportsData.GroupName AS 'Core Network Path',

        CAST(AVG(CAST(Containers_ContainerAvailability.GroupPercentAvailability as DECIMAL(10,2))) AS DECIMAL(10,2)) as 'Percent Availability'

      FROM

        Containers_AlertsAndReportsData with (nolock) INNER JOIN Containers_ContainerAvailability with (nolock) ON (Containers_AlertsAndReportsData.GroupID = Containers_ContainerAvailability.GroupID)

      WHERE

        DateTime BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND 

          Containers_AlertsAndReportsData.GroupName LIKE 'My Core Network%'

      GROUP BY

        Containers_AlertsAndReportsData.GroupName,Containers_AlertsAndReportsData.GroupID

      ORDER BY

          Containers_AlertsAndReportsData.GroupName ASC

       

      I would like to run it from the EOC and have it combine the results from 3 Orion instances.

       

      • Question 1: Can I convert the above query to SWQL?
      • Question 2: When / if I run it from the EOC, will it automatically query all 3 instances and pull the data together into a single report?
        • Re: Assistance in converting SQL to SWQL
          derhally

          EOC doesn't look at historical data, it primarily focuses on the current stats of Orion objects.  So that data is not available for you to run your query against.

           

          If you want to use SWQL against your Orion server, you'll need to use the Alerting entities and the Orion.ContainerStatus entity.

           

          If you want to automate this you could script the query to run against each server .