cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 11

Looking for a SQL query to get unmanaged nodes in the future

Hi,

I´m Looking for a SQL query to get unmanaged nodes in the future.

thanks

0 Kudos
5 Replies

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)

unmanaged_schedule.png

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

Level 15

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

Thanks! Good query.

0 Kudos

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-...