Building on this original post and the great work by @m-milligan I added some. I used it to add a custom query resource to a customer summary page. This shows devices in maintenance mode, suppressed from/to, the latest note, and the timestamp that note was added.

It took some work to get only the latest note to show up (the MAX timestamp area).

SELECT

N.Caption as [Node],

SuppressFrom,

SuppressUntil,

NN.Note as [Note],

NN.TimeStamp,

n.DetailsURL AS [_LinkFor_Node],

'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node]

FROM

(select Nodeid, MAX(Timestamp) as "Timestamp"

from Orion.NodeNotes

group by Nodeid) X

JOIN Orion.NodeNotes NN on X.Nodeid=NN.NodeID

and NN.TimeStamp=X.Timestamp

JOIN Orion.Nodes N

on N.Nodeid=NN.NodeID

JOIN Orion.AlertSuppression oa

on oa.EntityUri=N.Uri

where EntityUri not like '%/Interfaces%'

order by Timestamp desc

Hope this helps somebody. It was tough to be sure.

I may make this an original post afterwards due to how much has changed from OPs post.

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:

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)

This is excellent stuff, especially since I'm a n00b with SQL queries. I've been trying to add line to exclude specific N.Caption names to exclude nodes we don't want reported (items to manage but not alert, 100% of the time muted)

This query gets me exactly what I need except excluding caption name that contains 'TST' Any help appreciated.

Those are nodes that have never been unmanaged before. TimeLoggedUTC and AccountID come from Orion.AuditingEvents. If the node hasn't ever been unmanaged, it won't have a record in Orion.AuditingEvents.

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.
Learn more today by joining now.