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

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

  • Comments 1
  • Likes

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

clip_image002[1]

clip_image004[1]

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:

clip_image006

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.

clip_image008

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:

clip_image010

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.

clip_image012

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.

clip_image013

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

clip_image015

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • No part 3?