How to create a custom metric that monitors specific CHANGES
HugoKornelis
Posts: 40 Bronze 5
All of the custom metrics I have seen so far use a query on the SQL Server DMOs (dynamic management objects) to get a measurement value. That is great, but it excludes some categories of monitoring that I would like to create my own metrics for.
For example, let's say that I would like to have a custom metric that alerts me when someone has removed a SQL Agent job. There is no way to get that from just looking at the DMOs. I need to query msdb.dbo.sysjobs, and then compare it to the results of an earlier execution of the same query to identify jobs that have been removed.
So basically, what I am asking is if there is a supported way within a custom metric to store the results of a query and then later compare query results to the stored results.
(Ideally with a choice of two options - either always replace the results after comparison, so the deleted job would be flagged once and then clear automatically next time the metric runs; or keep the results unchanged until someone actively decides to clear the results, so that same removed job would continue to be flagged until an admin updates the list of jobs in the tool)
For example, let's say that I would like to have a custom metric that alerts me when someone has removed a SQL Agent job. There is no way to get that from just looking at the DMOs. I need to query msdb.dbo.sysjobs, and then compare it to the results of an earlier execution of the same query to identify jobs that have been removed.
So basically, what I am asking is if there is a supported way within a custom metric to store the results of a query and then later compare query results to the stored results.
(Ideally with a choice of two options - either always replace the results after comparison, so the deleted job would be flagged once and then clear automatically next time the metric runs; or keep the results unchanged until someone actively decides to clear the results, so that same removed job would continue to be flagged until an admin updates the list of jobs in the tool)
--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)
Tagged:
Best Answer
-
Alex B
Posts: 1,158 Diamond 4
Hi @HugoKornelis,
This is more of an auditing type functionality rather than a performance issue which is more what SQL Monitor is geared towards doing.
SQL Monitor lets you set up the SQL for it to run at the specified intervals and have it return a value. This value is then stored and you can alert either based on the value or the rate of change of the value, but it won't let you return a set of results (i.e. a list of jobs) and if that is different send an alert.
To get a semblance of what you are looking to do you could query something like the following every x unit of time and alert on the rate of change:SELECT COUNT(*) FROM msdb.dbo.sysjobs;
Which would produce, for example, 5, 5, 4,4... and the rate of change would be below 0
This would indicate that someone has deleted a job, but you wouldn't know which job.
Currently that's probably as close as you'll get to the functionality you are looking for.
Kind regards,
Alex
