1 Reply Latest reply on Dec 2, 2013 3:19 PM by zackm

    "Down Devices Since" Report

    kiwi

      Hello

       

      - I downloaded this report from thwack Content Exchange area. Down Nodes with Duration also here attached

      - Removed this part ( , nodes.n_mute_reason as Addtl_Info ) from the SQL SELECT query since the filed is a Custom Propert which I don't need )

      - Added a PC as an SNMP Node then disconnected it to produce the "Node Down"  Event

      - I ran the report which shows the exected Day / Hours / Minutes the PC had been down  like:  0d 1h 12m (i.e 0 Day, 1 Hour and 12 Min)

       

      Problem: When I came next morning (aftre 15 hours and 23 Min)  it shows  "Down_Since" 1d 16h 35m while it supposed to be 0d 16h 35m

      So, as long as we are within the same day, before midnight, the Report is Ok but not next day where it shows an "Extra Day"

      In fact for  "n" down days it shows "n+1" down days execpt  when it's withing the same day

       

      Whoever willing to help can easily test it after removing the Custom Propert Field I mention above and enabling SNMP on a test PC

      I'd appreciate a SQL Help to fix this bug please

       

      Thank you

      Down Nodes with Duration

        • Re: "Down Devices Since" Report
          zackm

          here is one i have used before for EST (you can edit the datediff line for different time zones)

           

          SELECT 
              NodeID,
              Caption AS 'HostName',
              IP_Address AS 'IP Address',
              'NODE' AS 'Device Type',
              ObjectSubType AS 'Polling Method',
              DATEDIFF(HH, DATEADD(HH, -4, TriggerTimeStamp), GetDate()) AS [Time Down in Hours]
          FROM 
              Nodes
          JOIN 
              AlertStatus
          ON NodeID=ActiveObject
          WHERE 
              Status = 2
          
          UNION ALL
          
          SELECT 
              InterfaceID,
              Caption AS 'HostName',
              'NO IP ADDRESS' AS 'IP Address',    
              'INTERFACE' AS 'Device Type',
              ObjectSubType AS 'Polling Method',
              DATEDIFF(HH, DATEADD(HH, -4, TriggerTimeStamp), GetDate()) AS [Time Down in Hours]
          FROM 
              Interfaces
          JOIN 
              AlertStatus
          ON 
              InterfaceID=ActiveObject
          WHERE 
              Status = 2
          AND
              Caption NOT LIKE '%Bearer%'
          AND 
              WAN_Interface_Department IS NOT NULL
          
          ORDER BY [Time Down in Hours] DESC