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

    SWQL Help

    algarciat

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

      SELECT 
        NodeID,
        NodeName, 
        AVG( Availability) as Availability,  
      DATEPART(yy,Date) as aYear, DATEPART(ww,Date) as week
      
      FROM   (
      SELECT 
        ResponseTime_Daily.DateTime as Date,
        Nodes.NodeID AS NodeID,
        Nodes.Caption as NodeName, 
        ResponseTime_Daily.Availability as  Availability
      FROM
        Nodes INNER JOIN ResponseTime_Daily ON (Nodes.NodeID = ResponseTime_Daily.NodeID)
      WHERE 
        (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
          derhally

          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
              algarciat

              Derhally,

               

              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