Reporting in System Center 2012 Virtual Machine Manager (Part 2 of 3)

Hello everyone. In the Part 1 we looked at reports that System Center 2012 Virtual Machine Manager (VMM) ships out of the box for our customers; in this part we want to shift gears a bit and talk about what additional things you can do based on the data we already are pumping into the OM Data Warehouse.

I touched upon the fact that the canned reports that we ship in one sense can be used as templates for your own custom reports; reading between the lines what it really means is that it’s not just the canned reports but the data that we are pushing into the OM Data Warehouse is something you could leverage upon.

How and Why? Well, every business and every group within an organization will often have very specific requirements when it comes to reporting. This means that very likely there are certain reports that you want tailored to the specific questions you want answered for a particular business related question. Having this data that VMM is pushing into the warehouse, you can build exactly that. As an example, lets look at how we can build a custom report that depicts CPU and Memory usage at a cloud level and at a cloud user level.

For this example I am going to use Microsoft Business Intelligence Development Studio (BIDS) to create the custom report.

What VMM Data is available in OM DW for custom reports?

VMM Library and Discovery MPs define all the types that are pushed into OM DW. To easily view this type information, you could use a tool like MP Viewer (run it from a machine that has System Center Operations Manager console on it). I have pointed out a few properties that we will be using in this custom report.

Creating a sample DW query for VMM Data

When the VMM-OM connection is established, instance data is being pushed into the OM warehouse and this data is available through the set of views that OM is making available. You should have a basic understanding of Data Warehouse Schema for this query. For our sample report, to report on memory usage say at a cloud level I will have to write a custom SQL query to gather and correlate information from these various views (time to take your SQL developer for a coffee J).

This sample query gets a property (Owner), related objects (Cloud, Service), and daily average perf data (CPU, Memory usage) for a VM object in a given time window.

It uses a wrapper query that formats the results from following 4 sub queries —

1- Gets Perf Data for VMs

2- Fetches Owner property of VMs

3- Fetches Clouds for VM

4- Fetches Services for VM, if available

Creating a Sample Custom Report

Once you have the query in place you can run it as is to ensure that it is pulling the data you are looking for and tweak it as necessary:

You should also see all the available data fields resulting from your query in the Report Data->Datasets section of the designer. In this case I am using the OperationsManagerDW database residing on my SQL Server as my data source using default Windows authentication.

From here you can drag and drop and format various form elements you need for the report in a straightforward way. Of course in the process you can define what kind of mathematical computation is required on each of those data fields that the query produces, what kind of interactivity with the report elements you need the report to have (like sorting, collapsing) etc. For the memory usage per cloud chart for our example I am going to format my Chart data to use averages for the DailyAvgMemUsage data field and report it by aggregating it per Cloud. Here is how chart data properties would look like for that pane:

I also want to show a tabular view of data supporting my report that can be sorted based on either the daily average memory or CPU usage. So I am going to use a table defined such that the data is being aggregated by VMOwner field and sortable on either the Memory or CPU usage fields.

And that’s about it. Click on the preview tab and my custom report appears! There are multiple ways to deploy the report now onto the OM reporting server and just about the easiest way is to deploy it directly from BIDS (you just need to make sure the OM reporting server URL is configured correctly in your project). Once you do that ahoy! Your custom report shows up in the OM reporting console and is ready for consumption.

Time to party J. Ah.. here is how the actual report we started out creating looks like:

For those of you who are reading the above report keenly.. you would have observed that my memory usage per cloud and per user charts shows up exactly the same – happens to be I have only two users; one in each cloud. Regardless we think it’s a pretty useful custom report to begin with..