Author Custom Reports in Ops Mgr 2007
Feed: www.contoso.se
Posted on: Sunday, January 25, 2009 3:53 PM
Author: Anders Bengtsson
Subject: Author Custom Reports in Ops Mgr 2007
Operations Manager 2007 collects large amounts of data from your environment. By using the Reporting feature, you can create reports based on this data that provide additional information about the health of your environment. Operations Manager can have four types of reports
· Published reports, automatically available in the console after ops mgr reporting installation
· Linked reports, based on existing reports
· Custom reports, authored from queries that you build in Visual Studio
· Report solution, defined with Visual Studio and are available in a management pack
I always try to solve the new report request with a linked report if possible. The next step is to use the built-in SQL Report Builder, which you find under the Reporting workspace. But when using that you will need a report model. A report model is a description of an underlying database that is used for building reports in Report Builder 1.0. For example in this post I use the ACS db (Audit Collection) report model to build custom ACS reports. In Ops Mgr there are report models for ACS, performance and event reports. But there are scenarios that you can´t solve with linked reports or SQL Report Builder, and then Visual Studio is a great tool to build reports with.
The first thing you need to do in Visual Studio when you start a new reporting project is to add a data source. A data source represents a connection to an external data source. The second thing is to add a report and a data set. A data set retrieves rows of data from a data source based on an SQL query. You can for example the query string below when working with performance reports. As you can see it looks for performance counters including Available MBytes in the name.
SELECT
vManagedEntityTypeImage.Image,
vPerfHourly.DateTime,
vPerfHourly.SampleCount,
vPerfHourly.AverageValue,
vPerfHourly.StandardDeviation,
vPerfHourly.MaxValue,
vManagedEntity.FullName,
vManagedEntity.Path,
vManagedEntity.Name,
vManagedEntity.DisplayName,
vManagedEntity.ManagedEntityDefaultName,
vPerformanceRuleInstance.InstanceName,
vPerformanceRule.ObjectName,
vPerformanceRule.CounterName
FROM
Perf.vPerfHourly INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId =
vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId =
vManagedEntityType.ManagedEntityTypeRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntityType.ManagedEntityTypeRowId =
vManagedEntityTypeImage.ManagedEntityTypeRowId INNER JOIN
vPerformanceRuleInstance ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId =
Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId =
vPerformanceRule.RuleRowId
WHERE
(vPerformanceRule.CounterName LIKE N’%Available MBytes%’)
ORDER BY vPerfHourly.DateTime
There are a number of good query strings at this TechNet page. The next thing to do is to start designing your report. You can drag and drop report objects from the toolbox. Report items add data, structure, and formatting to a report and come in two varieties; data regions and independent items. A data region renders data from an underlying data set.Independent report items are items that are not associated with a data set, for example a line or a rectangle. If we continue with the available Mbytes example a chart would be a good start. By drag and dropping a chart from the toolbox and then fields from the dataset you can easily create a chart. But it is not that easy-to-read.
To make the report more precise we could start by adding a drop down menu to select which machine to look at performance data from. To do that we first need to create a new dataset. You can use the same SQL query as before, but in this dataset only
select DISTINCT vManagedEntity.Path
, as we only want machines in the drop down menu. Then go to the Report menu and select to add a report parameter. Select to create a report parameter with a query based value and then select your new dataset and the path field. Then you need to add this parameter to your first dataset, as you want to only see performance data for the selected machine. To do that add vManagedEntity.Path and your parameter to the SQL query.
(vPerformanceRule.CounterName LIKE N’%Available MBytes%’) AND (vManagedEntity.Path = @Server)
If we now preview the report there is a drop down menu with all machines where the chart only show data shows data related to the selected machine.
The next think would be to change the time range. You can do that with report parameters and then add them to your SQL query. If you want to add a dynamic time range, for example NOW minus 7 days you can use the DateAdd command in your SQL query.
If you then right-click the chart there are a number of settings, for example change the scale, change chart type, enable 3-D and add a title to the chart. Other things that you might want to add is a header and some text to your report, then a table with details about the data in the chart. You can drag and drop both text box and matrix from the toolbox.
When you are satisfied with your report you can right-click the report project (top left side of visual studio) and deploy the report to your reporting server.
View article...
Published with BlogMailr