While you asked for a SQL query I do this with a SWQL query in a Custom Query resource (NPM 10.6 or later to support ToLocal function)
n.caption as Node
, '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node]
, '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]
, tolocal(UnManageFrom) as Unmanage
, daydiff(getutcdate(), UnManagefrom)as [Days till Unmanage]
, tolocal(UnManageUntil) as Remanage
, daydiff(getutcdate(), UnManageuntil)as [Days until Remanage]
FROM Orion.Nodes n
unmanaged = 'true' and
(minutediff(getutcdate(), UnManagefrom) > -10080)
minutediff(getutcdate(), UnManagefrom) >0
and minutediff(getutcdate(), UnManagefrom) <10080
order by unmanagefrom
If you are looking for nodes that have been set to be unmanaged starting in the future, you can use this:
CAPTION AS 'DEVICE',
IP_ADDRESS AS 'IP ADDRESS',
UNMANAGEFROM AS 'BEGIN UNMANAGE',
UNMANAGEUNTIL AS 'END UNMANAGE'
WHERE UNMANAGEFROM > (GETDATE())
If you are trying to use SQL to set devices to be unmanaged in the future, that will be different.
Can you tell me why all my unmanaged nodes show 3 hours forward ?
I did an unmanaged node today from 18:00 to 19:00 , but running this query I get Unmanaged from 21:00 to 22:00 .
Those times are displayed in the interface as local timezone but stored in the DB as UTC. The SWQL I provided below converts for you but you could also do that with SQL -
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.