Monitoring of special variations in SQL Agent, SSIS job execution durations

Question

We have built custom AzMon collectors for IaaS-hosted instances of SQLServer 2014/2016 SQL Agent, SSIS and SSRS. We now have statistical run charting of job, project/package and report execution durations. We want to have these durations ‘learnt’ and trigger alerts when they vary.

Maybe the Anomaly Detection API?

Any thoughts?

Answer 1

@AlexThomasDHHS-0019 Provided you are sending data to Log Analytics workspace, please review the Smart Analytics with in Log Analytics workspace and see if that fits your scenario.

You can create an automated detector for service disruptions based exclusively on an application's trace logs. The detector seeks abnormal sudden increases in the relative amount of error and warning traces in the application.

Two techniques are used to evaluate the service status based on trace logs data:

• Use make-series to convert semi-structured textual trace logs into a metric that represents the ratio between positive and negative trace lines.
• Use series_fit_2lines and series_fit_line to perform advanced step-jump detection using time-series analysis with a 2-line linear regression.

Hope the above information helps, please revert back if you have further queries