1 Reply Latest reply on Jun 21, 2016 1:01 PM by mwb

    SQL Query vs. SWQL Query

    mwb

      Looking for a favor and feedback from people very familiar with SWQL.  I'm aware of the language differences from SQL and have written a number of resources with the SWQL thanks to pointers and information shared here (thanks, guys!).  However, for the life of me I can't wrap my head around how I'm getting different results from the SQL and SWQL queries below.

       

      Can anyone give me an idea?

       

      For the record, this query is meant to look at any nodes that have been down within the last fifteen minutes (availability less than 100) and lay out the information for the node. "Department" is a Custom Property for Nodes.

       

      In the example I'm looking at this moment, I'm seeing a result in the SWQL query while the SQL is empty.

       

      SQL:

      select Caption, First, Last, Availability from 
      (select n.NodeID, Caption, Min(DateTime) as First, Max(DateTime) as Last from ResponseTime rt
      
      JOIN Nodes n
      on n.NodeID=rt.NodeID
      
      where rt.Availability<>100
      and Department='xxxx'
      and DateTime>DATEADD(hh,-1,getdate())
      and DateTime Between DATEADD(MINUTE, -15, getDate())
                             And GETDATE() 
      
      Group By n.NodeID, Caption) x
      
      JOIN 
      (select NodeID, AVG(Availability) as Availability from ResponseTime rt
      
      where DateTime>DATEADD(hh,-1,getdate())
      and DateTime Between DATEADD(MINUTE, -15, getDate())
                             And GETDATE() 
      Group by NODEID
      ) a
      ON a.NodeID=x.NodeID
      ORDER BY CAPTION
      

       

      SWQL:

      select x.Caption,  a.Availability , x.Start, x.Stop from 
      (select n.NodeID, Caption, Min(DateTime) as Start, Max(DateTime) as Stop from Orion.ResponseTime rt
      
      JOIN (Select NodeId, Caption from Orion.Nodes) n
      on n.NodeID=rt.NodeID
      JOIN (Select NodeID, Department from Orion.NodesCustomProperties) ncp
      on n.NodeID=ncp.NodeID
      
      where rt.Availability<>100
      and ncp.Department='xxxx'
      and DateTime> ADDDATE('HOUR', -1, GETDATE())
      and DateTime>= ADDDATE('MINUTE', -15, GETDATE())
      and DateTime<GETDATE()
      Group By n.NodeID, Caption
      ) x
      
      JOIN 
      (select rt.NodeID, AVG(rt.Availability) as Availability from ORION.ResponseTime rt
      
      where DateTime> ADDDATE('HOUR', -1, GETDATE())
      and DateTime>= ADDDATE('MINUTE', -15, GETDATE())
      and DateTime<GETDATE()
      GROUP by NodeID
      ) a
      ON a.NodeID=x.NodeID
      ORDER BY CAPTION
      
        • Re: SQL Query vs. SWQL Query
          mwb

          I think I already answered my own question with SolarWinds timezones and SWQL differences striking again.

           

          SWQL GETDATE() returns the local time of the web user whereas SQL GETDATE() returns the time of the database.  In my case, I'm looking at two windows of time that are two hours apart from each other.

           

          Sigh.