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

Current Muted Alerts (v5) [SAM & NPM]

Report.png

Report that shows the Current Muted Alerts and the current audit logs (showing who did the muting).

v1 - original build

v2 - added user who did the muting and Tooltip support

v3 - added local Orion server time in addition to UTC time

v4 - Fixed a 'possible' syntax error with a Join to the Audit Table

v5 - Fixed an issue with duplications occurring when a device is flipped from Muted to Unmuted and back

Tested against Core 2017.1 with NPM 12.1 and SAM 6.4

Created "filtered" reports for Current Muted Alerts for Network Devices (v5) [NPM Only] and Current Muted Alerts for Servers (v5) [SAM Only]

I can only test on a very small segment of my environment, and would appreciate any feedback.

Attachments
Comments

Works without any problems for me, Many thanks.

Orion Platform 2017.1.5200, SAM 6.4.0,  NPM 12.1

This is very nice.  Kudos!

By the way, we support several SolarWinds instances in my firm. Only one of them has SAM installed.  So, we can only runt this report in that server. Sadly, we're not planning to purchase more SAM licenses in the near future.  I imagine that there are other SolarWinds customers in this forum who also do not have SAM deployed in their environments. 

Is there a way to break the SAM dependency so it runs even if you have no add-on modules? Just a thought... 

I've added those two reports to this post.

Nice!  I just noticed the duplicated entry issue for the first time today and you have already fixed it.

Excellent Script.  I've been  using this to display currently muted nodes, however, I ran into an issue that if another users click on "suppress alerts", then it will show a duplicated event, where both userID are displayed.  Is there a way to only select only the current user?

Can you send me a screenshot of what's being reported?  I'll see if I can figure it out.

It's showing a duplicate at the UserID when suppressed the alerts along with a different user.

pastedImage_7.png

Code:

SELECT DISTINCT

CASE

          WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN N.Caption

          WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN AA.FullyQualifiedName

          ELSE 'SomethingElse'

       END AS Node,  '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a' + ToString(n.nodeid) AS [_LinkFor_Node]

, '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node], n.ipaddress as [IP],  AE.AccountID as [ID],

   

       ToLocal(SuppressFrom) AS [Suppress From], ToLocal([SuppressUntil]) AS [Suppress Until],

                    

           n.customproperties.os_type as [Type], n.customproperties.os_environment as [Environment],   n.customproperties.OS_Admin as [Admin], n.customproperties.site_name as [Site], n.customproperties.site_server_room_rak as [Rak], n.customproperties.site_server_room_row as [Row]

FROM Orion.AlertSuppression AS AlertSup

            

LEFT OUTER JOIN Orion.Nodes AS N ON AlertSup.EntityUri = N.Uri

LEFT OUTER JOIN Orion.APM.Application AS AA ON AlertSup.EntityUri = AA.Uri

LEFT OUTER JOIN Orion.AuditingEvents AS AE ON AE.AuditEventMessage LIKE CONCAT('%', CASE

          WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND EntityUri NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN N.NodeName

          WHEN EntityUri LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN AA.Name

          ELSE 'Wrong'

       END, '%') AND [EntityUri] LIKE CONCAT('%=', AE.NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS AT ON AE.ActionTypeID = AT.ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

order by SuppressFrom

Just wow. Thanks KMSigma, I was just thinking how in the world am I going to remember which nodes I muted... just muted some today. Certainly going to put this report to use tomorrow. Great work.

We need an option under the "Group by" on managed nodes page for muted alerts. Or list is under the "Status" category, so we can quickly identify and unmute in bulk. The report is great but when you have many to unmute bulk editing will make it much easier.

pastedImage_1.png

This seems like a great Feature Request to put in the Ideas space for NPM.

Hey KMSigma did you get a chance to see why there are still duplicated mute nodes from different AccountID?

I'm still getting these 1-offs duplicate, but nevertheless, it's a great script.

I did play with it, but I can't recreate the same in my environment, so crafting it isn't so easy.

Yes i put it in the feature request section.

Vote for it

I voted and encouraged by friends and (MVP) family to do the same.

Hey timt​. I'm seeing the same thing you are. I'm seeing 2-3 entries for muted devices. I think what's happening is the query is looking in the AuditingEvents table and returning all events that deal with muting that node...Not the most recent one.

Here's what I'm thinking:

Monday - Joe mutes then later un-mutes a device

Tuesday - Mike mutes then later un-mutes a device

Wednesday - John mutes a device and it's still muted

If we ran the report in that scenario, the report should only show John muting the device. In my case, it's returning results for Joe, Mike and John. For the "Start" time, for all three results, it's showing same start time when John muted the device. Hope this makes sense and help shed some light on the issue.

This is a very nice resource. Is there some way to add the Notes field to the report? The Notes field is used by many to explain the "why" aspect of maintenance. Thanks.

KMSigma , I am getting an issue where if someone suppressed the alert with a scheduled time, then when it triggers at the end of the scheduled time to re-enable the alerts, the information still remains on the mute alert report.

Do you think you can take another look at this?  Thanks much!

Found a workaround

SWQL Query to select only records with latest timestamp

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

     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 n.status = 9

ORDER BY N.Caption ASC, UnManagedWhen DESC 

timt​, I was having the same issue.  I just added a line to filter out those results.  Not perfect because it will ALSO filter out any that were actually muted by the admin, but at least clean enough to use.

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

   ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

AND [AE].AccountID NOT LIKE 'adm'

             THEN [N].[NodeName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

AND [AE].AccountID NOT LIKE 'adm'

Also, fantastic report, KMSigma​!  I had no idea so many nodes were being muted with NO END DATE!  Time to call a meeting.

No problem!  It was a trial to get the first version out the door, but worth it because of the need for everyone (myself included).

I was able to recreate the issue you are experiencing with the report in my lab environment. The problem we were having is when the query was fetching User ID's to tie to the Mute event it was grabbing everyone that has ever muted that Node from the events table. I had to call for some assistance from MrXinu who was able to create a method to filter this out to the most recent user that triggered the Mute. His final touch is below allowing you to get the report minus duplicated users:

SELECT EntityUri, UtcSuppressFrom, UtcSuppressUntil, LocalSuppressFrom, LocalSuppressUntil, Element, DetailsUrl, AE2.AccountID

FROM

(

SELECT DISTINCT [EntityUri],

  [SuppressFrom] AS [UtcSuppressFrom],

  [SuppressUntil] AS [UtcSuppressUntil],

  ToLocal([SuppressFrom]) AS [LocalSuppressFrom],

  ToLocal([SuppressUntil]) AS [LocalSuppressUntil],

  CASE

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

THEN [N].[Caption]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

THEN [I].[FullName]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

THEN [AA].[FullyQualifiedName]

ELSE 'SomethingElse'

  END AS [Element],

  CASE

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

THEN [N].[DetailsUrl]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

THEN [I].[DetailsUrl]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

THEN [AA].[DetailsUrl]

ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'

  END AS [DetailsUrl]

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

  ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

  ON [AlertSup].[EntityUri] = [I].[Uri]

LEFT OUTER JOIN Orion.APM.Application AS [AA]

  ON [AlertSup].[EntityUri] = [AA].[Uri]

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

  ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

THEN [N].[NodeName]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

THEN [I].[InterfaceCaption]

WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

THEN [AA].[Name]

ELSE 'Wrong'

END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS [AT]

  ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

) AS tbl1

JOIN

(

    SELECT NetObjectID, MAX(TimeLoggedUtc) AS MaxTimeLoggedUtc

    FROM Orion.AuditingEvents AS AE

INNER JOIN Orion.AuditingActionTypes AS [AT]

  ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

    GROUP BY NetObjectID

) AS tbl2 ON tbl1.EntityUri LIKE CONCAT('%=', tbl2.NetObjectID)

JOIN Orion.AuditingEvents AS AE2 ON tbl2.NetObjectID = AE2.NetObjectID AND tbl2.MaxTimeLoggedUtc = AE2.TimeLoggedUtc

Hope this helps anyone utilizing the great report that KMSigma created.

Jacob West

Loop1 Systems

LinkedIN

Facebook

Twitter

I added the custom property table to the query in case some of you need to filter out some nodes like I do.

SELECT DISTINCT [EntityUri],

       [SuppressFrom] AS [UtcSuppressFrom],

       [SuppressUntil] AS [UtcSuppressUntil],

       ToLocal([SuppressFrom]) AS [LocalSuppressFrom],

       ToLocal([SuppressUntil]) AS [LocalSuppressUntil],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[Caption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[FullName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[FullyQualifiedName]

          ELSE 'SomethingElse'

       END AS [Element],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[DetailsUrl]

          ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'

       END AS [DetailsUrl],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[Status]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[Status]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[Status]

          ELSE 30

       END AS [Status],

       [AE].AccountID

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

   ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NodesCustomProperties AS [CP]

   ON [N].NodeID = [CP].NodeID

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

   ON [AlertSup].[EntityUri] = [I].[Uri]

LEFT OUTER JOIN Orion.APM.Application AS [AA]

   ON [AlertSup].[EntityUri] = [AA].[Uri]

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

   ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[NodeName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[InterfaceCaption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[Name]

          ELSE 'Wrong'

       END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS [AT]

   ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%' AND [CP].CustomProperty1 LIKE 'CustomProperty1Value'

Isn't this only for Unmanaged Nodes and not Muted Nodes?

Hi Jake,

How can i add this report on my summary dashboard?

You can always create a custom query on a dashboard and then use the following queries:

Muted_Alerts_Resource.png

Custom SWQL Query:

----------------------------------------------------

-- SELECT QUERY

----------------------------------------------------

SELECT DISTINCT

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[Caption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[FullName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[FullyQualifiedName]

          ELSE 'SomethingElse'

       END AS [Element],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[DetailsUrl]

          ELSE 'SomethingElse'

       END AS [_LinkFor_Element],

       [AE].AccountID AS [By],

       ToLocal([SuppressFrom]) AS [Start],     

       ToLocal([SuppressUntil]) AS [End]

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

   ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

   ON [AlertSup].[EntityUri] = [I].[Uri]

LEFT OUTER JOIN Orion.APM.Application AS [AA]

   ON [AlertSup].[EntityUri] = [AA].[Uri]

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

   ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[NodeName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[InterfaceCaption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[Name]

          ELSE 'Wrong'

       END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS [AT]

   ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionType IN  ( 'Orion.AlertSuppressionAdded', 'Orion.AlertSuppressionChanged' )

ORDER BY [SuppressFrom]

Enable Search (Checked)

Search SWQL Query:

SELECT DISTINCT

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[Caption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[FullName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[FullyQualifiedName]

          ELSE 'SomethingElse'

       END AS [Element],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[DetailsUrl]

          ELSE 'SomethingElse'

       END AS [_LinkFor_Element],

       [AE].AccountID AS [By],

       ToLocal([SuppressFrom]) AS [Start],

       ToLocal([SuppressUntil]) AS [End]

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

   ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

   ON [AlertSup].[EntityUri] = [I].[Uri]

LEFT OUTER JOIN Orion.APM.Application AS [AA]

   ON [AlertSup].[EntityUri] = [AA].[Uri]

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

   ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[NodeName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[InterfaceCaption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[Name]

          ELSE 'Wrong'

       END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS [AT]

   ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionType IN  ( 'Orion.AlertSuppressionAdded', 'Orion.AlertSuppressionChanged' )

  AND CASE

         WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

            THEN [N].[Caption]

         WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

            THEN [I].[FullName]

         WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

            THEN [AA].[FullyQualifiedName]

         ELSE 'SomethingElse'

      END LIKE '%${SEARCH_STRING}%'

ORDER BY ToLocal([SuppressFrom])

Thank you - working as expected.

Thank you for teaching me a new thing today about search query.

I like your fix to exclude those past/duplicates, but how would I go about adding back in the Status column?

Thanks

EDIT:  Figured it out with my non *QL skills

Just what I needed! thanks KMSigma

Added SAM CP (and a part of 'Node CP' modification from j_dennis, without the filter)

SELECT DISTINCT [EntityUri],

       [SuppressFrom] AS [UtcSuppressFrom],

       [SuppressUntil] AS [UtcSuppressUntil],

       ToLocal([SuppressFrom]) AS [LocalSuppressFrom],

       ToLocal([SuppressUntil]) AS [LocalSuppressUntil],

       [CP].CustomProperty1 AS [Node CustomProperty1],

         [CP].CustomProperty2 AS [Node CustomProperty2],

       [AACP].CustomProperty1 AS [AppMonitor CustomProperty1],

        [AACP].CustomProperty2 AS [AppMonitor CustomProperty2],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[Caption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[FullName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[FullyQualifiedName]

          ELSE 'SomethingElse'

       END AS [Element],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[DetailsUrl]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[DetailsUrl]

          ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'

       END AS [DetailsUrl],

       CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[Status]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[Status]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[Status]

          ELSE 30

       END AS [Status],

       [AE].AccountID

FROM Orion.AlertSuppression AS [AlertSup]

LEFT OUTER JOIN Orion.Nodes AS [N]

   ON [AlertSup].[EntityUri] = [N].[Uri]

LEFT OUTER JOIN Orion.NodesCustomProperties AS [CP]

   ON [N].NodeID = [CP].NodeID

LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]

   ON [AlertSup].[EntityUri] = [I].[Uri]

LEFT OUTER JOIN Orion.APM.Application AS [AA]

   ON [AlertSup].[EntityUri] = [AA].[Uri]

LEFT OUTER JOIN Orion.APM.ApplicationCustomProperties AS [AACP]

   ON [AA].[ApplicationID] = [AACP].[ApplicationID]

LEFT OUTER JOIN Orion.AuditingEvents AS [AE]

   ON [AE].AuditEventMessage LIKE CONCAT('%', CASE

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'

             THEN [N].[NodeName]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'

             THEN [I].[InterfaceCaption]

          WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'

             THEN [AA].[Name]

          ELSE 'Wrong'

       END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)

INNER JOIN Orion.AuditingActionTypes AS [AT]

   ON [AE].ActionTypeID = [AT].ActionTypeID

WHERE [AT].ActionTypeDisplayName LIKE '%mute%'

Whenever I try and add this script into the report editor is tells me the query is invalid.  Any advice?

Orion Platform 2018.2 HF6, IPAM 4.7.0, VNQM 4.5.0, NPM 12.3, DPAIM 11.1.0, PM 2.1, NTA 4.2.3, VMAN 8.3.0, SAM 6.7.0, NetPath 1.1.3

Updated this comment 9.19.2020 to include improvements on the queries.

This is all some very nice work. Just wanted to add some clarity here as there's two outstanding issues I've seen. Both of which have actually been addressed here and there through the thread.

1- Query from report returns some errors
2- Query returning too many records where multiple users mute nodes
3- Query including null elements (this is due to deleted elements where audit logs are still present)
4- Original queries included icons

I happened to stumble upon the 2nd one and the query from @jacob.west did solve it, however was missing the _LinkFor_Element and a few other parts from the original.

All in all I figured I'd share the finished product here for other folks benefit as this is an INCREDIBLY useful query no doubt. Eventually I'll probably create an alert that will trigger if something has been muted for too long that would use some of this...

Just remove what you don't need from the top SELECT statement of each section depending on your need.

 

 

----------------------------------------------------
-- SELECT QUERY
----------------------------------------------------


SELECT DISTINCT [Element], [_LinkFor_Element], '/Orion/images/StatusIcons/Small-' + [_IconFor_Element] AS [_IconFor_Element], [AE2].[AccountID] AS [By], [UtcSuppressFrom] AS [From (UTC)], [LocalSuppressFrom] AS [From (Local)], [UtcSuppressUntil] AS [End (UTC)], [LocalSuppressUntil] AS [End (Local)]
FROM
(
SELECT DISTINCT [EntityUri],
  [SuppressFrom] AS [UtcSuppressFrom],
  [SuppressUntil] AS [UtcSuppressUntil],
  ToLocal([SuppressFrom]) AS [LocalSuppressFrom],
  ToLocal([SuppressUntil]) AS [LocalSuppressUntil],
CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[Caption]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[FullName]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[FullyQualifiedName]
    ELSE 'SomethingElse'
END AS [Element],
CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[StatusLED]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[StatusLED]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[StatusLED]
    ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'
END AS [_IconFor_Element],
CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[DetailsUrl]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[DetailsUrl]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[DetailsUrl]
    ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'
END AS [_LinkFor_Element]
FROM Orion.AlertSuppression AS [AlertSup]
LEFT OUTER JOIN Orion.Nodes AS [N]
  ON [AlertSup].[EntityUri] = [N].[Uri]
LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]
  ON [AlertSup].[EntityUri] = [I].[Uri]
LEFT OUTER JOIN Orion.APM.Application AS [AA]
  ON [AlertSup].[EntityUri] = [AA].[Uri]
LEFT OUTER JOIN Orion.AuditingEvents AS [AE]
  ON [AE].AuditEventMessage LIKE CONCAT('%', CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[NodeName]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[InterfaceCaption]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[Name]
    ELSE 'Wrong'
END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)
INNER JOIN Orion.AuditingActionTypes AS [AT]
  ON [AE].ActionTypeID = [AT].ActionTypeID
WHERE ([AT].ActionType IN  ( 'Orion.AlertSuppressionAdded', 'Orion.AlertSuppressionChanged' ) AND [AT].ActionTypeDisplayName LIKE '%mute%')
) AS [T1]
INNER JOIN
(
    SELECT NetObjectID, MAX(TimeLoggedUtc) AS MaxTimeLoggedUtc
    FROM Orion.AuditingEvents AS [AE]
INNER JOIN Orion.AuditingActionTypes AS [AT]
  ON [AE].ActionTypeID = [AT].ActionTypeID
WHERE [AT].ActionTypeDisplayName LIKE '%mute%'
    GROUP BY NetObjectID
) AS [T2] ON [T1].EntityUri LIKE CONCAT('%=', [T2].NetObjectID)
INNER JOIN Orion.AuditingEvents AS [AE2] ON [T2].NetObjectID = [AE2].NetObjectID AND [T2].MaxTimeLoggedUtc = [AE2].TimeLoggedUtc
WHERE [Element] IS NOT NULL
ORDER BY [UtcSuppressFrom] DESC


----------------------------------------------------
-- SEARCH QUERY
----------------------------------------------------


SELECT DISTINCT [Element], [_LinkFor_Element], '/Orion/images/StatusIcons/Small-' + [_IconFor_Element] AS [_IconFor_Element], [AE2].[AccountID] AS [By], [UtcSuppressFrom] AS [From (UTC)], [LocalSuppressFrom] AS [From (Local)], [UtcSuppressUntil] AS [End (UTC)], [LocalSuppressUntil] AS [End (Local)]
FROM
(
SELECT DISTINCT [EntityUri],
  [SuppressFrom] AS [UtcSuppressFrom],
  [SuppressUntil] AS [UtcSuppressUntil],
  ToLocal([SuppressFrom]) AS [LocalSuppressFrom],
  ToLocal([SuppressUntil]) AS [LocalSuppressUntil],
CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[Caption]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[FullName]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[FullyQualifiedName]
    ELSE 'SomethingElse'
END AS [Element],
CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[StatusLED]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[StatusLED]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[StatusLED]
    ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'
END AS [_IconFor_Element],
CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[DetailsUrl]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[DetailsUrl]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[DetailsUrl]
    ELSE '<a href="https://thwack.solarwinds.com/Orion/SummaryView.aspx">SomethingElse</a>'
END AS [_LinkFor_Element]
FROM Orion.AlertSuppression AS [AlertSup]
LEFT OUTER JOIN Orion.Nodes AS [N]
  ON [AlertSup].[EntityUri] = [N].[Uri]
LEFT OUTER JOIN Orion.NPM.Interfaces AS [I]
  ON [AlertSup].[EntityUri] = [I].[Uri]
LEFT OUTER JOIN Orion.APM.Application AS [AA]
  ON [AlertSup].[EntityUri] = [AA].[Uri]
LEFT OUTER JOIN Orion.AuditingEvents AS [AE]
  ON [AE].AuditEventMessage LIKE CONCAT('%', CASE
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%' AND [EntityUri] NOT LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/%'
    THEN [N].[NodeName]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Interfaces/InterfaceID=%'
    THEN [I].[InterfaceCaption]
    WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%'
    THEN [AA].[Name]
    ELSE 'Wrong'
END, '%') AND [EntityUri] LIKE CONCAT('%=', [AE].NetObjectID)
INNER JOIN Orion.AuditingActionTypes AS [AT]
  ON [AE].ActionTypeID = [AT].ActionTypeID
WHERE ([AT].ActionType IN  ( 'Orion.AlertSuppressionAdded', 'Orion.AlertSuppressionChanged' ) AND [AT].ActionTypeDisplayName LIKE '%mute%')
) AS [T1]
INNER JOIN
(
    SELECT NetObjectID, MAX(TimeLoggedUtc) AS MaxTimeLoggedUtc
    FROM Orion.AuditingEvents AS [AE]
INNER JOIN Orion.AuditingActionTypes AS [AT]
  ON [AE].ActionTypeID = [AT].ActionTypeID
WHERE [AT].ActionTypeDisplayName LIKE '%mute%'
    GROUP BY NetObjectID
) AS [T2] ON [T1].EntityUri LIKE CONCAT('%=', [T2].NetObjectID)
INNER JOIN Orion.AuditingEvents AS [AE2] ON [T2].NetObjectID = [AE2].NetObjectID AND [T2].MaxTimeLoggedUtc = [AE2].TimeLoggedUtc
WHERE ( [Element] IS NOT NULL AND [Element] LIKE '%${SEARCH_STRING}%' )
ORDER BY [UtcSuppressFrom] DESC

 

 

 

Version history
Revision #:
1 of 1
Last update:
‎03-17-2017 02:33 PM
Updated by: