Designing the perfect SCOM report – my workflow (Part 3/3)

Part 1 (Intro, Get knowledge, Get prepared)
Part 2 (Plan) 

Implement

In previous parts we discussed how to get ready for SCOM reports development and how to plan the perfect report. Now we’re ready to rock.

If you have done your homework, the implementation will be pretty much straightforward process , the speed will depend on your technical skills only. My implementation workflow usually looks as follows:

  1. Plugin headphones into my smartphone and switch on the music (now playing: “Autumn in New York” soundtrack);
  2. Write SQL Server stored procedure(s) for data retrieval;
  3. Create report definition (RDL);
  4. Create report parameter block (RPDL);
  5. Inject stored procedures, report definition and parameter block into management pack.

That’s it. Simple. However, there are a lot of pitfalls in reality, so I’ll provide an example.

0. Planning

Idea Report that will show number of discovered Windows Servers by version and edition at moments A and B. Reports should provide a capability of identifying which servers existed only at moment A or at moment B.
Audience & Intent IT managers – control migration process (or whatever they need to get from this report), IT Admins – review details if IT manager complains that something wrong.
Scope Any group or object can be specified. Containment should be respected if “Add group” button is used. Containment shouldn’t be enforced if “Add object” button is used.
Interval We’ll use 2 dates: first for moment A and second for moment B. We will not consider everything happened between these moments, data “slice” is what we’re looking for.
Parameters Moment A, Moment B, Time zone,  Scope.
Design & Layout Simple layout, text header (no graphic ),  no summary. Report parameters should be provided in separate section, time zone information should be placed in the footer. Report body should contain 2-level table: 1st level – summary (version & edition, server count for moment A and moment B), 2nd level – list of server names (collapsible).
Data retrieval strategy We’ll use Veeam_GRL_ObjectListGetSelected from Veem eGRL to get the list of selected objects, Veeam_GRL_ObjectListGet from Veem eGRL to parse the scope. Also, we’ll query vManagedEntity and vManagedEntityProperty to get the list of servers, aggregation will be done at SSRS level.
Performance This is a sample report, so we don’t care, however taking a look at query execution plan and  load test is always a good idea.
Extensibility Not required.

 

Okay, we’re done with defining what we want to implement, but the development environment is still not ready. Though all bits are installed, we’re still missing the project structure. As I use MPElementsExtract tool to inject code into management pack, I keep my directory structure compatible with this tool:

1. SQL

This report is the simple one, there is no complex logics inside. We’ll need following columns in resultant dataset:

  1. OS Version display name;
  2. Server name;
  3. Indicator if the server was running that OS version at moment A;
  4. Indicator if the server was running that OS version at moment B;

For sure, this report will not be very convenient for  tracking configuration, changes, but we do not consider this intent in this example.

Here is the implementation:

-- ##### SampleSCOMReports_GetWinServInfo_CreateAlter.sql
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('dbo') AND type = 'P' AND name = 'SampleSCOMReports_GetWinServInfo')
BEGIN
	EXECUTE ('CREATE PROCEDURE dbo.[SampleSCOMReports_GetWinServInfo] AS RETURN 1')
END
GO

ALTER PROCEDURE dbo.[SampleSCOMReports_GetWinServInfo]
	@dt1 DATETIME,
	@dt2 DATETIME,
	@ObjectList XML
AS
BEGIN
	DECLARE @METId INT
	DECLARE @METPropId UNIQUEIDENTIFIER

	-- get type ID for Windows Server OS
	SELECT @METId=vmet.ManagedEntityTypeRowId FROM vManagedEntityType vmet WHERE vmet.ManagedEntityTypeSystemName='Microsoft.Windows.OperatingSystem'
	-- get property ID for Windows Server OS version
	SELECT @METPropId=vmetp.PropertyGuid FROM vManagedEntityTypeProperty vmetp WHERE vmetp.ManagedEntityTypeRowId=@METId AND vmetp.PropertySystemName='OSVersionDisplayName'

	DECLARE @objects XML

	-- parse scope
	EXEC Veeam_GRL_ObjectListGet
		@StartDate = NULL,
		@EndDate = NULL,
		@ManagedEntityTypeRowId = @METId,
		@ObjectList = @ObjectList,
		@XmlOutputOnly = 1,
		@OutputXml = @objects OUT

	;WITH cte ([Server], [Version], A, B)
	AS
	(
		-- get all servers and their versions as of moment A (@dt1)
		SELECT
			[Server]=vme.[Path],
			[Version]=vmep.PropertyXml.value('(/Root/Property[@Guid[. = sql:variable("@METPropId")]]/text())[1]','nvarchar(max)'),
			A=1,
			B=0
		FROM
			vManagedEntity vme
			INNER JOIN @objects.nodes('/Objects/Object/ManagedEntityRowId') o(t) ON vme.ManagedEntityRowId=o.t.value('.','int')
			INNER JOIN vManagedEntityProperty vmep ON vmep.ManagedEntityRowId = vme.ManagedEntityRowId
		WHERE 
			vmep.FromDateTime<=@dt1 AND (@dt1 0 exists at moment A
		IsInB = SUM(B)	-- > 0 exists at moment B
	FROM
		cte c
	GROUP BY
		c.Version,
		c.[Server]	
END
GO

GRANT EXECUTE ON dbo.[SampleSCOMReports_GetWinServInfo] TO OpsMgrReader
GO

2. RDL

This is the easiest part.  Let’s create new SSRS project and create new report. After that we’ll need:

Reference Microsoft.EnterpriseManagement.Reporting.Code assembly…

Add some parameters…

… and code…

… and data sets…

… and report layout.

And, finally, test that everything works.

3. RPDL

This part may be tricky for a newcomer, but, actually, the only thing you need to remember is that controls are being rendered from left to right and from top to bottom. If you want to span control across multiple rows or columns, then just take a piece of paper and a pencil, like I usually do:

Fortunately, for this report everything is nice and easy. Here is the code for report parameter block (RPDL):

<?xml version="1.0" encoding="utf-8"?>
<ParameterBlock columns="3" xmlns="http://schemas.microsoft.com/mom/reporting/2007/ReportParameterSettings">
  <Controls>
    <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.RelativeDateTimePicker">
      <ReportParameters>
        <ReportParameter name="TZ" binding="TimeZone">
                <Prompt>SampleSCOMReport!SampleSCOMReport.Timezone</Prompt>
        </ReportParameter>
        <ReportParameter name="TZName" binding="TimeZoneName" />
        <ReportParameter name="DSBaseType" binding="StartDate_BaseType" />
        <ReportParameter name="DSBaseValue" binding="StartDate_BaseValue">
                <Prompt>SampleSCOMReport!SampleSCOMReport.MomentA</Prompt>
        </ReportParameter>
        <ReportParameter name="DSOffsetType" binding="StartDate_OffsetType" />
        <ReportParameter name="DSOffsetValue" binding="StartDate_OffsetValue" />
        <ReportParameter name="DFBaseType" binding="EndDate_BaseType" />
        <ReportParameter name="DFBaseValue" binding="EndDate_BaseValue">
                <Prompt>SampleSCOMReport!SampleSCOMReport.MomentB</Prompt>
        </ReportParameter>
        <ReportParameter name="DFOffsetType" binding="EndDate_OffsetType" />
        <ReportParameter name="DFOffsetValue" binding="EndDate_OffsetValue" />
      </ReportParameters>
    </Control>
    <Control type="Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.MonitoringObjectXmlPicker">
      <ReportParameters>
        <ReportParameter name="Objects">
                <Prompt>SampleSCOMReport!SampleSCOMReport.Scope</Prompt>
        </ReportParameter>
        <ReportParameter name="ManagementGroup" binding="GroupList" />
      </ReportParameters>
    </Control>
  </Controls>
</ParameterBlock>

 4. Inject into MP

Finally, we need to inject SQL scripts, report and parameter block into the management pack to deploy everything into SCOM. I’m not going to describe this process in details here. I encourage you to download my source code and study it. You may also want to read about my MPElementsExtract tool.  Have fun!

Downloads:

Leave a Comment