This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

How To Generate an Accurate APM Unmanage Report

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?

  • 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?

  • This is exactly what I was looking for.  Thanks a ton!