6 Replies Latest reply on Sep 20, 2017 1:52 PM by xtraspecialj

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

    byrona

      I am planning to go through and remove some old custom properties that are no longer in use.  Is there an easy way (SQL Query, etc.) that can be done to look through all of my alerts for the use of any given custom property in the trigger conditions?  This would save me from having to go manually look at every alert I have configured.

       

      Thanks in advance for any suggestions!

        • Re: Quick way to look through all alert trigger definitions for a specific custom property?
          aLTeReGo
          select * from AlertDefinitionsView WHERE TriggerQuery LIKE '%CustomPropertyName%'
          3 of 3 people found this helpful
          • Re: Quick way to look through all alert trigger definitions for a specific custom property?
            byrona

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

            • Re: Quick way to look through all alert trigger definitions for a specific custom property?
              xtraspecialj

              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":
              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.

              1 of 1 people found this helpful
                • Re: Quick way to look through all alert trigger definitions for a specific custom property?
                  wluther

                  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

                    • Re: Quick way to look through all alert trigger definitions for a specific custom property?
                      xtraspecialj

                      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.