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:

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:

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:

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:


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 Daily\Performance Rule Instance\Performance Rule”  entity the fields “Performance Object Name”  (equals Processor) and “Performance Counter Name”  (equals % Processor Time).

From “Performance Data Daily\Performance 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:


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.


Category: Bank

Similar articles: