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
http://www.loop1systems.com
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
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 -
http://stackoverflow.com/questions/8038744/convert-datetime-column-from-utc-to-local-time-in-select-statement
Thanks! Good query.
