26 Replies Latest reply on Jul 28, 2016 3:56 PM by matt.hill

    Modify a top ten display to include a downtime count

    masonstryker

      Hello! I would like to modify the realtime displays of any node that may be down to include a 'downtime counter' so that in once quick glance not only am I seeing the node down, I'm seeing how long it has been down for. Can anyone point me in the right direction please? Thank you!cust

        • Re: Modify a top ten display to include a downtime count
          mezdem

          You can create a custom report using the writer on the orion server.


          The sql would be similar to this, but remember to format the fields to use the 'gif' as a status icon etc.


          This can then be added as a custom report to your homepage/top 10 page etc.

           

          select NodeID,IP_Address,Caption, Status, StatusLED, LastSystemUpTimePollUtc, DATEDIFF(hh,LastSystemUpTimePollUtc,GETDATE()) as HoursDown

          from nodes

          where StatusLED = 'down.gif'

           

           

           

           

           

          1 of 1 people found this helpful
            • Re: Modify a top ten display to include a downtime count
              masonstryker

              Thank you! Worked like a charm!

              • Re: Modify a top ten display to include a downtime count
                masonstryker

                Mezdem, is there an easy way to include minutes down as well?

                  • Re: Modify a top ten display to include a downtime count
                    mezdem

                    How about something like this, you can choose how you display..

                     

                    select NodeID,IP_Address,Caption, Status, StatusLED, LastSystemUpTimePollUtc,

                    DATEDIFF(mi,LastSystemUpTimePollUtc,GETDATE()) % 60 as MinsDown,

                    DATEDIFF(hh,LastSystemUpTimePollUtc,GETDATE())% 24 as HoursDown,

                    DATEDIFF(d,LastSystemUpTimePollUtc,GETDATE()) as DaysDown,

                    --OR

                    convert(varchar(10), DATEDIFF(d,LastSystemUpTimePollUtc,GETDATE())) + ':' +

                    convert(varchar(10), DATEDIFF(hh,LastSystemUpTimePollUtc,GETDATE())% 24 ) + ':'+

                    convert(varchar(10), DATEDIFF(mi,LastSystemUpTimePollUtc,GETDATE()) % 60) as 'DD:HH:MM:SS',

                    --OR

                    convert(varchar(10), (DATEDIFF(d,LastSystemUpTimePollUtc,GETDATE()))) + ' Days ' +

                    convert(varchar(10), (DATEDIFF(hh,LastSystemUpTimePollUtc,GETDATE())% 24 )) + ' Hours '+

                    convert(varchar(10), (DATEDIFF(mi,LastSystemUpTimePollUtc,GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

                    from nodes

                    where StatusLED = 'down.gif'

                     

                    3 of 3 people found this helpful
                      • Re: Modify a top ten display to include a downtime count
                        mezdem

                        although i am not quite sure that is correct looking at it again... i may need to revisit this

                        • Re: Modify a top ten display to include a downtime count
                          masonstryker

                          That was perfect - everything I need in one script. Thank you!

                          • Re: Modify a top ten display to include a downtime count
                            rgward

                            Thanks mezdem.  Just what I was looking for.  However, how can I change the sql to work for my timezone (GMT -5)?

                              • Re: Modify a top ten display to include a downtime count
                                mezdem

                                hmm not really had to convert this before personally... but maybe something along the lines of the below?

                                 

                                SELECT

                                DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc)AS ColumnInLocalTime ,

                                .LastSystemUpTimePollUtc as LastSystemUpTimePollUtc

                                FROM Nodes

                                 

                                 

                                  • Re: Modify a top ten display to include a downtime count
                                    mezdem

                                    providing your sql servers local time is set to GMT-5 then your Getdate() will be correct and then just diff this from the UTC date.#

                                     

                                    --change all the 'LastSystemUpTimePollUtc' in code to 'DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc)'

                                     

                                    select NodeID,IP_Address,Caption, Status, StatusLED, LastSystemUpTimePollUtc,

                                    DATEDIFF(mi,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE()) % 60 as MinsDown,

                                    DATEDIFF(hh,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE())% 24 as HoursDown,

                                    DATEDIFF(d,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE()) as DaysDown,

                                    --OR

                                    convert(varchar(10), DATEDIFF(d,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE())) + ':' +

                                    convert(varchar(10), DATEDIFF(hh,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE())% 24 ) + ':'+

                                    convert(varchar(10), DATEDIFF(mi,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE()) % 60) as 'DD:HH:MM:SS',

                                    --OR

                                    convert(varchar(10), (DATEDIFF(d,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE()))) + ' Days ' +

                                    convert(varchar(10), (DATEDIFF(hh,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE())% 24 )) + ' Hours '+

                                    convert(varchar(10), (DATEDIFF(mi,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), Nodes.LastSystemUpTimePollUtc),GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

                                    from nodes

                                    where StatusLED = 'down.gif'

                                    1 of 1 people found this helpful
                                      • Re: Modify a top ten display to include a downtime count
                                        rgward

                                        Worked perfect!  Thanks so much! 

                                        • Re: Modify a top ten display to include a downtime count
                                          rgward

                                          mezdem,

                                          Hope you can help on this?  I just noticed that the result down time is 1 hour off.  If the node is down only 15 min the result for DD:HH:MM:SS is reported as 0 Days 1 Hours 15 Mins.  Any thoughts?

                                           

                                          SELECT StatusLED

                                          , NodeID

                                          , Caption

                                          , Status

                                          , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc) AS LastSystemUpTimePoll

                                          ,convert(varchar(10), (DATEDIFF(d,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()))) + ' Days ' +

                                          convert(varchar(10), (DATEDIFF(hh,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE())% 24 )) + ' Hours '+convert(varchar(10), (DATEDIFF(mi,DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), LastSystemUpTimePollUtc),GETDATE()) % 60)) + ' Mins ' as 'DD:HH:MM:SS'

                                           

                                           

                                          From Nodes

                                          WHERE StatusLED = 'down.gif'

                                           

                                           

                                          ORDER BY 6 desc

                                          1 of 1 people found this helpful
                                            • Re: Modify a top ten display to include a downtime count
                                              mezdem


                                              hi yep sorry i thought it was a little out, i have since found a better way of doing this. i have this in the ncm content sharing zone under `availability reports`. i will get the sql for u as soon as i can.

                                                • Re: Modify a top ten display to include a downtime count
                                                  mezdem


                                                  i think this works! run them next to each other and see if ok for u! working on my nexus 10 tablet so copy and paste is fun!

                                                   

                                                   

                                                  SELECT statusled, nodeid, caption, status,


                                                  CONVERT(varchar(6), convert(varchar(10), DATEDIFF(ss,lastsystemuptimepollutc, GETUTCDATE())) /86400)+ ' Days '


                                                  + RIGHT('0' + CONVERT(varchar(6), (convert(varchar(10), DATEDIFF(ss,lastsystemuptimepollutc, GETUTCDATE())) %86400)/3600),2)


                                                  + ' Hours ' + RIGHT('0' + CONVERT(varchar(2), (convert(varchar(10), DATEDIFF(ss,lastsystemuptimepollutc, GETUTCDATE()))  % 3600) / 60), 2)


                                                  + ' Minutes ' + RIGHT('0' + CONVERT(varchar(2), convert(varchar(10), DATEDIFF(ss,lastsystemuptimepollutc, GETUTCDATE()))  % 60), 2)+ ' Seconds'


                                                  as DownTime


                                                  From Nodes


                                                  WHERE StatusLED = 'down.gif'

                                                   

                                                   

                                                   

                                                  1 of 1 people found this helpful
                                                    • Re: Modify a top ten display to include a downtime count
                                                      mezdem

                                                      basically this is a lot simpler, convert the time difference between now and last poll of the node, converted into seconds. then you can easily convert the seconds into downtime - or uptime as i use for our monthly and quarterley availability stats..

                                                       

                                                      Does this work rgward?

                                                        • Re: Modify a top ten display to include a downtime count
                                                          rgward

                                                          mezdem, your code worked great.  Thanks for your help!   I didn't need the seconds conversion so I stripped that off.

                                                           

                                                          Now, I have one last date issue I can't seem to get to work.  I have a custompoller which returns date string in format mm/dd/yy that I need to select only records with dates greater than 3 years old.  This is what I've tried and a couple other ways but is not filtering dates older than 3 years.  Can you help?

                                                           

                                                          WHERE CustomPollerStatus.Status < CONVERT(varchar(8), DATEADD(year,-3,getdate()), 1))

                                                            • Re: Modify a top ten display to include a downtime count
                                                              mezdem

                                                              glad it works! I took the seconds off also!!

                                                               

                                                              re the custom poller, i have never queried that field always use the historical data.

                                                               

                                                              But this works on mine... might need tweaking for pollerID etc

                                                               

                                                              select *,

                                                              convert(datetime,CustomPollerStatus.Status),

                                                              DATEADD(year,-3,getdate())

                                                              from dbo.CustomPollerStatus

                                                              WHERE

                                                              convert(datetime,CustomPollerStatus.Status) < DATEADD(year,-3,getdate())

                                                              and

                                                              CustomPollerAssignmentID ='c805c80a-b53f-4dd8-91f4-00ba3344ac04'

                                                               

                                                                • Re: Modify a top ten display to include a downtime count
                                                                  marunderwood

                                                                  If your comparing varchar datetime's, you'll want them formatted as YYYYMMDDSS. This might help http://technet.microsoft.com/en-us/library/ms187928.aspx to choose the correct format.  Perhaps 111 is needed, instead of 1.

                                                                  • Re: Modify a top ten display to include a downtime count
                                                                    rgward

                                                                    mezdem,

                                                                    When I apply your suggestions, SQL throws the error "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.".

                                                                    If I omit the code changes marked in bold the report executes as expected for all nodes.

                                                                     

                                                                    Here is the complete SQL.  This may help better understand what I'm trying to do.

                                                                     

                                                                    SELECT Nodes.NodeID AS NodeID

                                                                    , Caption AS NodeName

                                                                    , Nodes.Modal AS Modal

                                                                    , CustomPollerAssignment.AssignmentName AS Assignment

                                                                    , CustomPollers.UniqueName AS PollerName

                                                                    , CONVERT(datetime,CustomPollerStatus.Status) AS ReplacementDate

                                                                    FROM CustomPollerStatus

                                                                     

                                                                    JOIN CustomPollerAssignment ON CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID

                                                                    JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID

                                                                    JOIN Nodes ON (Nodes.NodeId = CustomPollerAssignment.NodeId)

                                                                     

                                                                    WHERE ((CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate')

                                                                    AND CONVERT(datetime, CustomPollerStatus.Status) < CONVERT(varchar(8), DATEADD(year,-3,getdate()), 1))

                                                                    AND CONVERT(datetime, CustomPollerStatus.Status) < DATEADD(year,-3,getdate())


                                                                    Edit:  pasted the wrong statement...corrected statement.

                                              • Re: Modify a top ten display to include a downtime count
                                                Manilyn Ramos

                                                Hi, I'm confused on how you format the status ICON. I was trying to create a report where I concatenated the VendorIcon as well as the Vendor fields. However, the output was 311.gifWindows  

                                              • Re: Modify a top ten display to include a downtime count
                                                matt.hill

                                                HI,

                                                 

                                                This is almost perfect, but I can't get the results I want. I want to apply a filter, but I don't know SQL very well. This is the filter I'd like to apply, but I can't seem to get the syntax right.

                                                (caption not like 'kd*') AND (Device_Type!='Printer') AND (status <>9) AND (MachineType not like 'Cradle*')

                                                 

                                                I'm assuming status <> 9 is redundant with the "

                                                 

                                                WHERE StatusLED = 'down.gif'"