Custom Query to list muted and unmanaged nodes with details

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

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

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

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.