Custom SQL Alert Help!!

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%'

)

Appreciate the response Richard, however I'm having trouble following your query.

I was able to craft a query using the union operator. Not sure if it's the optimal query for my purposes, but it seems to work.

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

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?

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