All Alerts Inventory SWQL

WinterSoldier

I am working on an extract of all enabled alerts configured in my environment that should include trigger conditions, reset conditions, trigger actions, reset actions, Email recipients and object type.

Can anyone help me writing the swql query for the above requirement please?

Currently I have the following query but it doesn't contain all the columns that I need.

SELECT
ac.Name,
CASE WHEN ac.Severity = 2 THEN 'Critical' WHEN ac.Severity = 1 THEN 'Warning' END AS Severity,
ac.ObjectType,
a.Title,
ap.PropertyValue AS EmailTo,
ap1.PropertyValue AS EmailCC,
ap2.PropertyValue AS EmailBCC,
CategoryType
FROM
Orion.Actions a
INNER JOIN Orion.ActionsProperties ap ON ap.ActionID = a.ActionID
AND ap.PropertyName = 'EmailTo'
INNER JOIN Orion.ActionsProperties ap1 ON ap1.ActionID = a.ActionID
AND ap1.PropertyName = 'EmailCC'
INNER JOIN Orion.ActionsProperties ap2 ON ap2.ActionID = a.ActionID
AND ap2.PropertyName = 'EmailBCC'
INNER JOIN Orion.ActionsAssignments aa ON aa.ActionID = a.ActionID
INNER JOIN Orion.AlertConfigurations ac ON aa.ParentID = ac.AlertID
WHERE
CategoryType = 'trigger'--, 'Reset')
AND ActionTypeID = 'Email'
AND ac.Enabled = 1
ORDER BY
ac.Name

Lofstrand

Hi

Not sure it will look that good if you add trigger- and reset condition. They are in xml. A simple node down trigger looks like this:

(Added field is "ac.trigger")

<ArrayOfAlertConditionShelve xmlns="http://schemas.datacontract.org/2004/07/SolarWinds.Orion.Core.Models.Alerting" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><AlertConditionShelve><AndThenTimeInterval i:nil="true"/><ChainType>Trigger</ChainType><ConditionTypeID>Core.Dynamic</ConditionTypeID><Configuration><AlertConditionDynamic xmlns="http://schemas.datacontract.org/2004/07/SolarWinds.Orion.Core.Alerting.Plugins.Conditions.Dynamic" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><ExprTree xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Orion.Core.Models.Alerting"><a:Child><a:Expr><a:Child><a:Expr><a:Child/><a:NodeType>Field</a:NodeType><a:Value>Orion.Nodes|Status</a:Value></a:Expr><a:Expr><a:Child/><a:NodeType>Constant</a:NodeType><a:Value>2</a:Value></a:Expr></a:Child><a:NodeType>Operator</a:NodeType><a:Value>=</a:Value></a:Expr></a:Child><a:NodeType>Operator</a:NodeType><a:Value>AND</a:Value></ExprTree><Scope i:nil="true" xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Orion.Core.Models.Alerting"/><TimeWindow i:nil="true"/></AlertConditionDynamic></Configuration><ConjunctionOperator>None</ConjunctionOperator><IsInvertedMinCountThreshold>false</IsInvertedMinCountThreshold><NetObjectsMinCountThreshold i:nil="true"/><ObjectType>Node</ObjectType><SustainTime i:nil="true"/></AlertConditionShelve></ArrayOfAlertConditionShelve>

Adding action types and names can be done. You will then get one row for each action, if there are several actions on one alert. Is that ok?