Looking to create a Scheduled Unmanaged Node Report

Hi all,

Im relatively new to custom queries. What Im trying to do is create a scheduled report that will show all the nodes that are currently scheduled to be unmanaged at a future date and to show the unmanaged time window. I dont need to see the currently unmanaged nodes because I already have a widget to show that. As you know, when you schedule something in Orion, it wipes out any previous schedules that have been set for that node. If we had a scheduled report, we can verify whats already scheduled. Im hoping there is a custom query that I can use to generate this report. Any help would be greatly appreciated.

Find more posts tagged with

custom query widget

custom reports

Accepted answers

ecmel

Hi,

I think you can use the following SWQL query to pull the data for your report:

SELECT n.NodeID
, n.Caption as [Node Name]
, n.IP_Address as [IP Address]
, ToLocal(n.UnmanageFrom) as [Unmanage From]
, ToLocal(n.UnmanageUntil) as [Unmanage Until]
FROM Orion.Nodes n 
WHERE n.UnManageFrom > GETUTCDATE()
ORDER BY n.UnmanageFrom

All comments

ecmel

Hi,

I think you can use the following SWQL query to pull the data for your report:

SELECT n.NodeID
, n.Caption as [Node Name]
, n.IP_Address as [IP Address]
, ToLocal(n.UnmanageFrom) as [Unmanage From]
, ToLocal(n.UnmanageUntil) as [Unmanage Until]
FROM Orion.Nodes n 
WHERE n.UnManageFrom > GETUTCDATE()
ORDER BY n.UnmanageFrom

Ace4213

That works perfectly. Thanks!!!

Sinister1

This report works great! Does anyone know how to add who scheduled it? I tried adding the acctID field but its not working. Thank you!

monitoringlife

I made a reply over here that might help Unmanaged and muted not report with device notes

---please yell if shameless copy/paste is considered spam.

Try this (I have some exclusions on this you might want to pull off)

SELECT N.NodeName, A.AuditingActionType.ActionTypeDisplayName, AccountID, AuditEventMessage, TimeLoggedUtc, N.DetailsUrl
FROM Orion.AuditingEvents A
Join Orion.Nodes N on N.NodeID=A.NetworkNode
WHERE
    A.ActionTypeID in ('27', '28')
    AND N.UnManaged = '1'

mine looks like this

Sinister1

Thanks. Your report seems to show what is currently unmanaged (when viewing preview results), but not for systems that will be unmanaged in the future. The query from ecmel works perfectly for systems that will be unmanaged at a later date/time, but doesn't show who scheduled it. For now, i can track it via audit events, but it would be nice for it to show on the query report.

bobmarley

Try this link

Unmanaged Nodes with custom names - Custom Queries - The Orion Platform - THWACK (solarwinds.com)

Otherwise use this.

SELECT N.Caption AS NodeCaption, N.Status AS NodeStatus, IPAddress,   

     TOLOCAL(N.UnManageFrom) AS UnManageFrom, TOLOCAL(N.UnManageUntil) AS UnManageUntil,     

     TOLOCAL(AE.ObservationTimestamp) AS UnManagedWhen, AE.AccountID AS Who    

FROM Orion.Nodes N    

LEFT JOIN (

    SELECT A.NetworkNode, MAX(A.AuditEventID) AS AuditEventID

    FROM Orion.AuditingEvents A

    WHERE A.AuditingActionType.ActionType='Orion.NodeUnmanaged'

    GROUP BY A.NetworkNode

) AE1 ON N.NodeID = AE1.NetworkNode

LEFT JOIN Orion.AuditingEvents AE ON AE1.AuditEventID=AE.AuditEventID

--Where NodeStatus LIKE 9

ORDER BY UnManagedWhen DESC

Sinister1

Hi, thank you. However, again, this only shows systems that are currently unmanaged, not systems that will be unmanaged at a later date. To clarify, when you go to unmanage a system and choose to stop polling the object, you choose a future "beginning of maintenance" and "end of maintenance" date/time. As an example, today is 4/17/2023. I want to unmanage "server1" from 4/20/23 - 4/22/23. Ecmel's query above WILL show server1 will be unmanaged from 4/20/23 - 4/22/23, BUT, it doesn't show who scheduled it. Thats what I want to know, WHO scheduled it to be unmanaged from 4/20/23 - 4/22/23.

stevenstadel

Put the two scripts together

SELECT n.NodeID
, n.Caption as [Node Name]
, n.IP_Address as [IP Address]
, n.StatusDescription
, ToLocal(n.UnmanageFrom) as [Unmanage From]
, ToLocal(n.UnmanageUntil) as [Unmanage Until]
, ToLocal(AE.ObservationTimestamp) AS [UnManagedWhen]
, AE.AccountID AS [Who]    
FROM Orion.Nodes n 

LEFT JOIN (
    SELECT A.NetworkNode, MAX(A.AuditEventID) AS AuditEventID
    FROM Orion.AuditingEvents A
    WHERE A.AuditingActionType.ActionType='Orion.NodeUnmanaged'
    GROUP BY A.NetworkNode
) AE1 ON N.NodeID = AE1.NetworkNode
LEFT JOIN Orion.AuditingEvents AE ON AE1.AuditEventID=AE.AuditEventID
Where n.UnmanageFrom > '2023/01/01'

ORDER BY n.UnmanageFrom

bobmarley

Looks way nicer than mine Thank you!

I found an issue with the queries. These two lines:

, ToLocal(n.UnmanageUntil) as [Unmanage Until]
, AE.AccountID AS [Who]

Are pulling in from the AuditEvent Table. That table get populated at the time event occurs, not when it gets scheduled. So any data shown there is either from a previous event or is null. I'm not sure where the scheduling is kept in the database, if anyone knows please share.

If I unmanage a node in the future, in this case 2024

Then I view the node with the SWQL query it is showing an old date, from the last time I successfully unmanaged it. If I look in the AuditEvent table there is no record of that node being unmanaged today, it will show up on 4-18-24 when the event occurs.

ecmel

I think there is no easy way to pull the account that scheduled the maintenance. As shared previously, this information can be gathered from Audit Events but I'm not sure about the performance of the query in a large environment. I updated the query as below and it worked as expected in our LAB environment:

SELECT n.NodeID
, n.Caption as [Node Name]
, n.IP_Address as [IP Address]
, ToLocal(n.UnmanageFrom) as [Unmanage From]
, ToLocal(n.UnmanageUntil) as [Unmanage Until]
, a.AccountID as [Unmanaged By]
, ToLocal(a.TimeLoggedUtc) as [Action Time]
FROM Orion.Nodes n 
LEFT JOIN
(
    SELECT TimeLoggedUtc, AccountID, NetObjectID, AuditEventMessage
    FROM Orion.AuditingEvents
    WHERE NetObjectType='N'
    AND (ActionTypeID=29 AND AuditEventMessage like 'User % changed node % property UnManageUntil %')
    AND AuditEventID in 
    (
        SELECT LastEventID
        FROM
        (
            SELECT NetObjectID, max(AuditEventID) as LastEventID
            FROM Orion.AuditingEvents
            WHERE NetObjectType='N'
            AND (ActionTypeID=29 AND AuditEventMessage like 'User % changed node % property UnManageUntil %')
            GROUP BY NetObjectID
        )
    )
) a on n.NodeID=a.NetObjectID
WHERE n.UnManageFrom > GETUTCDATE()
ORDER BY n.UnmanageFrom