Based on Current Muted Alerts (v5) [SAM & NPM] but limited for environments without Server & Application Monitor.
Is there a way to filter out nodes that are in maintenance mode?
For example, a daily report of the current down nodes but exclude the muted nodes. Currently this is done via a custom property to mute the alert and the report is filtered to exclude devices with that custom property equal to True. I don't see an easy way to do this with the new maintenance mode feature so might have to continue with the custom property method.
I don't see why not, but you'd have to change the logic on a few things. Since I don't have an exact "current down" report that you are suggesting, you'd have to give me something to work with to start.
Thanks a lot for this report, it's been added to our daily list to check.
Just started looking at this... the SWQL code works in a web report as well as in the Orion SDK but when I try to use it in a custom query resource it fails with a simple error "There was an error processing the request." which usually means a query error but since it works in the SDK not sure what gives.
Have you seen the same error?
Hi All,
Can you give me some sql queries for Network device muted nodes only like cisco. or do you have any kind of report that contains the mute alert for network devices only?
You can try this SWQL query. I scarfed it from some other part of Thwack (but can't rememeber who to give credit to).
I added the Vendor CIsco in the where line....
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],
THEN [N].[DetailsUrl]
THEN [I].[DetailsUrl]
THEN [AA].[DetailsUrl]
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
THEN [N].[NodeName]
Hi Sothrels,
Thank you for the response.
As I checked it is not working, can you please check it once? I have checked in the report view and also checked the dashboard view but no luck.
Hmmm, I just pasted the above code again into SWQL Studio and it worked here.
What version NPM are you on?
we have latest version NPM 12.2. where have you put the same? do we have any options?
Yep, same version here.Do you have SWQL Studio loaded? If so you can paste the code in there and it will show you where the error is occurring.Also, try to comment out the last line "and N.Vendor = 'Cisco'" and make sure that isn't breaking things.This is a snap of my SWQL with the query pasted in. I do not have any results (nothing muted at the moment) but you can see it ran w/o errors.
Hi,
Sorry, I don't have SWQL Studio. I have added bold text to SQL query but still not getting the response. Can you please check it once?
THEN [N].[NodeName] WHEN [EntityUri] LIKE 'sis://%/Orion/Orion.Node/NodeID=%/Interfaces/InterfacesID=%
THEN [I]. [InterfaceCaption]
WHEN [EntityUri] LIKE 'swis://%/Orion/Orion.Nodes/NodeID=%/Applications/ApplicationID=%
THEN [AA].[Name]
ELSE 'Wrong'
END, '%' ) AND [EntityUri] LIKE CONTAC('%=' , [AE].NetobjectID)
INNER JOIN Orion.AuditingActionTypes AS [AT]
ON [AE].ActionTypeID = [AT].ActionTypeID
WHERE [AT].Actiontype IN ( 'Orion.AlertsuppressionAdded' , 'Orion.AlertSuppressionChanged' )
and N.Vendor = 'Cisco'
--------------------------------------------------------------------------------------------------------------------------------
Full query-:
Hi Sotherls,
Can we do some changes in below query for muted nodes (for networking devices only)
Custom SWQL 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
Enable search
Search SWQL Query:
Enter a custom SWQL query following the example of the query above. To refine your search, include additional WHERE conditions that use ${SEARCH_STRING} as a macro for the string you want to search.
select
n.caption as [Device], n.DetailsURL as [_LinkFor_Device]
where n.caption like '%${SEARCH_STRING}%'
Getting same error
codeassassin - which query were you running? I'm seeing some typos in the queries posted by er.vansh17091 (missing single quotes and typo in a function name).
This is his query, but working:
THEN [N].[NodeName] WHEN [EntityUri] LIKE 'sis://%/Orion/Orion.Node/NodeID=%/Interfaces/InterfacesID=%'
END, '%' ) AND [EntityUri] LIKE CONCAT('%=' , [AE].NetobjectID)
Pasted the query you posted above, and getting the following error:
Ah - for Custom Query widgets, they need a default sort (don't ask me why). Just append:
ORDER BY [Element]
at the end and you should be good.
Full Query:
AND N.Vendor = 'Cisco'
Thanks for your time, but I'm afraid I'm still getting the same error. I'm running the latest recommended release.
Do you have NPM and SAM? This query requires that you have both of them.
Hello KMSigma,
i tried the above query i am getting error, i am using 2019.4 version NPM and NCM
How to stop duplicate rows with users who did not Mute the Alert?
The SolarWinds Orion Monitoring Environment is v2019.4
After muting the Alerts for various Nodes, and Running the Draft Report initially with the Current+Muted+Alerts+for+Network+Devices+(v5).xml script,
Replaced with the latest SWQL Script by KMSigma 31-12-2019 05:16PM (@ BST; GMT +1).
I found that there were either 2 or 3 other Users Listed as Muting the Alerts on the Nodes for the same duration of Time that I had just set against my user name.
With my limited knowledge of SQL and SWQL , I was hoping that the ‘SELECT DISTINCT’ Syntax at the beginning of the Report would prevent duplicate lines and not show lines where we know that user did not Mute the Nodes Alert.
I will look forward to your feedback.
Daniel
This did helped me to modify the source code & get the report as per the requirement.
I'm trying to get the days count between unmanagefrom & current date using DATEDIFF but not working. Can someone help me with the correct SWQL function that i can use to get the result
Try this, it lists start of suppression, end date (if scheduled) and day count. It also converts to local time.
SELECT ONO.Caption, ToLocal (OAS.SuppressFrom) as SuppressStart, ToLocal (OAS.SuppressUntil) as SuppressEnd, DayDiff(OAS.SuppressFrom,GetDate()) AS Muted_DayCountFROM Orion.AlertSuppression OASJOIN Orion.Nodes ONOON OAS.EntityUri = ONO.Uri
Thanks DayDiff() gave me the data.
Do you know which function should i use to get the month count.
