2 Replies Latest reply: Dec 30, 2011 5:09 PM by bobross RSS

    How To Generate an Accurate APM Unmanage Report

    bobross

      We have a report that shows the APM Devices that are currently unmanaged; unfortunately, because these values are in UTC time it is a pain to go through and perform the calculations to figure out what the actual times are.  When dealing with instances of Solarwinds that are in different time zones, this can become extra tedious.

      Our current Report is running the following query:

      SELECT ID, Name,  NodeID, UnmanageFrom, UnmanageUntil, UnManaged
      
      FROM APM_Application
      
      where Unmanaged = 1 or unmanagefrom >= getdate()
      
      ORDER BY 1 ASC, 2 DESC

      I've tried to modify the UnmanageFrom and UnmanageUntil fields using the TODATETIMEOFFSET function,but this is just returning blank data.

      Any ideas on how to get this report to show Local times instead of UTC times?

        • Re: How To Generate an Accurate APM Unmanage Report
          martin.susil

          Hello,

          following SQL query returns dates in local time (local to SQL server):

          SELECT 

               ID

               ,Name

               ,NodeID

           

               ,DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UnmanageFrom) as UnmanageFromLocalTime

               ,UnmanageFrom AS UnmanageFromUtcTime

           

               ,DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UnmanageUntil) as UnmanageUntilLocalTime

               ,UnmanageUntil AS UnmanageUntilUtcTime

           

               ,UnManaged

           

          FROM 

               APM_Application

          WHERE 

               Unmanaged = 1 OR unmanagefrom >= GetUtcDate()

           

          ORDER BY 

               1 ASC

               ,2 DESC

           

          Does it help to achieve what you need or are you looking for something else?