4 Replies Latest reply on Aug 20, 2014 3:01 PM by mharvey

    SQL to SWQL - Report writer to web reports


      I'm trying to migrate some of my reports into web report to have one report rather than a multitude.


      I have a large custom report that looks for the average availability of last month per custom group. I can't get it to work in the SWQL and was wondering if anyone could help? If I can get this small section to work then I can edit the rest of the query.



      SELECT TOP 10000 AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,

             COUNT(DISTINCT Nodes.NodeID) AS COUNT_of_NodeID,

             'Campus-Core-Agg' AS AREA

      FROM   Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)

      WHERE  ( DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))


      AND    (

                  (Nodes.Vendor = 'Juniper Networks, Inc.')

             AND  (Nodes.Core_Aggregation = 1)



      Many thanks!!!

        • Re: SQL to SWQL - Report writer to web reports

          This is always a fun problem because SWQL works the same, but the table structure and functions change.


          For the tables, it looks like you need to prepend them with Orion.  Below is an example of this.  It's not always Orion and may be different depending upon the tables that you are trying to access.  You can install the SDK to get the reference material.  The SWQL Studio in the SDK is also very helpful for this kind of work.


          SELECT TOP 10000 AVG(rt.Availability) as AVERAGE_of_Availability

          from Orion.ResponseTime rt


          The avg, count, and distint functions are valid, but the Date/Time functions change in SWQL.  Can you describe the date range that you are looking for?  Is it the last 30 days or all items for a particular month?



          • Re: SQL to SWQL - Report writer to web reports

            In the Web Reports you have the option of using Custom SQL rather than SWQL so you can use the query as is, rather than changing the query to SWQL. 


            Web Reports.PNG


            For last month, you can use this in your where clause:


            DATEPART(m, DateTime) = DATEPART(m, DATEADD(m, -1, getdate()))


            Matthew Harvey

            Loop1 Systems