cancel
Showing results for 
Search instead for 
Did you mean: 
paul326
Level 7

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Figured it out... was adding the 'where' statement in the wrong place. Added it to the LEFT OUTER statement and all good

where N.Caption NOT LIKE 'TST%'

Paul

m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Great!

m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Are you looking at the tables (and the query) with SWQL Studio or with a traditional SQL tool (SQL Query Analyzer, Solarwinds Database Manager)? You'll only see the Uri column if you're using SWQL Studio, because Orion.Nodes is an entity in SWIS (it's based on the Nodes view in the SolarwindsOrion database, but it's not the same as the view).

0 Kudos
rschroeder
Level 21

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

NICE!

0 Kudos
rajasekar
Level 12

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi m-milligan

pastedImage_0.png

Kindly help me am using your script but it showing the wrong time and wrong account ID.

0 Kudos
mat12
Level 11

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Thanks for sharing your queries.

However, I noticed that the user account ID showing is incorrect if the scenario is:

1. User1 muted node1.

2. User2 Unmute node1.

3. User2 muted node1 again - this time, the account ID column is still showing User1 not User2.

Even if User2 unmute and mute the node multiple times, the User1 is still showing instead of the current user who muted the alert (User2).

is this another bug in mute alert feature?

Thanks in advance!

0 Kudos
Highlighted
conman
Level 7

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

There are limitations with these queries as we are matching the tables on timestamp. We are joining AlertSuppression and AuditingEvents on the minute that they were inserted into the database. There could be a scenario with slow SQL performance where the mute happens at one minute and the event is logged in the next minute in this case the muted entity will not appear in the list as we are unable to match on the timestamp.

----- active muted alerts for nodes ------

SELECT n.caption as NodeCaption, ae.timeloggedutc as AuditTimeLoggedUTC, a.SuppressFrom as AlertSuppressFrom, a.SuppressUntil as AlertSuppressUntil, AE.accountid as Account, ae.auditeventmessage as AuditMessage
  FROM AlertSuppression2 as a
  INNER JOIN Nodes as n on n.nodeid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
  INNER JOIN AuditingEvents as AE on AE.netobjectid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
  where a.entityuri not like '%interfaces%'
  and AE.actiontypeid in (77,79)
  and cast(ae.timeloggedutc as char) = cast(a.SuppressFrom as char)


  ------ active muted alerts for interfaces ------

SELECT i.interfacename as InterfaceName, ae.timeloggedutc as AuditTimeLoggedUTC, a.SuppressFrom as AlertSuppressFrom, a.SuppressUntil as AlertSuppressUntil,
  AE.accountid as Account, ae.auditeventmessage as AuditMessage
  FROM AlertSuppression2 as a
  INNER JOIN Interfaces as i on i.interfaceid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
  INNER JOIN AuditingEvents as AE on AE.netobjectid = cast((REVERSE(LEFT(REVERSE(entityuri), CHARINDEX('=', REVERSE(entityuri)) - 1))) as int)
  where a.entityuri like '%interfaces%'
  and AE.actiontypeid in (77,79)
  and cast(ae.timeloggedutc as char) = cast(a.SuppressFrom as char)

m-milligan
Level 13

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi mat12,

I tested this with my own system and I can't reproduce the behavior at all. I tried having User1 and User2 mute and unmute a node in various combinations (User1 mutes and unmutes, then User2 mutes versus User1 mutes, User2 unmutes and mutes again). The query always correctly showed the ID of the last user to mute the node.

I have made a minor adjustment to the query to ensure that it only looks at actions that muted nodes (not other kinds of Solarwinds entities). The revised query is:

Select N.Caption, Supp.SuppressFrom, Supp.SuppressUntil, AE.TimeLoggedUtc, AE.AccountID   
from Orion.Nodes N  
INNER JOIN Orion.AlertSuppression Supp on Supp.EntityUri = N.Uri  
LEFT OUTER JOIN (  
SELECT NetObjectID, Max(AuditEventID) as [AuditEventID]
FROM Orion.AuditingEvents  
where ActionTypeID in (  
SELECT ActionTypeID  
FROM Orion.AuditingActionTypes  
where ActionType = 'Orion.AlertSuppressionAdded'  
or ActionType = 'Orion.AlertSuppressionChanged'
)
and NetObjectType='N'  
group by NetObjectID  
) AS [LastMuteEvent] on LastMuteEvent.NetObjectID = N.NodeID  
LEFT OUTER JOIN Orion.AuditingEvents AE on AE.AuditEventID = LastMuteEvent.AuditEventID  
ORDER BY N.Caption  

Can you try again with this query and let me know if you still notice the error? Perhaps the old query was picking up mute events for non-nodes.

mat12
Level 11

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Thanks for the help and information conman unfortunately, no results came up with the query provided.

0 Kudos
mat12
Level 11

Re: Report showing user who unmanaged elements and muted alerts

Jump to solution

Hi m-milligan​,

Cool stuff! thank you very much with your updated query as I am able to see a unique results (without duplicates) and no old information showing up.

Very much appreciated! Thanks again!! I just need to find out how can I put a link for node details in the caption for this SWQL.

0 Kudos