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)

            • Re: SWQL Help



              Thanks for the clarification on LIKE and DATEPART. Regarding the version, i am using version 2. The Nodes.ResposeTimeHistory.Availability is taken over which period of time?, i didn't the design the query i posted, but if i can't use the availability from ResponseTime_Daily or ResponseTime_Hourly in SWQL, then the only option i have is Nodes.ResponseTimeHistory.Availability?


              Best regards,

              Alberto Garcia