Now that we can mute alerts (SAM 6.4), we want a resource that shows us all the nodes that are currently muted. Any idea which fields in which tables to query?
I ended up using a query I found in THWACK and modifying it slightly for our needs:
SELECT
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],
THEN [N].[Status]
THEN [I].[Status]
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.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
THEN [N].[NodeName]
THEN [I].[InterfaceCaption]
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%'
AlertSuppression2 table appears to be right.
I am still wondering around, but just found this and thought I would provide this information.
Hi,
I know its old thread, can you please me know how can we filter the mute nodes for Network device or CISCO only?
NPM: 12.2
I have found out how to do it by querying the Solarwinds DB directly using MS Access. The table names and column names are the same as SWQL, you will just have to convert the syntax. If you want to show all muted devices, just delete the bolded.
SELECTdbo_AlertSuppression2.SuppressFrom, dbo_AlertSuppression2.SuppressUntil, Nodes.NodeID, Nodes.IP_Address, Nodes.Caption, Nodes.StatusDescription, Nodes.Vendor, Nodes.MachineType, Nodes.UnManaged, Nodes.UnManageFrom, Nodes.UnManageUntil
FROM dbo_AlertSuppression2, Nodes INNER JOIN dbo_AlertObjects ON Nodes.NodeID = dbo_AlertObjects.RelatedNodeId
WHERE (((dbo_AlertObjects.EntityUri) Like [dbo_AlertSuppression2].[EntityUri]) AND ((Nodes.vendor) Like "*cisco*"))
GROUP BY dbo_AlertSuppression2.SuppressFrom, dbo_AlertSuppression2.SuppressUntil, Nodes.NodeID, Nodes.IP_Address, Nodes.Caption, Nodes.StatusDescription, Nodes.Vendor, Nodes.MachineType, Nodes.UnManaged, Nodes.UnManageFrom, Nodes.UnManageUntil
ORDER BY dbo_AlertSuppression2.SuppressFrom DESC;
