Is there a best practice for creating Custom Metric / Alerts from configuration-as-code repositories

mgavel

Would like to move all my custom metrics and associated alerts to a code repository so we can track and approve changes. 

Is there a configuration-as-code best practice to follow in SQL Monitor?

Alex B

Hi @mgavel,

We don't have a specific best practice here I'm afraid.  The custom metrics are only supported to be done via the UI, so there isn't a supported way of doing what you are asking.

Unofficially, since we don't support directly querying or modifying the database (take a backup before modifying the repository, the repository schema can change, this is provided as is), you could probably get away with creating the metric initially in the UI and then querying the settings.CustomMetrics table to get the Id and other info and then (after stopping the base monitor service) do an UPDATE statement to the Tsql column to update the T-SQL used by the custom metric with any changes from your repository (and any other columns I suppose).  For the custom alert settings, the settings.CustomAlertDefinitions table links the settings.CustomMetrics table to the config.AlertConfiguration table like this

SELECT cm.Name AS CustomMetric, cad.Name,ac._Configuration
FROM settings.CustomMetrics AS cm
JOIN settings.CustomAlertDefinitions AS cad
ON cm.Id = cad.CustomMetricId
JOIN config.AlertConfiguration AS ac
ON ac._SubType = cad.Id
WHERE ac._AlertType = 40

The _Configuration column is the configuration XML for the alert and you just need to have a look based on different settings you set in the UI and the same thing applies, you should be able to UPDATE it as well.

Then restart the base monitor and the changes should be in effect.

Kind regards,
Alex