Report to show configured alerts with trigger actions for specific customer nodes.

I'll try to spend some time on this tomorrow; what version are you running?  in 12.0.x, there is a resource available that shows you what alerts could be triggered by that object...at least you could spot check until we get the report going.

We are running 12.0, sorry for the delay long weekend... Thanks for taking a look at this!

Here is what I have so far, not sure why but the JOIN doesn't seem to be working.

SELECT Name, ac.Description, ObjectType, ac.Enabled

, Frequency, Severity, NotifyEnabled

, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, LastEdit), DATENAME(TzOffset, SYSDATETIMEOFFSET())))  AS LastEdit

, CreatedBy, Category

  FROM AlertConfigurations AS ac

  JOIN ActionsAssignments AS aa

  ON (aa.ParentId = ac.AlertID)

  JOIN Actions AS a

  ON (a.ActionID = aa.ActionID)

  WHERE Name LIKE '%Customer Name%'

  Order By Name

I'm pretty sure the key here is using the ActionsAssignments table to link the AlertConfigurations and Actions tables to get this report. I seem to be missing something and am in a little under my head as to what that is...

Thoughts anyone?

hmmm...

Try starting with this query,

select *

from Actions a

WHERE ActionID IN

    (SELECT ActionID FROM ActionsAssignments where ParentID IN (select AlertID from AlertConfigurations where Name like '%Customer Name%'))

If I remember correctly the alert objects table is going to be historical, as in objects only get an entry there if they have already had each alert.  This won't help you with listing out all the alerts that an object could fire but havent yet.  Ive danced with this problem a few times and there doesn't seem to be a handy way to generate this list.  The Node Details resource called All alerts this object can fire seems to be making a call to a function in the API but I have not been able to figure out how to generate that same result from a script yet.  The actual alert configurations arent likely to help you because their format for defining triggers is pretty difficult to parse into something you could show a customer.

Sorry I didnt bring good news for you, but figuring out that API call would be the only viable solution from what I can tell. Maybe mrxinu​ would have a thought?

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

I know this is an old post, but does anyone have an update?

While the resource on the node pages is great to show what alerts can be triggered, having a report is a much better way to look at these in bulk.

I'm also interested in what field I can use to poll such information

The asset 'All Alerts this Object can trigger' will only display alerts configured against 'nodes'.  No SWQL/SQL/Interface/Etc. alerts will populate there.  It causes some confusion for my new users.

SELECT Name, ac.Description, ObjectType
-- , ac.Enabled, Frequency, Severity, NotifyEnabled
, CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, LastEdit), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LastEdit
, aa.actionid, a.Title
, ap.propertyvalue
, CreatedBy, Category

FROM AlertConfigurations AS ac
JOIN ActionsAssignments AS aa ON (aa.ParentId = ac.AlertID) and (aa.EnvironmentType = 'Alerting')
JOIN Actions AS a ON (a.ActionID = aa.ActionID)
JOIN ActionsProperties AS AP ON (aP.ActionID = a.ActionID) and (aP.PropertyName = 'Message')