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

              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'"