SWQL Query for linking Alert Configurations to Alert Actions?

Anyone have a SWQL that will let me query my AlertConfigurations and see the associated trigger actions (and the actions properties?)

Find more posts tagged with

alert_actions

Alerting

Accepted answers

ctech291

This is what we use:

SELECT TOP 1000 
 ac.AlertID
,ac.AlertMessage
,ac.AlertRefID
,ac.Name
,ac.Description
,ac.ObjectType
,ac.Enabled
,ac.Frequency
,ac.Trigger
,ac.Reset
,ac.Severity
,ac.NotifyEnabled
,ac.NotificationSettings
,ac.LastEdit
,ac.CreatedBy
,aa.CategoryType
, aa.ActionAssignmentID
,aa.ActionID
--,aa.ParentID
--,aa.EnvironmentType

--,aa.InstanceType
,aa.Uri
,ap.Action.Enabled
,ap.Action.Title
, ap.PropertyName
, ap.PropertyValue


FROM Orion.AlertConfigurations ac
LEFT OUTER JOIN Orion.ActionsAssignments aa
    ON ac.AlertID = aa.ParentID
LEFT OUTER JOIN Orion.ActionsProperties ap
    ON aa.ActionID = ap.ActionID
where 1=1
    --and ac.Name like '%service is down%' --You can filter on the Alert name
    and ac.Enabled = 'True'
    and ap.PropertyValue like '%Platform%' --You can filter on the property values (such as the json payload) or uri)

All comments

ctech291

This is what we use:

SELECT TOP 1000 
 ac.AlertID
,ac.AlertMessage
,ac.AlertRefID
,ac.Name
,ac.Description
,ac.ObjectType
,ac.Enabled
,ac.Frequency
,ac.Trigger
,ac.Reset
,ac.Severity
,ac.NotifyEnabled
,ac.NotificationSettings
,ac.LastEdit
,ac.CreatedBy
,aa.CategoryType
, aa.ActionAssignmentID
,aa.ActionID
--,aa.ParentID
--,aa.EnvironmentType

--,aa.InstanceType
,aa.Uri
,ap.Action.Enabled
,ap.Action.Title
, ap.PropertyName
, ap.PropertyValue


FROM Orion.AlertConfigurations ac
LEFT OUTER JOIN Orion.ActionsAssignments aa
    ON ac.AlertID = aa.ParentID
LEFT OUTER JOIN Orion.ActionsProperties ap
    ON aa.ActionID = ap.ActionID
where 1=1
    --and ac.Name like '%service is down%' --You can filter on the Alert name
    and ac.Enabled = 'True'
    and ap.PropertyValue like '%Platform%' --You can filter on the property values (such as the json payload) or uri)

jjhj

@ctech291 Thanks for your QUERY!

Based on an SMTP Update, My Query and Update Are as Follows. I hope This Helps Someone!

1. Selection Query

SELECT AC.AlertID      ,AC.Name      ,AC.Enabled      ,AA.ActionID      ,AA.EnvironmentType      ,AA.CategoryType      ,AP.PropertyName      ,AP.PropertyValueFROM [SolarWindsOrion].[dbo].[AlertConfigurations] ACINNER JOIN [SolarWindsOrion].[dbo].[ActionsAssignments] AA ON AC.AlertID = AA.ParentIDINNER JOIN [SolarWindsOrion].[dbo].[ActionsProperties] AP ON AA.ActionID = AP.ActionIDWHERE AC.Enabled = 1 AND AP.PropertyName IN (&#39;EmailFrom&#39;, &#39;Sender&#39;);

Explanation

Retrieves enabled alerts (Enabled = 1).
Selects properties related to email settings (EmailFrom and Sender).

2. Conditional Requirements

If PropertyName = 'Sender'its value must be ${DefaultEmailFrom}.
If PropertyName = 'EmailFrom'its value must be noreply@primient.com.

3. Update Query

UPDATE APSET AP.PropertyValue =     CASE         WHEN AP.PropertyName = &#39;Sender&#39; THEN &#39;${DefaultEmailFrom}&#39;        WHEN AP.PropertyName = &#39;EmailFrom&#39; THEN &#39;noreply@primient.com&#39;    ENDFROM [SolarWindsOrion].[dbo].[ActionsProperties] APINNER JOIN [SolarWindsOrion].[dbo].[ActionsAssignments] AA ON AP.ActionID = AA.ActionIDINNER JOIN [SolarWindsOrion].[dbo].[AlertConfigurations] AC ON AA.ParentID = AC.AlertIDWHERE AC.Enabled = 1 AND AP.PropertyName IN (&#39;Sender&#39;, &#39;EmailFrom&#39;);

Explanation

Updates the Sender property to ${DefaultEmailFrom}.
Updates the EmailFrom property to noreply@primient.com.
Ensures only enabled alerts (Enabled = 1) are modified.