How to use Report Builder to create custom reports in SCOM 2007

Operations Manager has some really nice reporting features. Most management packs have several predefined reports on board and even community management packs (especially those from last MP contest) have pretty cool reports on board as well. Still, sometimes there’s just not the right report available. In this case you have a couple of options, here are some popular examples:

1. Access the data directly in the database:
   http://blogs.technet.com/b/kevinholman/archive/2007/10/18/useful-operations-manager-2007-sql-queries.aspx
2. Creating a Custom Report for SCOM 2007 R2 with SQL 2008 reporting in Microsoft Visual Studio 2008
   http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexId/60805/Default.aspx

While those options have many advantages and gives you full control of how your report will look and feel like, it’s very time consuming and there’s also a steep learning curve involved. If you just want to get the data out of your data warehouse and sent to you by email as an excel file, it’s a lot of trouble to go through.

Another option is to use the built-in Report Builder which ships with SQL Reporting Services. I couldn’t find much information on how to use this beast with SCOM, so I decided to play around with it and blog about it.

But before we get started, we need to install the models on the reporting server. Pete posted an excellent blog post over a year ago describing how to install the models which are shipped with SCOM since SP1:http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/20269/Default.aspx

After you installed the models and assigned the right data source to them, we can get started…

The Mission:

For this demo, I show how to create a report listing all computers where the average CPU utilization over the last 7 days is below a certain value. There’s a similar report in the Virtual Machine Manager Management Pack but unfortunately the report is not designed to get scheduled because it only accepts absolute date values. So, our report here should be scheduled to run every week showing the results for the last 7 days.

Get Started:

In the reporting space you should an “Actions” item called “Design a new report” (be sure you have the Actions pane visible!):

Clicking on the above item will open the Report Builder of SQL Reporting Services. When you successfully installed the models as explained in Pete’s post, you should see the two models in the Getting Started pane:

Select the Performance model, Table layout and click on OK. On the left hand side you should now see the “Explorer” pane showing you all the entities from the model and the corresponding fields below.

Basic Report Design:

Our report should contain a column showing the computer name, one showing the average value, one for the minimum value and another one for the maximum value of the CPU utilization. In the Entities explorer select “Object” and in the fields box, drag the “Name” field to the column fields on the design surface.

After doing that, click on the “Performance Data Dailies” entry in the Entities explorer. Expand the fields “# Average Value”, “# Min Value” and “# Max Value”, like this:

Drag the child elements: “Avg Average Value”, “Min Min Value” and “Max Max Value” to thecolumn fields area on the design surface, next to the name field. Right-click on each of the columns and uncheck the “Show Subtotal” item to get rid of the subtotal line. You can alsodouble-click on the column headers to provide some meaningful name for each column. Your designer should look like this now:

Fine Tuning the Report Design:

Right-click on the field below the computer name column header (on the “xxxxxx” text)and select “Edit Formula…”. Let’s use the formula dialog to ensure every computer name is written in upper case:

For the average, min and max values we want to round the numbers to display only 2 digits. Open the formula dialog as before for these columns and use the ROUND function:

Sorting:

Click on the “Sort and Group” tool bar button to specify which column is sorted by default:

You may also check out the Report Properties available using the Report menu:

Filtering:

Now it’s time to pick the right data from our model. First thing to do is to limit our results to the class “Windows Computer”. To do that, select Class as entity and drag the “Class Default Name” to the right panel and select Windows Computer from the Filter List:

After that we need from the “Performance Data DailyPerformance Rule InstancePerformance Rule” entity the fields “Performance Object Name” (equals Processor) and “Performance Counter Name” (equals % Processor Time).

From “Performance Data DailyPerformance Rule Instance” entity the field “Instance Name”(equals _Total).

Then select “Performance Data Daily” from the entity box and drag the Date Time field to the right and configure it “after 7 days ago”.
Lastly, expand the # Average Value field and drag the Avg Average Value to the right, and configure it “less than or equal to 1”. Right click on the last condition and select “Prompt” from the context menu to make this value configurable. The filter should then look like this:

Conclusion:

Now you can test and run the report from the report builder or save it directly to the reporting services instance. Use the web access to the reporting services reports to create a new folder if you wish and save your freshly compiled report to that folder. After a refresh in the Operations Console, the folder and the report will appear and is ready to run and more important: ready to schedule!

You can always re-open your reports with the Report Builder using the File – Open menu. I hope I could show you how to use the models and the reporting builder to create your own reports. If you have any feedback or improvement suggestions, let me know.