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?