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

Custom Query to list muted and unmanaged nodes with details

I'm expounding on an original request I made here.

I want to list the status, node name, from - until and who muted/unmanaged (not in screen shot). 

unmanged_nodes.png

I've come dangerously close by using the query written by grta​ on this post.  The problem I'm having is that the Muted nodes are still showing up in the query despite their "until" times expiring.  I used the snipped of code that @grta mentioned in their original reply but when doing so I only get Unmanaged nodes.  I do have muted nodes to test with, I set one indefinitely prior to running this query. 

The code in question is, you'll see that I added it to the 2nd select.

where  

-- Added to remove outdated period of muting 

tolocal(SuppressUntil) > GETDATE()  

AND tolocal(SuppressFrom) < GETDATE()  

-- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%') 

unmanged_nodes1.png

The query I'm using is as follows:

SELECT

'Unmanaged' as [Status]

,n.Caption AS [Node]

,tostring(tolocal(n.UnManageFrom)) AS [From]

,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(n.UnManageUntil)) end AS [Until]

,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

,CASE

WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'

ELSE ae.AccountID

END AS [Account]

FROM

Orion.Nodes n

JOIN (

    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent

    FROM Orion.AuditingEvents rec

    WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'

    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID

JOIN (

    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

    FROM Orion.AuditingEvents ae

    WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

WHERE n.Status = 9

union all

(SELECT

'Muted' as [Status]

,n.caption

,tostring(tolocal(SuppressFrom)) as [From]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'

else tostring(tolocal(SuppressUntil )) end AS [Until]

,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'

else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]

,n.DetailsURL AS [_LinkFor_Node]

,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

, ae.AccountID AS [Account]

FROM Orion.AlertSuppression asup

join orion.nodes n on asup.entityuri=n.uri

join (

    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent

    FROM Orion.AuditingEvents ae

    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID

join (

    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc

    FROM Orion.AuditingEvents ae

    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')

    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent

where  

-- Added to remove outdated period of muting 

tolocal(SuppressUntil) > GETDATE()  

AND tolocal(SuppressFrom) < GETDATE()  

-- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%') 

)

ORDER BY [node] asc, [status] desc

In closing, I'm a SWQL noob, cobbling together bits and pieces of items to fashion what I want...

Thanks

Wes

4 Replies
Level 12

Unmanaged show up fine, but I'm not seeing muted nodes.

0 Kudos

I went back and edited the query that does unmanaged and unmuted and added the same OR clause and it appears to work.  SWQL follows.  Your mileage may vary...

SELECT


'Unmanaged' as [Status]


,n.Caption AS [Node]


,tostring(tolocal(n.UnManageFrom)) AS [From]


,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'


else tostring(tolocal(n.UnManageUntil)) end AS [Until]


,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'


else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]


,n.DetailsURL AS [_LinkFor_Node]


,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]


,CASE


WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'


ELSE ae.AccountID


END AS [Account]


FROM


Orion.Nodes n


JOIN (


    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent


    FROM Orion.AuditingEvents rec


    WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'


    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID


JOIN (


    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc


    FROM Orion.AuditingEvents ae


    WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent


WHERE n.Status = 9


union all


(SELECT


'Muted' as [Status]


,n.caption


,tostring(tolocal(SuppressFrom)) as [From]


,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'


else tostring(tolocal(SuppressUntil )) end AS [Until]


,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'


else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]


,n.DetailsURL AS [_LinkFor_Node]


,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]


, ae.AccountID AS [Account]


FROM Orion.AlertSuppression asup


join orion.nodes n on asup.entityuri=n.uri


join (


    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent


    FROM Orion.AuditingEvents ae


    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')


    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID


join (


    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc


    FROM Orion.AuditingEvents ae


    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')


    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent


--added by WR

where  

    -- Added to remove outdated period of muting 

    tolocal(SuppressUntil) > GETDATE()  

AND

    tolocal(SuppressFrom) < GETDATE()  

    -- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%') 

OR

    --Added to capture those nodes where the suppress until time is not set

    SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

)



ORDER BY [node] asc, [status] desc





-- The following is whats used to search the results, it'll go in the Search SWQL Query pane on the custom query widget


'Unmanaged' as [Status]


,n.Caption AS [Node]


,tostring(tolocal(n.UnManageFrom)) AS [From]


,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then 'Not set'


else tostring(tolocal(n.UnManageUntil)) end AS [Until]


,case when n.UnManageUntil is null or n.UnManageUntil = '9999-01-01 00:00:00' then '-'


else tostring(daydiff(getutcdate(), n.unmanageuntil)) end as [Days Left]


,n.DetailsURL AS [_LinkFor_Node]


,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]


,CASE


WHEN ae.accountID IS NULL THEN 'Audit Log Not Found'


ELSE ae.AccountID


END AS [Account]


FROM


Orion.Nodes n


JOIN (


    SELECT rec.NetObjectID, max(rec.timeloggedutc) as recent


    FROM Orion.AuditingEvents rec


    WHERE rec.auditingactiontype.actiontype = 'Orion.NodeUnmanaged'


    group BY rec.NetObjectID) mostrecent ON mostrecent.NetObjectID = n.NodeID


JOIN (


    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc


    FROM Orion.AuditingEvents ae


    WHERE ae.auditingactiontype.actiontype = 'Orion.NodeUnmanaged') ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent


WHERE n.Status = 9


and (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')


union all


(SELECT


'Muted' as [Status]


,n.caption


,tostring(tolocal(SuppressFrom)) as [From]


,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then 'Not set'


else tostring(tolocal(SuppressUntil )) end AS [Until]


,case when SuppressUntil is null or SuppressUntil = '9999-01-01 00:00:00' then '-'


else tostring(daydiff(getutcdate(), asup.SuppressUntil)) end as [Days Left]


,n.DetailsURL AS [_LinkFor_Node]


,'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]


, ae.AccountID AS [Account]


FROM Orion.AlertSuppression asup


join orion.nodes n on asup.entityuri=n.uri


join (


    SELECT ae.NetObjectID, max(ae.timeloggedutc) as recent


    FROM Orion.AuditingEvents ae


    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')


    group BY ae.netobjectid) mostrecent ON mostrecent.NetObjectID = n.NodeID


join (


    SELECT ae.NetObjectID, ae.AccountID, ae.timeloggedutc


    FROM Orion.AuditingEvents ae


    WHERE ae.auditingactiontype.actiontype in ('Orion.AlertSuppressionChanged','Orion.AlertSuppressionAdded')


    Order BY ae.TimeLoggedUtc desc) ae ON ae.NetObjectID = n.NodeID and ae.timeloggedutc=mostrecent.recent


where (n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%')


)


ORDER BY [node] asc, [status] desc



0 Kudos

Hi wes.reneau​, I have a need to create reports involving Unmanage and Mute too. I reviewed the code you posted, and made my own changes. I have some questions for you on your solution.

First, what version of Orion are you using? We are using 2019.2 HF3 (NPM 12.5). IN our deployment, the table AlertSuppression is empty. We do have an AlertSuppression2 table, which has the "muting" information in it. What's more, our AlertSuppression table has different column headers than does AlertSuppression2 table. The Alert Suppression 2 table has columns which align with your posted solution.

Secondly, the SQL functions tostring, tolocal, daydiff are not available in MS SQL. Are you  using Oracle perhaps?

Lastly, in the where clauses, I noted this basic setup:

WHERE [asup2].[SuppressUntil] > GETUTCDATE()   
  AND [asup2].[SuppressFrom] < GETUTCDATE()

I get the idea. If there was a suppression that SuppressUntil before the current date/time, then that means that the suppression would have expired. And, it seems to me that there is an assumption being made. In the Nodes.UnManageUntil field, for nodes that are unmanaged and have no end date set, the end date is set as "9999-01-01 00:00:00.000" but it seems that this is not the case with the SuppressUntil field. We have several nodes that are muted indefinitely, and one test-node I set to mute until later today. When I run following query, only the result that does not have NULL in the SuppressUntil field is the one that I set an end date for muting. All others that are muted indefinitely have NULL in the field, not 9999-01-01...

So, why are I bringing this up? Because you also posted a revised 'where' clause. I wanted to give you some feedback on it. I appreciate that you posted at the beginning of the thread that you are a Noob. I was in the same place 18 months ago. Some may say I am an advanced Noob, LOL-- not yet a professional. I hope you receive this discouse constructively.

where   
    -- Added to remove outdated period of muting 
    tolocal(SuppressUntil) > GETDATE()  
AND
    tolocal(SuppressFrom) < GETDATE()  
    -- AND (A.DisplayName like '%${SEARCH_STRING}%' or n.caption like '%${SEARCH_STRING}%' or ae.accountid like '%${SEARCH_STRING}%') 
OR
    --Added to capture those nodes where the suppress until time is not set
    SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

Line 9, "SuppressUntil = '9999-01-01 00:00:00'", Is included in Line 3 "tolocal(SuppressUntil) > GETDATE()   " with one exception, if there were a scheduled mute which begins in the future, and has not end date, then it could be included by Line 9 but not Lines 3-5. It seems like that is not the reason why you have those lines. Further, since it appears that the default behavior for indefinite mutes is to use NULL instead of "9999/01..." Then the whole where clause could be condensed down to one of these:

If you want only current mutes:

WHERE SuppressFrom < GETUTCDATE()

This is equivalent to Lines 1-9.

Or, if you want to include mutes scheduled in the future, then simply use:

Nothing would give you all current and future mutes.

Also, I noted that you are converting to local time at many points. If you work with UTC times, then you won't have the convert -- except in your final output. The logic which the table viewer won't see doesn't matter if it is in UTC or local time, but operating in UTC, your query will run faster and be simpler to read. When it comes to the final table others will read, then convert to UTC (or just present in UTC and let the viewer convert in his/her own mind if needed).

Good job. You have done an impressive amount of work. Keep at it, and keep learning -- I can tell you have the mindset for scripting SQL.

0 Kudos

I dialed back my query to focus on muted nodes, specifically those w/o a ending date/time and I've got this.  This does not address the unmanaged nodes, those can be captured w/ a SQL query using the following " Status = '9'"  It doesn't get you the details you'd probably want but it works in a pinch until the custom query is fixed up.

The key was adding the "or" in the where clause.  Forgive my formatting but it makes it easier for me to read.

-- This version suppresses nodes that "suppress untill" time has past.  I'm not sure why they show up

-- despite the time having already expired.  But the WHERE clause prevents them from showing up.

-- Now able to see the nodes that have no expiration date for mutes.  Accomplished this by adding

-- OR SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

-- to the WHERE clause.

SELECT

        -- ONODES is the alias is for Orion.Nodes table

        -- OALERTSUPPRESS is the alias for Orion.AlertSuppression table

        ONODES.caption AS [Device], ONODES.DetailsURL AS [_LinkFor_Device],

        '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Device],

        tostring(tolocal(SuppressFrom)) AS [Supress From],

      

        CASE

            WHEN SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

            THEN 'Not set'

        ELSE

            tostring(tolocal(SuppressUntil )) END AS [Supress Until],

        CASE

            WHEN SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

            THEN '-'

        ELSE

            tostring(daydiff(getutcdate(), OALERTSUPPRESS.SuppressUntil)) END AS [Days Left]

     

FROM

        Orion.AlertSuppression OALERTSUPPRESS

JOIN

        Orion.nodes ONODES ON ONODES.uri = OALERTSUPPRESS.EntityURI

WHERE

        -- The end date (SuppressUntil) is GREATER than todays date (GETDATE)

        -- This hides the nodes that have until dates that occur in the past

        -- apprently this is an issue as these dates dont always purge

        tolocal(SuppressUntil) > GETDATE()

AND

        tolocal(SuppressFrom) < GETDATE()

OR

        SuppressUntil IS NULL OR SuppressUntil = '9999-01-01 00:00:00'

ORDER BY [SuppressUntil] ASC

0 Kudos