Report Fragment–Visual Studio Authoring Extensions

Developing reports in SCOM is quite a bit different than developing any type of monitoring workflow. You really need to ramp up your skills on a couple different tools and languages to become a good report developer.

In this post, I will cover a typical VSAE fragment that provides for deploying the report and stored procedure files – of course, the report files are deployed to the report server and the stored procedure is installed on the data warehouse.

This post covers the fragment essentials – it does not get into report or stored procedure development. It is intended to be a quick reference for those developers out there to quickly plug in the necessary elements to push the rdl and sql resource files in their management pack.

At the end, I will provide some essential elements that need to be included in your sql file that will satisfy "install", "upgrade", and "uninstall" functionality, as well as set the right execution permissions that will allow the data reader account to run the report in a generic environment.

In this example, there is a main report and a detail report. The detail report may be launched by clicking on an element in the main report – consider this a linked report.

Here’s the entire fragment

<ManagementPackFragment SchemaVersion="2.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Reporting>
    <DataWarehouseScripts>
      <DataWarehouseScript ID="MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script" Accessibility="Public">
        <InstallScript>Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Install</InstallScript>
        <UninstallScript>Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Uninstall</UninstallScript>
        <UpgradeScript>Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Upgrade</UpgradeScript>
      </DataWarehouseScript>
      <DataWarehouseScript ID="MyReports.Deploy.MyReportAvailabilityDataGet.Script" Accessibility="Public">
        <InstallScript>Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Install</InstallScript>
        <UninstallScript>Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Uninstall</UninstallScript>
        <UpgradeScript>Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Upgrade</UpgradeScript>
      </DataWarehouseScript>
    </DataWarehouseScripts>
    <Reports>
      <Report ID="MyReports.Availability.Main" Accessibility="Public" Visible="true">
        <Dependencies>
          <DataWarehouseScript>MyReports.Deploy.MyReportAvailabilityDataGet.Script</DataWarehouseScript>
        </Dependencies>
        <ReportDefinition>Res.MyReports.Availability.Main</ReportDefinition>
      </Report>
      <Report ID="MyReports.Availability.Detail" Accessibility="Public" Visible="true">
        <Dependencies>
          <DataWarehouseScript>MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script</DataWarehouseScript>
          <Report>MyReports.Availability.Main</Report>
        </Dependencies>
        <ReportDefinition>Res.MyReports.Availability.Detail</ReportDefinition>
      </Report>
    </Reports>
  </Reporting>
  <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
      <DisplayStrings>
        <DisplayString ElementID="MyReports">
          <Name>MyReports</Name>
          <Description>This management pack contains all data warehouse and reporting elements for custom MyReports.</Description>
        </DisplayString>
        <DisplayString ElementID="MyReports.Availability.Main">
          <Name>Availability Main</Name>
          <Description>Availability MyReport Main</Description>
        </DisplayString>
        <DisplayString ElementID="MyReports.Availability.Detail">
          <Name>Availability Detail</Name>
          <Description>Availability MyReport Detail</Description>
        </DisplayString>
        <DisplayString ElementID="MyReports.Deploy.MyReportAvailabilityDataGet.Script">
          <Name>Deploy MyReport Availability Data Get Script</Name>
        </DisplayString>
        <DisplayString ElementID="MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script">
          <Name>Deploy MyReport Availability Data Detail Get Script</Name>
        </DisplayString>
      </DisplayStrings>
    </LanguagePack>
  </LanguagePacks>
  <Resources>
    <Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Install" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Install.sql" HasNullStream="false" />
    <Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Uninstall" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Uninstall.sql" HasNullStream="false" />
    <Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Upgrade" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Upgrade.sql" HasNullStream="false" />
    <Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Install" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Install.sql" HasNullStream="false" />
    <Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Uninstall" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Uninstall.sql" HasNullStream="false" />
    <Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Upgrade" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Upgrade.sql" HasNullStream="false" />
    <ReportResource ID="Res.MyReports.Availability.Detail" Accessibility="Public" FileName="Res.MyReports.Availability.Detail.rdl" HasNullStream="false" MIMEType="application/octet-stream" />
    <ReportResource ID="Res.MyReports.Availability.Main" Accessibility="Public" FileName="Res.MyReports.Availability.Main.rdl" HasNullStream="false" MIMEType="application/octet-stream" />
  </Resources>
</ManagementPackFragment>

 

Let’s break it down.

 

Data Warehouse Scripts

<DataWarehouseScript ID="MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script" Accessibility="Public">
  <InstallScript>Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Install</InstallScript>
  <UninstallScript>Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Uninstall</UninstallScript>
  <UpgradeScript>Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Upgrade</UpgradeScript>
</DataWarehouseScript>

This is where the resource file pointers are defined – your sql stored procedures. You will need each version of the stored procedure to install, upgrade, and uninstall the stored procedure. These pointers reference the actual sql file resource later.

Note: I have yet to see the uninstall work (I think this is a bug in the sdk, but I won’t go there now).

 

Reports

<Report ID="MyReports.Availability.Main" Accessibility="Public" Visible="true">
  <Dependencies>
    <DataWarehouseScript>MyReports.Deploy.MyReportAvailabilityDataGet.Script</DataWarehouseScript>
  </Dependencies>
  <ReportDefinition>Res.MyReports.Availability.Main</ReportDefinition>
</Report>

This section defines the report id, the report dependencies, and the report definition resource (this points to the actual rdl file later).

 

Resource Files (skipping language packs, as we know what that’s for)

<Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Install" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Install.sql" HasNullStream="false" />
<Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Uninstall" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Uninstall.sql" HasNullStream="false" />
<Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Upgrade" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataGet.Script.Upgrade.sql" HasNullStream="false" />
<Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Install" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Install.sql" HasNullStream="false" />
<Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Uninstall" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Uninstall.sql" HasNullStream="false" />
<Resource ID="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Upgrade" Accessibility="Public" FileName="Res.MyReports.Deploy.MyReportAvailabilityDataDetailGet.Script.Upgrade.sql" HasNullStream="false" />
<ReportResource ID="Res.MyReports.Availability.Detail" Accessibility="Public" FileName="Res.MyReports.Availability.Detail.rdl" HasNullStream="false" MIMEType="application/octet-stream" />
<ReportResource ID="Res.MyReports.Availability.Main" Accessibility="Public" FileName="Res.MyReports.Availability.Main.rdl" HasNullStream="false" MIMEType="application/octet-stream" />

This section ties the resource id’s from above to actual physical files you will include in your solution – these are the .rdl and .sql files.

 

Stored Procedure Necessities

I mentioned earlier that I would discuss a couple things you will need in your stored procedure. Namely, you will need to specify the action of the procedure (install, upgrade, uninstall) and you need to assign permissions to execute the procedure (otherwise it will not work and you’ll get errors).

MSDN has a mediocre description for deploying stored procedures, but it falls short with real world examples. So I’ll give an example of each here.

Install

Basically, you need to first create the procedure, and then alter the procedure as follows. Don’t worry about the parameter declarations – it’s just an example in case you have them.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'MyReport_AvailabilityDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.MyReport_AvailabilityDataGet AS RETURN 1')
END
GO

ALTER PROCEDURE dbo.MyReport_AvailabilityDataGet
    @StartDate datetime,
    @EndDate datetime,
    @ObjectList xml,
    @MonitorName nvarchar(256),
    @DataAggregation tinyint = 0,
    @LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
{your procedure goes here}
 

Upgrade

The only thing that needs to be changed for the upgrade procedure is to remove the entire first section of the install procedure (the first 5 lines). Everything else stays the same – just start your procedure with the ALTER PROCUDURE section.

 

Uninstall

This is actually very simple – just perform a drop.

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'MyReport_AvailabilityDataGet')
BEGIN
        DROP PROCEDURE dbo.[MyReport_AvailabilityDataGet]
END
GO
 

Assign Permissions to the DataReader account

This is required at the end of both the install and upgrade scripts. The OpsMgrReader account is a standard role that is created during setup, so unless you have some custom configuration in your environment, this will work for you.

GRANT EXECUTE ON MyReport_AvailabilityDataGet TO OpsMgrReader
GO

 

And that’s about it. Now go write some reports and deploy them with your management pack, like a pro!

4 thoughts on “Report Fragment–Visual Studio Authoring Extensions”

  1. 1. Re uninstall: Uninstall part will be executed when the script is not fully installed yet (MemberDatabaseScript. InstallCompletedInd = 0) and the new version of the script arrives. (Ref: [dbo].[DeploymentOperationDescriptorList])

    2. Re upgrade: it is a good idea to include object creation with appropriate existence check for upgrade as well. Object may not exist in the previous version of the MP or may be removed by mistake – in this case ALTER will fail and will block further deployment of MP reporting components.

    3. Re object existence check: this should also include the schema check – I have seen some cases when SQL DBAs tried to change the default schema for OpsMgrWriter account, so it’s a good practice to specify the schema name ([dbo] or whatever you use) explicitly.

    4. Re script structure: ALTER statement should be the only statement in the batch, so script should look like this:
    SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘…’ AND schema_id=SCHEMA_ID(‘dbo’)
    BEGIN
    EXECUTE (‘CREATE PROCEDURE [dbo].[…] AS RETURN 1’)
    END
    GO

    ALTER PROCEDURE [dbo].[…]
    … — parameters
    AS
    BEGIN
    … — SP body
    END
    GO

    GRANT EXECUTE ON [dbo].[…] TO OpsMgrReader
    GO

  2. Any reason why you set the reports to visible=false? I was wondering quite some time why the reports were not showing up in the console…

Comments welcome (links require moderation)