4 Replies Latest reply on Jun 26, 2013 8:57 AM by algarciat

    SWQL Help


      I am trying to create the SWQL version of a SQL report written by somebody else. This is just part of the SQL Query

        AVG( Availability) as Availability,  
      DATEPART(yy,Date) as aYear, DATEPART(ww,Date) as week
      FROM   (
        ResponseTime_Daily.DateTime as Date,
        Nodes.NodeID AS NodeID,
        Nodes.Caption as NodeName, 
        ResponseTime_Daily.Availability as  Availability
        Nodes INNER JOIN ResponseTime_Daily ON (Nodes.NodeID = ResponseTime_Daily.NodeID)
        (Nodes.Caption LIKE '%[0-9][0-9]%') AND  ResponseTime_Daily.DateTime >= '2013'
      UNION .............

      I have a few questions.

      In the Orion SDK table description html, i don't see any  ResponseTime_Daily, ResponseTime_Detail  or ResponseTime_Hourly table, but they are used in the hole SQL query. How this tables are accessed using SWQL?

      can DATEPART be used in SWQL?

      can LIKE be used in SWQL?


      Would really appreciate any help on this.


      Best regards,

      Alberto Garcia

        • Re: SWQL Help

          What version of Orion are you using?


          You can use LIKE similar to SQL.  And the corresponding DATEPART functions, are Year(), Week, Month, etc..


          In SWQL  you can get the response time by querying Orion.ResponseTime entity or you can use the "navigation property" RepsonseTimeHistory on the Nodes Entity


          Here is a SWQL query that groups the availability by Node, Year and Week


          SELECT Nodes.NodeID, Nodes.NodeName, AVG(Nodes.ResponseTimeHistory.Availability) AS AvgAvailability, Year(Nodes.ResponseTimeHistory.DateTime)AS Year, Week(Nodes.ResponseTimeHistory.DateTime) AS Week

          FROM Orion.Nodes

          GROUP BY Nodes.NodeID, Nodes.NodeName, Year(Nodes.ResponseTimeHistory.DateTime), Week(Nodes.ResponseTimeHistory.DateTime)