Seems so trivial, but I could do figure out How To Trigger An Alert (Advanced) When Custom Property Has Been Changed
Any help is much appreciated
--
Alex
Solution:
WHERE (AuditingEvents.AuditEventMessage LIKE '%changed custom property%') AND (DATEDIFF(minute, AuditingEvents.TimeLoggedUtc, GETUTCDATE()) < 5)
WHERE
(AuditingEvents.AuditEventMessage LIKE '%changed custom property%') AND
(DATEDIFF(minute, AuditingEvents.TimeLoggedUtc, GETUTCDATE()) < 5)
Type of Property to Monitor: Auditing Events ???
This is very very strange. From the trigger parameters it is quite obvious that this should trigger an alert, but it doesn't. Am I missing something? avn, would you please be able to confirm that the above does work in your environment?
OK, I did some tests - apparently this WILL trigger and alert if you will change custom property ITSELF (i.e. description, etc). However, sorry for not making it clear, I would like to receive an alert when VALUE of the custom property has been changed.
Also, I do not want this alert to sit in "Alerts". I would like to only trigger an email and clear off alert straight away. If someone can help how to achieve this - it would be very much appreciated
Thanks,
OK, I am answering first part of my own question. Below - is how to trigger an alert when value of the custom property changes.
The other question still remains:
How do I clear off alert straight away as soon as it has been triggered?
I see in this case only the following option: play around with time.
Script looks record over the past five minutes.
Type of Property to Monitor: Custom SQL Alert
Set up yuor Trigger Query: Auditing Events
last part of the script:
AuditingActionTypes.ActionTypeID in (24,25,26,27,29)
AND (DATEDIFF(minute, AuditingEvents.TimeLoggedUtc, GETUTCDATE()) < 5)
Looks like you have already figured this out but I will add what I have done as it might work better for some like me that are not as SQL statement literate...
In my case, I have developed what I call an Information Alert Notification as opposed to a Warning or Critical Alert Notification. To do this, I set both the Trigger and Reset Conditions to be exactly the same. I set a 0 second timer on the Trigger Conditions and a 90 second timer on the Reset Conditions. I leave the Alert Rule with no Trigger Actions configured and only Reset Actions. This way the alert triggers, no actions are taken and 90 seconds later the Reset Actions fire an Information Alert Notification to team as well as logging the notification went out to the team.
I use this mostly for delivering information to the team that backups or DB integrity checks were successful. (Non-successful jobs are delivered through Warning or Critical Alert Notifications.) Because the team would like to see these the next business day after the job is run, I also set the Time of Day to be Monday thru Friday with a one minute window in the morning. This causes these notifications to get delivered at a specific time each day. Because none of these require any urgent attention, hence the "Informational" status, delivering them only once a day is sufficient and therefore this design works for this purpose.