OpsMgr Custom Reporting Tips & Tricks

OpsMgr Custom Reporting Tips & Tricks

  • Comments 2
  • Likes

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.

  1. What do I need for creating Custom OpsMgr Reports?
  2. 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)
  3. 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)

  1. Open SQL Server Business Intelligence Development Studio and click on File and create a new Project
    image 
  2. Select Report Server Wizard Project Wizard and give your Report a Name and click on OK.
    image
  3. Click Next on Report Wizard Screen
    image
  4. Create a New data source and give it a name like, DataWarehouseMain and click on Edit.
    image
  5. Enter the correct Connection Properties and click on OK.
    image
  6. Click on Next in Report Wizard screen
    image
  7. Now we need to use the SQL query from the Authoring Guide. Copy and past the SQL query to the Query string window.
    image

    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.

    image

    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.

      image

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

    image

10.Select the style of the layout.
    image

11. Select the Deployment Location and click on Next.

    image

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

image

So this is the Result:

image

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


These are some things we are going to change:

  1. Less columns (EventNumber, Event Publisher Name, Date Time, ComputerName, UserName and Rendered Descripition)
  2. Add a Start and End Data Parameter
  3. Add a select Computer Group Parameter
  4. 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'

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
AND
      Evt.DateTime BETWEEN @StartDate AND @EndDate

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.

image 

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

image

image

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

image

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)

image

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

image

Test the Report and select different Start and End Dates

image

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.

image

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

Select Add DataSet.

image

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

image

And click on OK to save DataSet

image

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:

image

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.

image

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'


image

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.

image

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

image

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.

image

image

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

image

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.

image

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.

image

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.

image

Let’s create the some JOINs.

image

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.

image

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:

image

After Changing the Computer Group:

image

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

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

image

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

image

image

Result:

image

As you see we need to configure the Group Display Name

Therefore we need the ComputerGroup DataSet

image 

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

image

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

image

Test the Report with the Preview Tab.

image

Result:

image

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

image

Insert the next expression into you text box:

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

image

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:

image

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

image

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

image

 

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!

Attachment: Simple Report 1.rdl
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Hi Stefan, first of all I would want to thank you for an excellent blog. IT really has helped me. You certainly know how to communicate woth your audience. I have a small favour to ask. When I ran the SELECT * FROM vEventLoggingComputer - NETBIOS Names showed in the output results. The Join appear not to work as its not showing me the correct information. You mentioned that you would be able to provide another query to those with this problem. Please at your nearest convenience can you perhaps post this query here. Thanks again for all your help

  • Hi Stefan, I was wondering if you had some time to look at this for me. My email address is Lawrence dot Idem at smurfitkappa dot com. I anxiously wait for your help and support. Thanks again