Hi Daniel,
Not pretty, but a workaraound ;) although we would prefer a real resultset with multiple column it's still better than nothing. We might be able to use it in some cases.
Thank's,
/Simon

Hi Simon,

No problem! :). I can see how this restriction would make things difficult in your scenario. There is already a suggestion on uservoice to lift the restriction:

https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/3651307-how-about-a-custom-metric-or-alert-to-return-data-

(and slightly related https://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/3604640-collecting-custom-metrics-for-multiple-databases-a)

This improvement is on our radar, but there are a few engineering challenges in making this work well with SQL Monitor. The best way to speed us up in that direction is to vote on these suggestions!

Daniel

Footnote:
For now, there is a little (not very pretty) hack that can help in some cases: When the number of the mismatching surnames exceeds 0, you can instruct your Custom Metric's SQL script to SELECT a string containing all the surnames that you want to report. SQL Monitor will then report a "Custom Metric Collection Error", displaying your message as an "error message" in the alert details (and in the future, in the alert emails as well).

Daniel, thank you again for your input!

We are already using several Custom Metrics, however their limitation is: "The T-SQL query must return a single numeric scalar or NULL".

We would like to include/attach more information than that in the email, so that the recipient can act on the alert email directly, without the need to create and run a SQL query to get the additional information that he/she needs in order to deal with the alert (the names and employee numbers in my example). Currently all we can email is that for example twelve employees have mismatching surnames, but not which employees.

I hope that describes our whishes clearer..
/Simon

Hi there,

I think the kind of use case that you are describing is covered by our Custom Metric feature, in SQL Monitor 3.0 and newer: It allows you to specify T-SQL queries that can run against any selection of databases in specified intervals. These queries will run independently from any existing alerts, and you can define alerts against the results they return. More info here:

http://www.red-gate.com/supportcenter/Content/SQL_Monitor/help/3.2/SM_AboutCustomMetrics

(By the way, the alert emails sent by Custom Alerts are currently being improved as well)

Does that seem like it will be useful for your scenarios?

Daniel

OK, I see. The main reason why we want to add custom querires is that we could then make alerts that are specific to our application's database.

We could then make more specific application alerts that fire emails when we find a data inconsistency/mismatch/error that is not caught by rules in the database model definition.

Simple example just to try to give an example: let's say that in one part of our database an employee is registered with a different surname than in a table in another part (same employeenumber), since it's populated from another source. We can't have foreign key's to cover that so we would like to run nightly tests that email the list of employees that have this problem. This list could then even be sent directly to non-database persons that could fix it.

That would increase the use and thus value of the SQLMonitor product for us.

Hi there,

Ah, OK, thanks for the clarification. We are not planning to add follow-up queries of this kind to our alert emails; We want to focus on making our alerts, and their emails, as useful out of the box as possible. The good news however is that your example is already covered: The index table will be part of the Index Fragmentation alert email.

Hi Daniel,
No, I mean that if the metric for example returns that too many indexes have excessive writes (say the alert limit is twelve), then I would like to write a query that gets executed and it's resultset attached to the alert email. In this cae, a query listing the names of the twelve indexes (and aditional information that would be nice to have).
Does that make sense?

Hi Anonymous,

Are you referring to the value of a Custom Metric when a Custom Metric alert is sent? If I got that right, the answer is yes: Custom Metric email templates will include the metric's value at the time of the alert, as well as the Custom Alert's description text.

Good news! Will we be able to attach the results of a custom query to the email? That would really be a useful feature for us, and we would be able to move more of our SQLAgent alerts in to SQLMonitor.

I would really like to be able to include the results of a custom query in an alert!!

Thanks for your feedback. B.Dax.

SQL Server error log entry is an example where extra info would be really beneficial. There isn't a whole lot of text in an error log entry and just putting everything also into the email would be much better than just current "error log entry happened" style - one has to react ASAP to some of those errors. Having the info inside the email would help loads.

Thanks Aforz. I have merged this now.

Regards,
Priya

This seems to be same as http://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/2664948-add-more-information-to-alert-email

List of tokens for Subject Line as detailed in my comment in http://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/3324034-cluster-name, PLEASE........

if this is ever implemented please do it with fleability built in so that users can edit/ad to the thing if something in should be missing. In otherwords make it work like custom mertrics which let the user do the defining.

In short SQL MOnitor should do the job of capturing the data in a way that DBA's would want and then make that captured info available (in full) to the user so they can do with it as they need including how to respond.

Thanks for the suggestion

Thanks Ryan and Tim.

This seems like a pretty basic and useful feature. I'd love to see it added to the product.

YES YES OH MY GAWD YES.

This would be soooooo helpful, especially when out and away from my computer