6 Replies Latest reply on Apr 9, 2014 8:27 AM by madhavan

    Filter for last 30 days

    stuartwhyte

      Hello,

       

      I'm using the below to pull down/up and duration statistics:

       

      SELECT
      NodeName AS [Node Name],
              '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
              DetailsUrl AS [_LinkFor_Node Name],
              DOWNEVENT.EVENTTIME AS [DOWN TIME],
              (SELECT TOP 1 UPEVENT.EVENTTIME AS ENDTIME FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) AS [UP TIME],
              (SELECT TOP 1 MINUTEDIFF(DOWNEVENT.EVENTTIME, UPEVENT.EVENTTIME) AS DIFF FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) AS [DOWN TIME IN MINUTES]
      FROM ORION.NODES NODES
      INNER JOIN ORION.EVENTS DOWNEVENT
      ON NODES.NODEID = DOWNEVENT.NETWORKNODE WHERE EVENTTYPE = 1 ORDER BY EVENTID DESC
      
      
      
      

       

      Can anyone help me with the WHERE statement to filter for only down events in the last 30 days.

       

      Thanks

       

      Stuart

        • Re: Filter for last 30 days
          RichardLetts

          EVENTTIME > dateadd(day,-30,getdate())

          1 of 1 people found this helpful
            • Re: Filter for last 30 days
              stuartwhyte

              Thanks RichardLetts, when I add that to my where line:

               

              WHERE EVENTTYPE = 1 AND EVENTTIME > dateadd(day,-30,getdate())  ORDER BY EVENTID DESC

               

              I get this error in the resource window:

               

              Error: A query to the SolarWinds Information Service failed.

                • Re: Re: Filter for last 30 days
                  madhavan

                  Hi,

                   

                  Use the below mentioned query. Here, DAYDIFF(DOWNEVENT.EVENTTIME, GETDATE()) < 30 will filter recent 30 days of data.

                   

                  SELECT 
                  NodeName AS [Node Name], 
                  '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name], 
                  DetailsUrl AS [_LinkFor_Node Name], 
                  DOWNEVENT.EVENTTIME AS [DOWN TIME], 
                  (SELECT TOP 1 UPEVENT.EVENTTIME AS ENDTIME FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) AS [UP TIME], 
                  (SELECT TOP 1 MINUTEDIFF(DOWNEVENT.EVENTTIME, UPEVENT.EVENTTIME) AS DIFF FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) AS [DOWN TIME IN MINUTES] 
                  FROM ORION.NODES NODES
                  INNER JOIN ORION.EVENTS DOWNEVENT 
                  ON NODES.NODEID = DOWNEVENT.NETWORKNODE
                  WHERE EVENTTYPE = 1 and DAYDIFF(DOWNEVENT.EVENTTIME, GETDATE()) < 30
                  ORDER BY EVENTID DESC
                  
                  1 of 1 people found this helpful
                    • Re: Filter for last 30 days
                      stuartwhyte

                      Thanks madhavan - works perfectly.

                       

                      One last request (as I KNOW this will come up) can I also filter for [DOWN TIME IN MINUTES] > (say) 10 minutes? Ive tried "and [DOWN TIME IN MINUTES] > 10" but this fails on me again.


                      Stuart

                        • Re: Re: Filter for last 30 days
                          madhavan

                          Hi,

                           

                          [DOWN TIME IN MINUTES] is just an alias. Use the below mentioned query. (Replace [DOWN TIME IN MINUTES] with the query which returns downtime in minutes.

                           

                          SELECT   
                           NodeName AS [Node Name],   
                           '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],   
                           DetailsUrl AS [_LinkFor_Node Name],   
                           DOWNEVENT.EVENTTIME AS [DOWN TIME],   
                           (SELECT TOP 1 UPEVENT.EVENTTIME AS ENDTIME FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) AS [UP TIME],   
                           (SELECT TOP 1 MINUTEDIFF(DOWNEVENT.EVENTTIME, UPEVENT.EVENTTIME) AS DIFF FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) AS [DOWN TIME IN MINUTES]   
                          FROM ORION.NODES NODES  
                          INNER JOIN ORION.EVENTS DOWNEVENT   
                           ON NODES.NODEID = DOWNEVENT.NETWORKNODE  
                          WHERE EVENTTYPE = 1 
                           AND (SELECT TOP 1 MINUTEDIFF(DOWNEVENT.EVENTTIME, UPEVENT.EVENTTIME) AS DIFF FROM ORION.EVENTS UPEVENT WHERE UPEVENT.EVENTTYPE = 5 AND UPEVENT.EVENTTIME > DOWNEVENT.EVENTTIME AND UPEVENT.NETWORKNODE = DOWNEVENT.NETWORKNODE ORDER BY UPEVENT.EVENTID ASC) > 10
                          ORDER BY EVENTID DESC