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

eHello 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.

Operations Manager publishes its Data Warehouse Schema and here is some good content on how to build custom reports.

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).

Here is a SQL query I am going to use (query provided as is):


DECLARE @precision INT 

DECLARE @daysHistory INT 




SET @daysHistory = 30 /* This report query looks at a static window of past 30 days*/ 

SET @precision = 1

SET @GBFactor = 1073741824.0

SET @ReportStartTime = DATEADD(DAY, @daysHistory * 1, GETUTCDATE()) 

SET @ReportEndTime = GETUTCDATE() 


SELECT CloudRelation.CloudName, 


       vME.DisplayName                     AS VM, 

       vOwner.PropertyValue                AS VMOwner, 

       ROUND(perfTable.CPUAvg, @precision) AS DailyAvgCPU, 

       ROUND(perfTable.RAMAvg, @precision) AS DailyAvgMemUsage, 



FROM   vManagedEntity AS vME 


       INNER JOIN  /* SubQuery 1 – VMs have aggregated daily perf data available in DW Perf Views*/




                            AVG(CASE  WHEN RName = N’Microsoft.SystemCenter.VirtualMachineManager.2012.VirtualMachine.PercentCPU’ THEN Entry.AVG1 END) AS CPUAvg, 

                            AVG(CASE  WHEN RName = N’Microsoft.SystemCenter.VirtualMachineManager.2012.VirtualMachine.Memory’     THEN Entry.AVG1 END) AS RAMAvg, 





                                vRule.RuleSystemName AS RName, 

                                AVG(perf.vPerfDaily.AverageValue) AS AVG1, 


                                CONVERT(DATE, perf.vPerfDaily.DateTime, 1) AS CollectionDate 

                            FROM   perf.vPerfDaily, 




                            WHERE  vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId 

                                    AND vPerformanceRuleInstance.PerformanceRuleInstanceRowId =  perf.vPerfDaily.PerformanceRuleInstanceRowId 

                                    AND vPerformanceRuleInstance.InstanceName = vManagedEntity.DisplayName 

                                    AND ( perf.vPerfDaily.DateTime >= @ReportStartTime ) 

                                    AND ( perf.vPerfDaily.DateTime <= @ReportEndTime ) 

                                    AND ( vRule.RuleSystemName IN (  N’Microsoft.SystemCenter.VirtualMachineManager.2012.VirtualMachine.PercentCPU’ , 

                                                            N’Microsoft.SystemCenter.VirtualMachineManager.2012.VirtualMachine.Memory’ ) ) 

                            GROUP  BY vPerformanceRuleInstance.InstanceName, 




                        ) AS Entry 

                        GROUP  BY 



                        ) AS perfTable 

        ON vME.ManagedEntityRowId = perfTable.ManagedEntityRowId 


    INNER JOIN  /* SubQuery 2 – Fetch "Owner" property of a VM */ 





                            MAX(ToDateTime) AS  LatestDate 


                           [vManagedEntityPropertySet] AS vMEPS, 

                           vManagedEntityTypeProperty AS vMETP, 

                           vManagedEntityType AS vMET 


                           vMEPS.PropertyGuid = vMETP.PropertyGuid 

                           AND vMETP.PropertySystemName = ‘Owner’ 

                           AND vMET.ManagedEntityTypeSystemName = N’Microsoft.SystemCenter.VirtualMachineManager.2012.VirtualMachine’ 

                           AND [PropertyValue] !=  

                        GROUP  BY 


                            [PropertyValue]) AS vOwner

                        ON vME.ManagedEntityRowId = vOwner.ManagedEntityRowId 


    INNER JOIN  /* Subquery 3- Fetch clouds for VM using ‘PrivateCloudContainsVirtualMachine’ relationship  */ 




                        vManagedEntity.DisplayName AS CloudName 



                            SELECT vRelationship.TargetManagedEntityRowId, 


                            FROM   vRelationship 

                            INNER JOIN 


                            ON vRelationshipProperty.RelationshipRowId =  vRelationship.RelationshipRowId 

                            INNER JOIN 


                            ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId 

                                AND vRelationshipProperty.ToDateTime IS NULL 

                            WHERE  vRelationshipType.RelationshipTypeSystemName = N’Microsoft.SystemCenter.VirtualMachineManager.PrivateCloudContainsVirtualMachine’

                        ) AS CloudVM 

                    INNER JOIN 


                    ON vManagedEntity.ManagedEntityRowId = CloudVM.SourceManagedEntityRowId

               ) AS CloudRelation 

    ON vME.ManagedEntityRowId = CloudRelation.TargetManagedEntityRowId 


    LEFT JOIN /* Subquery 4 – Fetch Service for VM using 2 relationships */

              /* LEFT JOIN will return VMs even if they do not belong to any service*/



                            vManagedEntity.DisplayName              AS ServiceName, 

                            vRelationship.SourceManagedEntityRowId  AS ServiceId, 

                            vRelationship.TargetManagedEntityRowId  AS TierId, 

                            TierVMRelation.TargetManagedEntityRowId AS VMId 




                        INNER JOIN vRelationshipType 

                        ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId 


                        INNER JOIN 








                                INNER JOIN vRelationshipType 

                                ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId 

                                WHERE  vRelationshipType.RelationshipTypeSystemName = N’Microsoft.SystemCenter.VirtualMachineManager.2012.ComputerTierContainsVirtualMachine’

                            ) AS TierVMRelation 

                        ON vRelationship.TargetManagedEntityRowId = TierVMRelation.SourceManagedEntityRowId 


                        INNER JOIN vManagedEntity 

                        ON vManagedEntity.ManagedEntityRowId = vRelationship.SourceManagedEntityRowId 

                        WHERE  vRelationshipType.RelationshipTypeSystemName = N’Microsoft.SystemCenter.VirtualMachineManager.2012.ServiceHostsComputerTier’

                   ) AS ServiceVMRelation 

                   ON vME.ManagedEntityRowId = ServiceVMRelation.VMId 


    ORDER  BY 

        CloudName DESC, 

        ServiceName DESC, 




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..

Thank you!

Chetan Gangwar | Developer | MSFT
Chaitanya Garikiparthi | Program Manager | MSFT

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

App-V Team blog:
ConfigMgr Support Team blog:
DPM Team blog:
MED-V Team blog:
Orchestrator Support Team blog:
Operations Manager Team blog:
SCVMM Team blog:
Server App-V Team blog:
Service Manager Team blog:
System Center Essentials Team blog:
WSUS Support Team blog:

The Forefront Server Protection blog:
The Forefront Endpoint Security blog :
The Forefront Identity Manager blog :
The Forefront TMG blog:
The Forefront UAG blog: