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

Current Muted Alerts for Network Devices (v5) [NPM Only]

Report_MutedAlertsNPMOnly.png

Based on Current Muted Alerts (v5) [SAM & NPM] but limited for environments without Server & Application Monitor.

Attachments
Comments

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], 

       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] 

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.

pastedImage_0.png

pastedImage_1.png

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?

pastedImage_1.png

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.


pastedImage_0.png

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?

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

             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-:

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 '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'

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]

,'/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

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:

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 '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 CONCAT('%=' , [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'

Pasted the query you posted above, and getting the following error:

pastedImage_0.png

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:

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 '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 CONCAT('%=' , [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'

ORDER BY [Element]

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

sw2174873_0-1587754772389.png

 

Hi,

 

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

Version history
Revision #:
1 of 1
Last update:
‎04-06-2017 11:25 AM
Updated by: