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?
- What next? How do I create my first OpsMgr Custom Report?
- The highlevel steps for creating a Custom OpsMgr Report are:
- Create a database query to get the data you need
- Use Visual Studio (SQL Server Business Intelligence Development Studio) to create the Report
- 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'
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.
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
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 |
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!
