Quick way to look through all alert trigger definitions for a specific custom property?

select * from AlertDefinitionsView WHERE TriggerQuery LIKE '%CustomPropertyName%'

Sweet, thanks aLTeReGo​!  Much easier than I had expected, I was looking at the DB but had overlooked that specific table.

I needed something like this to find how many and which alerts were using some Old Custom Properties of ours.  I created an Orion report out of it, but i have the report too customized for our needs to make it useful to share the entire thing, so instead I'll just supply the SWQL queries I used down below.  To run these queries you have two options (and my apologies if you already know this stuff, but even if you do somebody else reading this might not):

1. Create a Report in the Orion web console, choose "Custom Table", then, in the "Selection Method" drop-down at the top of the custom table's "Add Content" page, also known as the Data Source, choose "Advanced Database Query SQL/SWQL".  Next, make sure the "SWQL" radio button is selected above the empty box below it and paste in the query, as seen in the screenshot below.  Name the source something that makes sense to you and choose "Add To Layout":
   1. 
2. Download the OrionSDK and install it on your local workstation.  The SDK comes with a program called "SWQL Studio", which is a program for writing and editing SWQL queries that you can then use in Custom Table resources or Custom Query resources in the Orion web console or in scripts that you create to interact with the Orion API.  The newest version of the SDK can be found here (I've been using the Beta release and it runs just fine, I recommend grabbing that one): Releases · solarwinds/OrionSDK · GitHub

There are two queries, the first one gives you a count of how many times a property is used in an Alert Message, an Alert Trigger Condition, and an Alert Reset Condition:

SELECT 
cp.Field AS OldProperty
,SUM(CASE 
    WHEN (ac.AlertMessage LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.AlertMessage LIKE '%Nodes.CustomProperties.' + cp.Field + '%') THEN 1
    ELSE 0
    END) AS AlertMessageCount
,SUM(CASE 
    WHEN (ac.Trigger LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.Trigger LIKE '%Nodes.CustomProperties.' + cp.Field + '%') THEN 1
    ELSE 0
    END) AS AlertTriggerCount
,SUM(CASE 
    WHEN (ac.Reset LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.Reset LIKE '%Nodes.CustomProperties.' + cp.Field + '%') THEN 1
    ELSE 0
    END) AS AlertResetCount
FROM 
Orion.AlertConfigurations ac,
Orion.CustomProperty cp 
WHERE
cp.Table = 'NodesCustomProperties'
AND
cp.Field IN ('YourFirstPropertyHere', 'YourSecondPropertyHere', 'YourThirdPropertyHere', 'AndSoOnAndSoForth', 'IThinkYouGetTheIdea', 'PutAsManyAsYouLikeInThisList', 'However', 'TheMoreYouPut', 'TheSlowerThisQueryWillRun')
GROUP BY
cp.Field

The second query is each property with the specific alert its used in.  For each alert there are several columns that tell you if the property was used in the Alert Message, Trigger Condition, Reset Condition and whether or not the Alert is Enabled:

SELECT DISTINCT
ac.Name AS AlertName
,ac.Enabled
,cp.Field AS OldProperty
,CASE 
    WHEN (ac.AlertMessage LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.AlertMessage LIKE '%Nodes.CustomProperties.' + cp.Field + '%') THEN 'TRUE'
    ELSE 'FALSE'
    END AS AlertMessage
,CASE 
    WHEN (ac.Trigger LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.Trigger LIKE '%Nodes.CustomProperties.' + cp.Field + '%') THEN 'TRUE'
    ELSE 'FALSE'
    END AS AlertTrigger
,CASE 
    WHEN (ac.Reset LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.Reset LIKE '%Nodes.CustomProperties.' + cp.Field + '%') THEN 'TRUE'
    ELSE 'FALSE'
    END AS AlertReset
FROM 
Orion.AlertConfigurations ac
JOIN
Orion.CustomProperty cp ON cp.Table = 'NodesCustomProperties' AND (
(ac.AlertMessage LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.AlertMessage LIKE '%Nodes.CustomProperties.' + cp.Field + '%') 
OR 
(ac.Trigger LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.Trigger LIKE '%Nodes.CustomProperties.' + cp.Field + '%') 
OR 
(ac.Reset LIKE '%NodesCustomProperties|' + cp.Field + '%' OR ac.Reset LIKE '%Nodes.CustomProperties.' + cp.Field + '%')
) 
WHERE
cp.Field IN ('YourFirstPropertyHere', 'YourSecondPropertyHere', 'YourThirdPropertyHere', 'AndSoOnAndSoForth', 'IThinkYouGetTheIdea', 'PutAsManyAsYouLikeInThisList', 'However', 'TheMoreYouPut', 'TheSlowerThisQueryWillRun')

The way I've got our report setup is I added two Custom Tables.  The first one on top uses the Count query to give me a nice summary of how many times each property shows up in alerts.  The second table below that uses the other query and it tells me all of the specific alerts that use each one.  I group this second one by the OldProperty column, otherwise the report just looks silly.  See my example on how ours looks here:

***** IMPORTANT NOTE ******

What these queries will not tell you is if any alert actions use these properties.  Since alert actions are technically separate from alerts (one action can even be assigned to multiple alerts now), you would need an entire other query to look for that.  You could of course join the action table to the alert configuration table, but honestly I would keep them separate since, as I said, one action can be assigned to multiple alerts now.  To me it would be more beneficial to keep the queries separate.  I haven't created a query for the actions yet, but it's something I hope to get to eventually.

This is very interesting, it would seem this table(view) pulls data from a table that was used for the old alert manager before moving to the web.  In cases where the alert was changed after migrating to the web based alert manager you may get results showing a custom property existing in an old alert but doesn't exist in it now.

xtraspecialj​ Schweet!! I like it. I had been trying to do that in SQL, with my tiny brain, but never could get it to work the way I wanted it to work.

I reckon I have gone and made this more complicated than it needed to be... figures.

Anyway, this is what I have for finding custom properties required in alerts.

WITH Alerts_CTE (AlertName, TriggerQuery,ResetQuery)
--WITH Alerts_CTE (AlertName,TriggerQuery,TriggerQueryDesign,ResetQuery,ResetQueryDesign,SuppressionQuery,SuppressionQueryDesign)
AS
(
SELECT
AlertName
--,Enabled
,TriggerQuery
--,TriggerQueryDesign
,ResetQuery
--,ResetQueryDesign
--,SuppressionQuery
--,SuppressionQueryDesign

FROM dbo.AlertDefinitionsView

--WHERE
--Enabled=1
)

,CustProps_CTE (TABLE_NAME,COLUMN_NAME)
AS
(
SELECT
TABLE_NAME
,COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE 
TABLE_NAME = 'NodesCustomProperties'
AND
COLUMN_NAME <> 'NodeID'
)

,AlertQuery_CTE (COLUMN_NAME)
AS
(
SELECT
--TABLE_NAME
COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE 
TABLE_NAME = 'AlertDefinitionsView'
AND
COLUMN_NAME LIKE '%query%'
)

SELECT 
AlertName
,TriggerQuery
--,TriggerQueryDesign
,ResetQuery
--,ResetQueryDesign
--,SuppressionQuery
--,SuppressionQueryDesign
,CustProps_CTE.COLUMN_NAME AS 'CustomProperty'
--,AlertQuery_CTE.COLUMN_NAME

FROM Alerts_CTE,CustProps_CTE,AlertQuery_CTE

WHERE
TriggerQuery LIKE '%'+CustProps_CTE.COLUMN_NAME+'%'
OR
ResetQuery LIKE '%'+CustProps_CTE.COLUMN_NAME+'%'

As it is, it seems to find the alerts which require a custom property for the trigger, or reset.

Although, I was wanting it to go through each result of the "AlertQuery_CTE", and apply the LIKE filter, so it would search across all possible locations in the alert.

I will be putting your hard work to use today, "upgrading" my solution, so thank you very much.

Thank you,

-Will

Yeah, I try to never use SQL if I can help it.  It's just a general bad practice.  The only times I use SQL is when I need a function that SWQL doesn't have or if SWQL's performance is too slow (like when querying really large statistics tables and stuff).  SWQL is more secure (it honors account and view limitations) and it won't break should they ever do a Schema change. 

For example, when they modified the DB schema and broke the Nodes table into several separate tables it broke any queries that did an Update, Insert, or Delete from the Nodes table.  It would have broken any SELECT queries from the Nodes table too, but they were nice enough to create a Nodes View that allowed them to still work, but they didn't have to do that and they may not do that in a future schema change, so I just stick with SWQL to be safe.  Plus I like some of the features of SWQL like the relationships between entities and some of their custom functions.

aLTeReGo​

Has the table for the alert definitions changed? It appears the alert definitions are now in the "AlertConfigurations" table.

beastroy  wrote: aLTeReGo Has the table for the alert definitions changed? It appears the alert definitions are now in the "AlertConfigurations" table.

There have been no significant changes since this was posted. The AlertDefinitionsView is still very much used.

aLTeReGo​

Thanks for the response. In our case, in the AlertDefinitionsView table the "TriggerQuery" field is null for all of our alerts. I had to use the following query to successfully find all of the alerts using a custom property:

SELECT *

FROM [AlertConfigurations]

WHERE [Trigger] LIKE '%custom property%'