This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

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

  • 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)

  • 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

  • The Orion.ResponseTime exposes all the response time data for you.  It includes daily, hourly and detail data which has not been summarized yet.