9 Replies Latest reply on Nov 20, 2012 3:17 PM by Leon Adato

    SQL gurus, can you help me with node filters for sequential time fames?

    mas589


      I am trying to define a page for our NOC that dispalys nodes down by time period. They want to see at a glance what just went down (within the last 8 hours) , what went down in the last 24 hours and what has been down for over 24 hours to help them see what would have been worked by a previous shift. I am not proficient yet by any means in SQL and can not seem to accomplish this. I tried using status=2 and SystemUpTime with time periods less than and greater than parameters but they do not work consisently often showing a node that has been down for days in the under 8 hours view.

       

      Has anyone successfully done this that can tell me what parameter to use and what the filter should look like?

       

      Thank you for your help if you can!

        • Re: SQL gurus, can you help me with node filters for sequential time fames?
          Leon Adato

          Do you want to see what WENT down (but might be up now), or what IS down, sorted (and possibly grouped) by duration? Just want to be clear before I start throwing SQL at you.

          • Re: SQL gurus, can you help me with node filters for sequential time fames?
            PavelSrot

            Here is example how to perform this one in ReportScheduler. Example give you only list of NodeID and this you can join with other tables. You can use it in report writer

            SET NOCOUNT ON;
            CREATE TABLE #tmpResponseTime
            (
               [NodeID] int,
               [DateTimeS] datetime,
               [DateTimeE] datetime,
               [Availability] int
               --[ChangeType] int - 0 from up to down, 1 from down to up
              
            );

            DECLARE @nodeID int
            DECLARE @dateTime DateTime
            DECLARE @availability int
            DECLARE @lastDateTime DateTime
            DECLARE @lastAvailability int

            DECLARE @lastNodeID int

            DECLARE responseTime_Cursor CURSOR FOR
            SELECT [NodeID], [DateTime], [Availability] FROM ResponseTime_Detail R1
            ORDER BY [NodeID], [DateTime]

             

            OPEN responseTime_Cursor
            FETCH NEXT FROM responseTime_Cursor INTO @nodeID, @dateTime, @availability
            SET @lastAvailability = @availability
            SET @lastDateTime = @dateTime

            SET @lastNodeID = @nodeID
            WHILE @@FETCH_STATUS=0
              BEGIN

                 IF @lastNodeID <> @nodeID

                    BEGIN

                        SET @lastDateTime = @dateTime

                        SET @lastAvailability = @availability

                    END

             

                 IF @lastAvailability <> @availability
                    BEGIN
                       INSERT INTO #tmpResponseTime (NodeID, DateTimeS, DateTimeE, Availability) VALUES(@nodeID, @lastDateTime, @dateTime, @availability)
                       SET @lastDateTime = @dateTime
                    END

             

                 SET @lastAvailability = @availability

                 SET @lastNodeID = @nodeID
                 FETCH NEXT FROM responseTime_Cursor INTO @nodeID, @dateTime, @availability
                
              END
             
            CLOSE responseTime_Cursor
            DEALLOCATE responseTime_Cursor

            SELECT Av.* FROM (
            SELECT NodeID, datediff(hour ,DateTimeS, DateTimeE) AS [Hours]  FROM #tmpResponseTime
            WHERE [Availability]=100
            ) AS Av
            Where AV.[Hours]>16 -- you will retrieve list of nodes which was down longer than 16 hours

            DROP TABLE #tmpResponseTime

              • Re: SQL gurus, can you help me with node filters for sequential time fames?
                mas589

                Wow, no wonder I couldn't figure this one out!! Thanks so much I will set this up right away!!!!

                  • Re: SQL gurus, can you help me with node filters for sequential time fames?
                    Leon Adato

                    This one may be a little simpler (no cursors, etc). What it does is join the nodes table to the alertstatus table, pulling the durationfor how lnog a device was down. The one key piece is that it uses the built-in alert called "Alert me when a device goes down" as it's key. If you use a different alert for up/down, change the title to match.

                     

                    select nodes.caption,  Nodes.GroupStatus, nodes.nodeid, nodes.percentloss,

                    a1.downtime, nodes.n_mute_reason as Addtl_Info

                    from Nodes

                    left outer join (select AlertStatus.ActiveObject as nodeid,

                         CONVERT(varchar, alertstatus.triggertimeoffset/60/60/24) as days,

                         +CONVERT(varchar, alertstatus.triggertimeoffset/60/60 % 24) as hours,

                         +CONVERT(varchar, alertstatus.triggertimeoffset % 60) as minutes,

                         CONVERT(varchar, alertstatus.triggertimeoffset/60/60/24)+' d '

                         +CONVERT(varchar, alertstatus.triggertimeoffset/60/60 % 24)+' h '

                         +CONVERT(varchar, alertstatus.triggertimeoffset % 60)+'m' as downtime,

                         AlertDefinitions.AlertName

                         from AlertStatus

                         join AlertDefinitions on AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

                         WHERE

                         alertdefinitions.alertname like '%alert me when a node goes down%'

                         OR alertdefinitions.alertname is NULL) a1

                    on convert(varchar, Nodes.nodeid) = a1.nodeid

                    WHERE

                    nodes.status <> 9

                    order by nodes.GroupStatus asc,

                    a1.days desc, a1.hours desc, a1.minutes desc, nodes.caption

                      • Re: SQL gurus, can you help me with node filters for sequential time fames?
                        mas589

                        Thanks again, went to report writer and used one of the sql reports when I enter this it gives an error of, invlaid column name 'n_mute_reason', did I miss a step?

                          • Re: SQL gurus, can you help me with node filters for sequential time fames?
                            Leon Adato

                            Nope, I forgot that everyone doesn't have that custom field. Here's the updated SQL:

                             

                            select nodes.caption,  Nodes.GroupStatus, nodes.nodeid, nodes.percentloss,

                            a1.downtime

                            from Nodes

                            left outer join (select AlertStatus.ActiveObject as nodeid,

                                 CONVERT(varchar, alertstatus.triggertimeoffset/60/60/24) as days,

                                 +CONVERT(varchar, alertstatus.triggertimeoffset/60/60 % 24) as hours,

                                 +CONVERT(varchar, alertstatus.triggertimeoffset % 60) as minutes,

                                 CONVERT(varchar, alertstatus.triggertimeoffset/60/60/24)+' d '

                                 +CONVERT(varchar, alertstatus.triggertimeoffset/60/60 % 24)+' h '

                                 +CONVERT(varchar, alertstatus.triggertimeoffset % 60)+'m' as downtime,

                                 AlertDefinitions.AlertName

                                 from AlertStatus

                                 join AlertDefinitions on AlertStatus.AlertDefID = AlertDefinitions.AlertDefID

                                 WHERE

                                 alertdefinitions.alertname like '%alert me when a node goes down%'

                                 OR alertdefinitions.alertname is NULL) a1

                            on convert(varchar, Nodes.nodeid) = a1.nodeid

                            WHERE

                            nodes.status <> 9

                            order by nodes.GroupStatus asc,

                            a1.days desc, a1.hours desc, a1.minutes desc, nodes.caption