Custom metric: alert when value changed
I want to be alerted if someone creates or deletes a database. The metric itself is easy (select count(*) from sys.databases - executed for the master DB on all servers).
But in the alert setting I can only define that it should trigger if the new result is abover or below a set value but not for value changed (compared eigther to the previous value or the "baseline").
So I have to create two metrics / alerts for DB drop (below) and create (above) AND have to define / modify the number of databases as alert threshold for each server / instance separat.
Jeff Lonn commented
Agree, I'd like to have multiple for custom and standard alerts (Long-running query), and also add Include Queries with, currently only have "Exclude". For instance I'd like to exclude BACKUP DATABASE with one set of thresholds, and include BACKUP DATABASE with another set of thresholds.
Thanks for creating the new UV requests - since one of them meets the aims of this discussion, I'll close off this request and transfer the votes across to that thread when this discussion is finished.
Regarding the documentation, I'm not seeing the same as you. For eg, jump to this Custom Metric on the on-line demo:
Click on the question mark at "Use collected or calculated values", and the description includes this text, under "When should I use calculated values?":
“SQL Monitor can calculate the rate for you by finding the difference between each pair of consecutive values and dividing it by the number of seconds between each collection. The result gives the rate of change per second.”
Do you see the same? If not, could you be using an old version of SQL Monitor?
it would be nice to have multiple alerts for a single custom metrics.
- Your application has its own job scheduler. You want to be informed if the duration of a specific job is greater than 1 hour. But you want to be informed too, if the duration was lesser than 1 minute (because then the job may have been failed).
- Custom metric for specific database / server objects (as number of databases or of tables or certificates or logins or members in a specific role or ...). You want to be alerted if the number increases or decreases.
You are right - I could have skipped the multiply by number of seconds. On the other hand I can now see how many DBs were created / dropped (by rounding the value).
A single alert / metric would work, if you would not only me allow to compare the theshold above or below a given value but simply changed by value (regardless if + or -).
And if you would output the current value and the last value in the alert email I would be able to decide if it was a drop or create.
Regarding the built-in-documentation: go to the configure tab | custom metric | create (or edit) and click onto the question mark at "Use collected or calculated values". Neither here nor on the following custom alert page at "Define alert thresholds" I can find a hint that this will refer to the change rate per second if I use calculated values.
I'll open two new user voices for built-in alarms and multipe alerts from one metric. The non-equal-alert is the intent of this user voice request.
You shouldn't need to multiply the calculated value, because you can configure the alert to fire if the calculated value is greater than 0, which should work regardless of the collection frequency.
In terms of your second comment, I can't think of an easy way to have a single custom metric/alert work for both adding and dropping database: potentially the Custom Metrics could write values into a temp table and then return the absolute value of the difference between the last two values, but that's quite hacky.
As to your third observation (that the creation of 2 databases in the time period between collection would only raise one alert), I can't think of a simple way of address this.
One observation here is that the alert contains the value of the metric when the alert is raised: so this value will be higher if more than one database is created/deleted between collections.
[In this situation, you probably would want to multiply the metric by the number of seconds between collections, to make it easy to spot that the alert was associated with more than one database creation event.]
Another thought is that you could create a DDL trigger that wrote to the event log when a database was created, so each database creation event would then trigger a "SQL Server error log entry" alert in SQL Monitor. But this feels like a sledgehammer to crack a nut.
Thanks for triggering this discussion. This has teased out a number of potential problems and/or feature requests:
- built-in alerts for creation or deletion of a database
- allow for more than one alert to be triggered from a single Custom Metric
- the option to alert based on the Custom Metric not being equal to a value, in addition to the current logic of the value passing through thresholds from either above or below each threshold
If any of these sound useful to you, could you please create UserVoice requests so that people can vote for that specific feature.
PS I believe that the built-in documentation (question mark) does state that the calculated value is the rate per-second -- can you tell me where you're looking that you can't see this?
In this case I have to multiply the number of databases by the number of seconds in my refresh interval (e.g. 60 for every minute; 300 for 5 min; 600 for 10 min ...).
But this solution would help me only halfway - it removes the need to customize the alert for every server, but I still need two alerts - one for create and one for drop.
And you'll get only one alert, e.g if you collect the values once per hour and someone creates a db at 9:15 and the another one at 10:15 because the threshold remains at 1 in this case.
PS: you should mention that the threshold is the rate per second between collections in the build-in-documentation (question mark) in the SQL Monitor.
When you create a Custom Metric, you can define whether the metric uses the collected value or the calculated value: the latter is the rate of change (per second) between collections.
I think this should give you what you need?
Matt Laffoon commented
We have a scenario where we would like to create 2 different alerts for the same metric. The scenario is we have created a metric to capture the backup size growth percentage from the day prior. We have need for 2 different alerts. 1) Alert when the percentage is over X percent in a single day 2) Alert when the percentage is over Y percent for n days. The way we have to do this now is create the same metric twice and obviously that causes excessive hits to the databases.
Max - thank you for your feedback.
I don't know if you have been involved with our Early Access Program (EAP), but in v3.0 we have a new feature called Custom Metrics. Within that you can measure a rate of change which may go somewhat towards what you are after. Custom Metrics are metrics you want to add to the system (over and above the defaults) and you do this by entering T-SQL that returns the value you are interested in.
If you are interested, you can access the EAP here http://www.red-gate.com/products/dba/sql-monitor/eap-download
max duncan commented
Alerting on numerical values is somewhat functional, but alerts should be able to fire based on percentage of change. For instance, I have a metric that stays constant when queried. If the number drops by 25%, that shows a problem. This metric is different across my farm so plugging in a value does not work. PLEASE INCLUDE!!!