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.
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.
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.
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.
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
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:
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
This is amazing!
Thank you everyone!
My local time is one hour off for some reason. Any idea how I can add 1 hour to the local time.
Apologies but I'm a total SQL noob.
I believe ToLocal() is the local timezone of the Orion Server if I'm not mistaken so you may want to check its timezone. Either way though you could add an hour by doing this to the ToLocal() functions.
AddHour(1, ToLocal([SuppressFrom])) AS [LocalSuppressFrom],
AddHour(1, ToLocal([SuppressUntil])) AS [LocalSuppressUntil],
Thank you very much for that code sum_giais. I appreciate your help. Interestingly enough, daylight saving commences today and it seems to have fixed my issue without using the code. I think it may be a windows error in the time settings I need to update.
Don't know why, but v5 shows me extra user which is not related. For example, I mute one of our nodes. I see myself in the report, that's okay, but also I see other user 🙂 and this user is 100% unrelated to this mute event.
If I run a select query from latest sum_giais post I see only one event as it should be:
I tried to unmute it (report became empty, that's logical) and mute again (saw the same picture with extra user)
Also I have a custom widget with this query:
select
n.caption as [Device], n.DetailsURL as [_LinkFor_Device]
,'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Device]
,n.IP_Address as [IP], n.DetailsURL as [_LinkFor_IP]
, Alerts.SuppressFrom as [Mute from], Alerts.SuppressUntil as [Mute Until]
from Orion.AlertSuppression AlertS
join Orion.nodes n on n.uri = AlertS.EntityURI
and it shows only one user (me)
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.