Monthly Archives: June 2020

SCOM 2019: Updated SQL Management Packs for SSRS and SSAS v7.0.22.0

Updated management packs for SSRS and SSAS have been released, they are available at the below links and support versions from 2012 through to 2019 and now include Power BI Report Server:

SQL Server Analysis Services

SQL Server Reporting Services

SQL Server Analysis Services MP Changelog

What’s New

  • Added support for SQL Server Analysis Services 2012, 2014, and 2016 in addition to previously supported 2017 and up
  • Added Start/Stop Analysis Services Windows Service tasks
  • Implemented Database Status monitor 
  • Updated display strings

SQL Server Reporting Services MP Changelog

What’s New

  • Added support for monitoring SQL Server Reporting Services 2012, 2014, and 2016 in addition to 2017 and up
  • Added Start/Stop Reporting Services Windows Service tasks
  • Updated Event Log Collection Target Management Server Discovery to make it use default SCOM action profile instead of SQL MP Discovery run as profile
  • Updated display name of SSRS Deployment object to display AG name as part of it instead of GUID
  • Updated logic of installation detection for local Reporting Services instance to query Windows Registry instead of WMI
  • Improved error handling for cases when error “Process with an Id is not running” is returned
  • Updated display strings

Bug Fixes

  • Fixed false alerting with status code 400 in monitor Report Manager Accessible for SSRS 2016 and PBIRS
  • Fixed alert parameter replacement failure in monitor Report Manager Accessible
  • Fixed issue with accessing DBConnectionString property of PBIRS
  • Fixed NullReferenceException error when Report Server portal being configured to have several ports

Scheduling reports using your Azure Monitor data

It’s quite a common ask, as to how to take the data in an Azure Monitor Logs workspace and create a report that can then be scheduled. Lets take a look at how we can achieve that.

Immediately we are talking about automation when we use the word schedule and Azure has several tools which we can use. The best fit in this case is Logic Apps.

In this example we will create a report showing if any agents haven’t had a successful heartbeat in the past 24 hours.

Navigate to the Logic Apps blade in your Azure Portal and click +Add

Populate the fields selecting my subscription and resource group, creating a new RG if necessary. Then I’ll give my logic app a name and choose my Azure region and click Review + Create and then Create

Once the deployment is complete I can navigate to the resource and it automatically opens the Logic Apps Designer. Now every Logic App needs to start with a trigger and because I want to run a schedule I am going to use Recurrence

I want this on a daily basis so I’m going to enter 24 Hour as my parameters and then click new step

Search for Run Query and Visualize Results as this option will allow a KQL query as a parameter and the results can be manipulated in a variety of ways. Make sure to select the one for Azure Monitor Logs and not for Azure Data Explorer.

You will need to sign in to create a connection with Azure Monitor Logs. Now populate the fields choosing the subscription, resource group and workspace that contains the data you want to use in your report. Put your query in the relevant section and choose chart type HTML.

Heartbeat
| project TimeGenerated, Computer
| where TimeGenerated < now()
| summarize ["Last Heartbeat"]=max(TimeGenerated) by Computer
| where ["Last Heartbeat"] < ago(24h)

The last thing that needs to be done is connect the logic app to a step to send email, click New Step, search for send an email, I’ll be using Office365 but you can use other providers. Select Send an email v2 and sign in to create the connection.

Populate as below making sure to include the attachments

Click save and we’re all good to go. Now test by clicking Run.

You should receive an email with an attachment and voila opening it will have a nice html table with our query results.

These steps can be easily replicated and amended to be used for any number of handy reports using your Azure Monitor Logs data.