5 Replies Latest reply on Feb 25, 2016 3:43 PM by njoylif

    Custom SQL Alert Help!!

    dgillette

      I want to use this SQL query in an alert but I can't seem to find a way to make it work. It seems like I am limited to querying only certain tables in the Orion DB because I can't free form the entire query. The SELECT and FROM part of the query is hardcoded and determined by whatever is selected in the "set up your SQL condition" list. I don't see a selection in the list that returns the table I want to query. Is it not possible to use this SQL query in an alert?

       

      SELECT [Message]

      FROM [dbo].APM_SqlBbServerErrorLog_Detail]

      WHERE [Message] LIKE '%untrusted%'

        • Re: Custom SQL Alert Help!!
          RichardLetts

          look at creating a custom sql alert on a node...

           

          your 'where' clause will now look something like:

           

          trigger:

          where NodeID in (select nodeid from

          FROM [dbo].APM_SqlBbServerErrorLog_Detail]

          WHERE [Message] LIKE '%untrusted%'

          )

           

          reset :

          where NodeID NOT in (select nodeid from

          FROM [dbo].APM_SqlBbServerErrorLog_Detail]

          WHERE [Message] LIKE '%untrusted%'

          )

          • Re: Custom SQL Alert Help!!
            njoylif

            I think what RichardLetts was getting at and the thing to understand for alert queries is that you MUST tie the criteria to an object that the alerting engine knows about -> i.e. one of the "allowed" tables from drop down.

            in your case...

            you have to tie in the applicationID b/c that is one of only 3 columns in that table.

            so...

            set up an application alert (play with it to see if appInsight or regular app gives better results)...

            </hard coded>

            JOIN APM_SqlBbServerErrorLog_Detail el WITH(NOLOCK) ON (APM_AlertsAndReportsData.ApplicationID=el.ApplicationID)

            where el.message like '%untrusted%'

            -- and further criteria as needed

            1 of 1 people found this helpful
              • Re: Custom SQL Alert Help!!
                dgillette

                I got the trigger condition configured and summary is showing "this alert would be immediately triggered on 8 objects", which is what I'm expecting, however the trigger action is not executing, i.e. I'm not getting an email. Any idea why this might be happening?

              • Re: Custom SQL Alert Help!!
                njoylif

                no, but if you go back into the trigger action and select simulate for the email action, change object to one that would alert and execute, you should get email and that will tell you if that is working.

                if not, change the to: to be just your email address and simulate again.

                try simulate -> simulate as well to see if macro translations occuring.

                open ticket if that continues to fail.

                 

                make sure the alert is enabled too