Hi,
I´m Looking for a SQL query to get unmanaged nodes in the future.
thanks
If you are looking for nodes that have been set to be unmanaged starting in the future, you can use this:
SELECT
CAPTION AS 'DEVICE',
IP_ADDRESS AS 'IP ADDRESS',
UNMANAGEFROM AS 'BEGIN UNMANAGE',
UNMANAGEUNTIL AS 'END UNMANAGE'
FROM NODES
WHERE UNMANAGEFROM > (GETDATE())
If you are trying to use SQL to set devices to be unmanaged in the future, that will be different.
- ZackM
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)
SELECT
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]
, unmanaged
, 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
where
(
unmanaged = 'true' and
(minutediff(getutcdate(), UnManagefrom) > -10080)
)
or
(
minutediff(getutcdate(), UnManagefrom) >0
and minutediff(getutcdate(), UnManagefrom) <10080
)
order by unmanagefrom
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 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.