Reporting in System Center 2012 Virtual Machine Manager (Part 2 of 3)
Published Feb 15 2019 01:00 PM 373 Views
First published on TECHNET on May 14, 2012

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.

Operations Manager publishes its Data Warehouse Schema and here is some good content on how to build custom reports. http://technet.microsoft.com/en-us/library/gg508710 .

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 @GBFactor REAL

DECLARE @precision INT

DECLARE @daysHistory INT

DECLARE @ReportStartTime DATETIME

DECLARE @ReportEndTime DATETIME

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,

ServiceVMRelation.ServiceName,

vME.DisplayName                     AS VM,

vOwner.PropertyValue                AS VMOwner,

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

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

perfTable.CollectionDate

FROM   vManagedEntity AS vME

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

(

SELECT

ManagedEntityRowId,

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,

CollectionDate

FROM

(

SELECT

vRule.RuleSystemName AS RName,

AVG(perf.vPerfDaily.AverageValue) AS AVG1,

vManagedEntity.ManagedEntityRowId,

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

FROM   perf.vPerfDaily,

vPerformanceRuleInstance,

vManagedEntity,

vRule

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,

vManagedEntity.ManagedEntityRowId,

vRule.RuleSystemName,

perf.vPerfDaily.DateTime

) AS Entry

GROUP  BY

ManagedEntityRowId,

CollectionDate

) AS perfTable

ON vME.ManagedEntityRowId = perfTable.ManagedEntityRowId

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

(

SELECT

[ManagedEntityRowId],

[PropertyValue],

MAX(ToDateTime) AS  LatestDate

FROM

[vManagedEntityPropertySet] AS vMEPS,

vManagedEntityTypeProperty AS vMETP,

vManagedEntityType AS vMET

WHERE

vMEPS.PropertyGuid = vMETP.PropertyGuid

AND vMETP.PropertySystemName = 'Owner'

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

AND [PropertyValue] != ''

GROUP  BY

[ManagedEntityRowId],

[PropertyValue]) AS vOwner

ON vME.ManagedEntityRowId = vOwner.ManagedEntityRowId

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

(

SELECT

CloudVM.TargetManagedEntityRowId,

vManagedEntity.DisplayName AS CloudName

FROM

(

SELECT vRelationship.TargetManagedEntityRowId,

vRelationship.SourceManagedEntityRowId

FROM   vRelationship

INNER JOIN

vRelationshipProperty

ON vRelationshipProperty.RelationshipRowId =  vRelationship.RelationshipRowId

INNER JOIN

vRelationshipType

ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId

AND vRelationshipProperty.ToDateTime IS NULL

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

) AS CloudVM

INNER JOIN

vManagedEntity

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*/

(

SELECT

vManagedEntity.DisplayName              AS ServiceName,

vRelationship.SourceManagedEntityRowId  AS ServiceId,

vRelationship.TargetManagedEntityRowId  AS TierId,

TierVMRelation.TargetManagedEntityRowId AS VMId

FROM

vRelationship

INNER JOIN vRelationshipType

ON vRelationshipType.RelationshipTypeRowId = vRelationship.RelationshipTypeRowId

INNER JOIN

(

SELECT

vRelationship.TargetManagedEntityRowId,

vRelationship.SourceManagedEntityRowId

FROM

vRelationship

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,

vME.ManagedEntityRowId,

CollectionDate

____

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 :

App-V Team blog: http://blogs.technet.com/appv/
ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/
DPM Team blog: http://blogs.technet.com/dpm/
MED-V Team blog: http://blogs.technet.com/medv/
Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
Operations Manager Team blog: http://blogs.technet.com/momteam/
SCVMM Team blog: http://blogs.technet.com/scvmm
Server App-V Team blog: http://blogs.technet.com/b/serverappv
Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center Essentials Team blog: http://blogs.technet.com/b/systemcenteressentials
WSUS Support Team blog: http://blogs.technet.com/sus/

The Forefront Server Protection blog: http://blogs.technet.com/b/fss/
The Forefront Endpoint Security blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/

Version history
Last update:
‎Mar 11 2019 09:19 AM
Updated by: