4 Replies Latest reply on Aug 25, 2014 4:20 AM by madhavan

    Anyone have time to convert the SQL for the Availability Report in Report Writer to SWQL?

    ks1917

      I've been trying to convert the SQL for the availability report in report writer to SWQL for close to week.  I'm not having much luck, beyond my current skill level.  Anyone have time to help?

      I've pretty much determined that DECLARE and SET startements are out.

      The "WITH (NOLOCK)" also causes SWQL to choke.

       

      From what I can tell, I think I should be able to just use the Orion.NPM.Interfaces table is it's auto-magical links to rewrite in SWQL,

      Using the auto-magical links the statements:

      SUM(IA.Availability.Availability * IA.Availability.Weight) as Sum1

      Sum(IA.Availability.Weight) as Sum2

      both cause an error.  I can select those fields, just can use them in a SUM statement.

       

      Here's the SQL from Report Writer:

       

      DECLARE @StartDate DateTime

      DECLARE @EndDate DateTime

      SET @StartDate = FLOOR(CAST(DATEADD(WEEK, -1, GETDATE()) AS FLOAT));

      SET @EndDate = CAST(GETDATE() AS FLOAT);

      WITH T1 AS{

         SELECT

            IA.NodeID,

         IA.InterfaceID,

         SUM(IA.Availability * IA.Weight) AS Sum1,

         SUM(IA.Weight) AS Sum2

        FROM InterfaceAvailability IA WITH(NOLOCK)

        WHERE

        IA.DateTime between @StartDate and @EndDate

        GROUP BY

        IA.NodeID,

        IA.InterfaceID

      }

       

      SELECT

        IA.nodeid, IA.interfaceid, IA.NodesVendorIcon, IA.NodeName,

        IA.InterfaceICon, IA.InterfaceCaption,

        IA.Availability

      FROM {

        SELECT

        Nodes.NodeID,

        Interfaces.InterfaceID,

        SUM(Sum1) / SUM(Sum2) AS Availability,

        Interfaces.InterfaceIcon, Interfaces,Caption as InterfaceCaption,

        Nodes.Caption as NodeName,

        Nodes.VendorIcon as NodesVendorIcon

        FROM T1

        INNER JOIN Nodes WITH(NOLOCK)

        ON

        T1.NodeID = Nodes.NodeID

        INNER JOIN Interfaces WITH(NOLOCK)

        ON

        T1.InterfaceID = Interfaces.InterfaceID

        GROUP BY

        Nodes.NodeID, Interfaces.InterfaceID, Interfaces.InterfaceIcon, Interfaces.Caption, Nodes.Caption. Nodes.VendorIcon

        AS IA

        ORDER BY

        NodeID, InterfaceID