This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Current Muted Alerts (v5) [SAM & NPM]

KMSigma.SWI over 7 years ago

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.

Top Replies

Parents

0 jacob.west over 6 years ago

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="'">thwack.solarwinds.com/.../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

Facebook

Twitter

Reply