OpsMgr Custom Reporting Tips & Tricks

Article
12/30/2009

Lately I’ve been busy creating some OpsMgr Custom Reports for customers and wanted to share some tips & tricks for creating custom OpsMgr Reports. Hope you can use them in your own custom OpsMgr Reports.

First some background info on what to use for creating Custom OpsMgr Reports.

What do I need for creating Custom OpsMgr Reports?
- Your first start needs to be, reading the Operations Manager 2007 Report Authoring Guide
- Secondly you need to install Visual Studio and SQL Server Business Intelligence Development Studio
What next? How do I create my first OpsMgr Custom Report?
- The highlevel steps for creating a Custom OpsMgr Report are:
  1. Create a database query to get the data you need
  2. Use Visual Studio (SQL Server Business Intelligence Development Studio) to create the Report
  3. Deploy the Report to OpsMgr
- In the Operations Manager 2007 Report Authoring Guide are some examples to get you started. (starts on page 23)
How do I make my Custom Report more beautiful?
- It depends on what you mean with more beautiful ;-) But here are some examples I use to make my custom OpsMgr Reports more beautiful;
  - Insert Charts
  - Insert Pictures
  - Show extra info, like;
    - who run the report
    - how long did it take for the report to run
    - extra parameters (Begin Date and End Date, Filter on ComputerGroup Members).

Let’s start with the Custom Reports - Sample Scenario for a Simple Report 1 from Operations Manager 2007 Report Authoring Guide and try to make this report a little bit more beautiful ;-)

Background info: This report collects all Events with an ID of 21025. I created this Custom Report on my OpsMgr 2007 R2 environment with SQL 2008 and Visual Studio 2008.

Here are the steps taken from the Authoring Guide with some extra info added me:

Procedures
To create a report using Visual Studio (open SQL Server Business Intelligence Development Studio)

Open SQL Server Business Intelligence Development Studio and click on File and create a new Project
Select Report Server Wizard Project Wizard and give your Report a Name and click on OK.
Click Next on Report Wizard Screen
Create a New data source and give it a name like, DataWarehouseMain and click on Edit.
Enter the correct Connection Properties and click on OK.
Click on Next in Report Wizard screen

Now we need to use the SQL query from the Authoring Guide. Copy and past the SQL query to the Query string window.

SELECT evt.eventnumber,Evt.EventOriginId, Evt.EventPublisherRowId, Pub.EventPublisherName, Evt.DateTime, Evt.EventChannelRowId, Chl.EventChannelTitle, Evt.EventCategoryRowId, Chl.EventChannelTitle, Evt.EventLevelId, Lev.EventLevelTitle, Evt.LoggingComputerRowId, Cmp.ComputerName, Evt.EventDisplayNumber, Evt.UserNameRowId, Usr.UserName, Det.RawDescription, Det.RenderedDescription, Det.EventData FROM Event.vEvent(NoLock) Evt Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join Event.vEventDetail (NoLock) Det On Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025

TIP: Test this query first in SQL Management Studio.
Ok, we know now this query will give us some results back when we use this in our Custom Report.

8. Select the report type, Matrix or Table, and then click Next.

9. Select the fields to be displayed on the different areas of the report, and then click Next.

10.Select the style of the layout.

11. Select the Deployment Location and click on Next.

12. Give the Report a Name and Review the Report Summary and select Preview Report and click on Finish

So this is the Result:

Maybe not completely what you wanted. Let’s change that.

These are some things we are going to change:

Less columns (EventNumber, Event Publisher Name, Date Time, ComputerName, UserName and Rendered Descripition)
Add a Start and End Data Parameter
Add a select Computer Group Parameter
Add who run the report and how long did it take for the report to run

Less columns

If we want less columns in our Report we can do two things; change the SQL query or remove the columns in the Report Designer. If you don’t need the columns leave them out the query, I would say.

We can change the SQL query to:

SELECT Pub.EventPublisherName, Evt.DateTime, Cmp.ComputerName, Evt.EventDisplayNumber, Usr.UserName, Det.RenderedDescription FROM Event.vEvent(NoLock) Evt Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join Event.vEventDetail (NoLock) Det On Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025

Add a Start and End Data Parameter

If we want to be able to select a Start and End Date parameter in the report we need to add some variables to our query.

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME

SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-21 00:00:00.000'

Remark: Use above query in SQL Management Studio for testing the results. In Visual Studio you don’t need to DECLARE and SET the StartDate and EndDate.

Let’s test this first in Visual Studio

The easiest way is to create a new Report using the already created Data Warehouse.

If you select Add New Report the same Report Wizard is shown. See for the steps above, but now use the new SQL query. There is no need to create a New Data Source

After clicking Next you see the Preview Report and there you see the two added parameters Start Date and End Date

Before we can use these parameters we need to configure them. Click on Design. Double click on Parameters in the Report Data screen (in Visual 2005 you go to tools I believe)

Change the Data Type from Text to Date/Time for both Parameters

Test the Report and select different Start and End Dates

Add Computer Group Parameter

Now we want to add a Computer Group parameter so we can select a Computer Group where we want to see the events from it’s members.

We first need to create a SQL query that shows us all the OpsMgr Computer Groups in a dropdown list.

SELECT DisplayName FROM vManagedEntity WHERE ManagedEntityTypeRowID in (SELECT ManagedEntityTypeRowID from dbo.ManagedEntityDerivedTypeHierarchy ((SELECT ManagedEntityTypeRowId from vmanagedentitytype WHERE managedentitytypesystemname = 'system.group'),0)) ORDER BY DisplayName

This will give us the next result.

Now we need to create a new DataSet for this query in Visual Studio.

Select Add DataSet.

Give the Dataset a Name (ComputerGroup) insert the above SQL query.

And click on OK to save DataSet

But how do we use this ComputerGroup and it’s member to filter the eventids in our Report?

First we need to retrieve the the members of the ComputerGroup and store them in a Table variable so we can use this in a JOIN.

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable).

The next SQL query retrieves all members of a given ComputerGroup.

DECLARE @GroupDisplayName NVARCHAR(250)

SET @GroupDisplayName = 'Agent Managed Computer Group';

USE [OperationsManagerDW]

Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName=@GroupDisplayName

Result from SQL Management Studio:

If we place the results of this query in a temp table or table variable we can use this in a JOIN on ComputerName from the Event Table.

TIP: Use the Design Query in Editor if you need to create a JOIN for your query.

TIP: Create the TEMP table first as a new table in your database, so you can use this table in the Design Query in Editor SQL Server Management Studio

To create the table ComputerGroupMembers in the OperationsManagerDW database run the next query.

CREATE TABLE ComputerGroupMembers (Displayname NVARCHAR(250))

INSERT INTO ComputerGroupMembers Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName='Management Server Computer Group' -- 'Management Server Computer Group' of -- 'Agent Managed Computer Group'

We now created the ComputerGroupMember table. We can use this table to only show the records for the members of our computer group.

First we take a look at the base query and remove any unnecessary stuff.

SELECT Pub.EventPublisherName, Evt.DateTime, Cmp.ComputerName, Evt.EventDisplayNumber, Usr.UserName, Det.RenderedDescription

FROM Event.vEvent(NoLock) Evt Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join Event.vEventDetail (NoLock) Det On Evt.EventOriginId = Det.EventOriginId

Let’s copy this to the Design Query in Editor tool.

Reorder the tables and have look at the JOINs in the original query.

Now we have a look at how we could create a JOIN on the ComputerGroupMembers temp table we have created.

Let’s add our table.

Then we need to create a JOIN for DisplayName with ComputerName in the Cmp table.

Remark: In some OpsMgr environments the vEventLoggingComputer View of the OperationsManagerDW database returns a NETBIOS name instead of the FQDN and your JOIN is not working.

Please check this by running the next SQL query:

SELECT * FROM vEventLoggingComputer

If the above results return FQDN you are ready to go on. If not contact me and I’ll help you solve this by using another query.

Let’s assume you can create the JOIN between the ComputerGroupMembers (FQDN members) and the vEventLoggingComputer View. We are only interested in the Rows from our ComputerGroupMembers.

When we test this query in the SQL Analyzer we get this:

SELECT Pub.EventPublisherName, Evt.DateTime, Cmp.ComputerName, Evt.EventDisplayNumber, Usr.UserName, Det.RenderedDescription, ComputerGroupMembers.Displayname FROM Event.vEvent AS Evt WITH (NoLock) INNER JOIN EventPublisher AS Pub WITH (NoLock) ON Evt.EventPublisherRowId = Pub.EventPublisherRowId INNER JOIN EventChannel AS Chl WITH (NoLock) ON Evt.EventChannelRowId = Chl.EventChannelRowId INNER JOIN EventCategory AS Cat WITH (NoLock) ON Evt.EventCategoryRowId = Cat.EventCategoryRowId INNER JOIN EventLevel AS Lev WITH (NoLock) ON Evt.EventLevelId = Lev.EventLevelId INNER JOIN EventLoggingComputer AS Cmp WITH (NoLock) ON Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId INNER JOIN EventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId RIGHT OUTER JOIN ComputerGroupMembers ON Cmp.ComputerName = ComputerGroupMembers.Displayname

This is still not completely correct but this is a start to work from.

Ok, let’s try to fix this. First let us pick the Tables or Views we need for our query.

I think we need the next table (ComputerGroupMembers) and views.

Let’s create the some JOINs.

So your query could look something like this:

SELECT ComputerGroupMembers.Displayname, Evt.DateTime, Evt.EventDisplayNumber, Det.RenderedDescription, Usr.UserName FROM ComputerGroupMembers WITH (NOLOCK) INNER JOIN vEventLoggingComputer AS Cmp WITH (NOLOCK) ON Cmp.ComputerName = ComputerGroupMembers.Displayname INNER JOIN Event.vEvent AS Evt WITH (NOLOCK) ON Cmp.EventLoggingComputerRowId = Evt.LoggingComputerRowId INNER JOIN vEventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025

TIP: Use the NOLOCK option.
SQL Server offers many hints that can be used to determine how a query executes and therefore the impact of the query on other processes. One such hint that is offered in SQL Server is the NOLOCK hint. This query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks.

Let’s test above query in SQL Management Studio by changing the Members of the Computer Group.

I changed the members of the ComputerGroupMembers table by changing the the Computer Group in above query.

Now let’s look at our Report Query. Hopefully we only see events from OpsMgrR2DC1.opsmgrdemo.r2.

Before changing the Computer Group:

After Changing the Computer Group:

It seems to work ;-)

No we can remove the ComputerGroupMembers table from the OperationsManagerDW database because we are going to use a temp/variable table later on. The ComputerGroupMembers table was only to help us create the correct query.

Run DROP TABLE ComputerGroupMembers to delete the table.

We are going to use the next query to create a variable table in our last query.

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @ComputerGroupMembers TABLE (DisplayName NVARCHAR(250)) DECLARE @GroupDisplayName NVARCHAR(250)

SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-21 00:00:00.000' SET @GroupDisplayName = 'Management Server Computer Group';

--INSERT ComputerGroup Members in Variable table @ComputerGroupMembers --Use Variables if the table is not large so it can be hold in memory --Retrieves Members of selected OpsMgr ComputerGroup INSERT INTO @ComputerGroupMembers Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName=@GroupDisplayName

SELECT t1.Displayname, Evt.DateTime, Evt.EventDisplayNumber, Det.RenderedDescription, Usr.UserName FROM @ComputerGroupMembers as t1 INNER JOIN vEventLoggingComputer AS Cmp WITH (NOLOCK) ON Cmp.ComputerName = t1.Displayname INNER JOIN Event.vEvent AS Evt WITH (NOLOCK) ON Cmp.EventLoggingComputerRowId = Evt.LoggingComputerRowId INNER JOIN vEventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025

AND Evt.DateTime BETWEEN @StartDate AND @EndDate

We save the Members of the Computer Group ‘Management Server Computer Group’ in variable which we later use in the final SELECT statement.

And now we can finally go back to Visual Studio and use the above query in our Simple Report 1.

Again add a new report and start the Report Wizard.

Use the next query in the Query Builder:

DECLARE @GroupDisplayName NVARCHAR(250)

INSERT INTO @ComputerGroupMembers Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName=@GroupDisplayName

AND Evt.DateTime BETWEEN @StartDate AND @EndDate

As you can see there are three parameters needed. Parameter GroupDisplayName is filled by the ComputerGroup DataSet.

Result:

As you see we need to configure the Group Display Name

Therefore we need the ComputerGroup DataSet

Go to Parameters folder and right click and select Parameter Properties.

Select Get Values from a query, select the ComputerGroup Dataset and select the DisplayName for Value field and Label Field and click on OK.

Test the Report with the Preview Tab.

Result:

As you see selecting the ‘All Windows Computer’ Group returns all events with eventid 21025 from all members of the All Windows Computer Group!

Who run the report and how long did it take for the report to run

We can add extra info to the report by inserting two text boxes with who run the report and how long it took for the report to run.

Add a TextBox from the Toolbox to your Report

Insert the next expression into you text box:

="Generated by " + User!UserID + " on " + Globals!ExecutionTime.ToString()

Add another TextBox for the How long the report has run.

Use the next expression:

="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")) ) & " at server: " & Globals!ReportServerUrl

Result:

Final Tip: Change the parameter order. With up and down arrows you can change the order of your parameters.

So if you have done all this and more your Simple Report 1 could look like this.

I’ve attached the Report.rdl file so you can have a look at the complete report.

Have fun creating OpsMgr Custom Reports and have a great New Year!

Simple Report 1.rdl

OpsMgr Custom Reporting Tips & Tricks

Additional resources