---
title: "Step 2. Modify Sample SQL Statements"
description: "Each Veeam VMware PowerPivot Example spreadsheet displays data returned by sample SQL queries. You can edit the query statements to meet your specific needs: All sample queries below return data recorded to the OpsMgr Data Warehouse during the..."
canonical: "https://helpcenter.veeam.com/docs/mp/resource_kit/modify_sample_statements.html"
breadcrumb: "Veeam MP Resource Kit Guide > Visualizing Veeam MP Data Using Microsoft PowerPivot > Veeam VMware PowerPivot Example > Step 2. Modify Sample SQL Statements"
dateModified: "2026-09-10"
---

# Step 2. Modify Sample SQL Statements

Each Veeam VMware PowerPivot Example spreadsheet displays data returned by sample SQL queries. You can edit the query statements to meet your specific needs:

::: note

All sample queries below return data recorded to the OpsMgr Data Warehouse during the previous 7 days.

:::

1. Launch Microsoft Excel, open the **PowerPivot** tab and click **Manage**.
2. In the **PowerPivot for Excel – Veeam VMware PowerPivot Example** window, switch to a worksheet, open the **Design** tab and click **Table Properties**:

   - The query on the **Perf** worksheet returns changes in performance metric values and shows metric sample count, minimum, maximum and average values.

     [![The Perf sample query](images/perf_properties.png "The Perf sample query")](images/perf_properties.png){.toggle scale=65}

     ```
     SELECT     PERF.ManagedEntityRowId
     ,PR.RuleRowId
     ,PERF.DateTime
     ,PERF.AverageValue
     ,PERF.MinValue
     ,PERF.MaxValue
     ,PERF.SampleCount
     ,PR.ObjectName
     ,PR.CounterName
     ,PRI.InstanceName
     FROM Perf.vPerfHourly PERF
     INNER JOIN vPerformanceRuleInstance PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId
     INNER JOIN vPerformanceRule PR ON PR.RuleRowId = PRI.RuleRowId
     WHERE PERF.DateTime > (GETUTCDATE() - 7)
     ```

   - The query on the **Entities** worksheet returns a list of monitored objects and shows classes to which these objects belong. 

     [![The Entities sample query](images/entities_properties.png "The Entities sample query")](images/entities_properties.png){.toggle scale=65}

     ```
     select distinct
     ME.ManagedEntityRowId
     ,ME.Name
     ,ME.DisplayName
     ,ME.Path
     ,MET.ManagedEntityTypeDefaultName
     from Perf.vPerfHourly PERF
     inner join vManagedEntity ME on ME.ManagedEntityRowId = PERF.ManagedEntityRowId
     inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
     inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
     where PERF.DateTime > (GETUTCDATE() - 7)
     order by MET.ManagedEntityTypeDefaultName
     ```

   - The query on the **MPs and Rules** worksheet returns a list of performance collection rules and management packs in which these rules are included.

     [![The MPs and Rules sample query](images/mps_rules_properties.png "The MPs and Rules sample query")](images/mps_rules_properties.png){.toggle scale=65}

     ```
     SELECT distinct RU.RuleRowId
     ,MP.ManagementPackDefaultName
     ,MP.ManagementPackSystemName
     ,RU.RuleDefaultName
     FROM vManagementPack MP
     inner join vRule RU on RU.ManagementPackRowId = MP.ManagementPackRowId
     inner join vPerformanceRule PR on PR.RuleRowId = RU.RuleRowId
     where PR.LastReceivedDateTime > (GETUTCDATE() - 7)
     ```

   - The query on the **Properties** worksheet returns a list of the latest object property values, such as the number of vCPUs or the amount of allocated memory.

     [![The Properties sample query](images/properties_properties.png "The Properties sample query")](images/properties_properties.png){.toggle scale=65}

     ```
     SELECT 
        p.ManagedEntityRowId,
        d.DisplayName,
        vmetp.PropertyGuid,
        vmetp.PropertySystemName,
        PropertyValue = p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp.PropertyGuid")]]/text())[1]','nvarchar(256)'),
        p.FromDateTime,
        p.ToDateTime
       FROM
        [OperationsManagerDW].[dbo].[vManagedEntityProperty] as p
        INNER JOIN [OperationsManagerDW].[dbo].[vManagedEntity] d ON p.ManagedEntityRowId = d.ManagedEntityRowId
        Inner join [OperationsManagerDW].[dbo].[vManagedEntityTypeProperty] vmetp on vmetp.ManagedEntityTypeRowId=d.ManagedEntityTypeRowId
        Where p.ToDateTime IS NULL
     ```

   - The query on the **DatastoreswithDiskSize** worksheet returns a list of monitored datastores and shows their capacity.

     [![The DatastoreswithDiskSize sample query](images/datastoreswithdisksize_properties.png "The DatastoreswithDiskSize sample query")](images/datastoreswithdisksize_properties.png){.toggle scale=65}

     ```
     select distinct
     ME.ManagedEntityRowId
     ,ME.Name
     ,ME.DisplayName
     ,ME.Path
     ,MET.ManagedEntityTypeDefaultName
     ,convert(float,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp.PropertyGuid")]]/text())[1]','nvarchar(256)'))  as DiskSize
     from vManagedEntity ME 
     inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
     inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
     Inner join vManagedEntityTypeProperty vmetp on vmetp.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId
     inner join vManagedEntityProperty p on p.ManagedEntityRowId=ME.ManagedEntityRowId and vmetp.PropertySystemname like 'diskSize' and p.ToDateTime is Null
     order by MET.ManagedEntityTypeDefaultName
     ```

   - The advanced query returns a list of monitored Hyper-V VMs and shows their most important properties.

     ```
     select distinct
     ME.ManagedEntityRowId
     ,ME.Name
     ,ME.DisplayName
     ,ME.Path
     ,MET.ManagedEntityTypeDefaultName
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp.PropertyGuid")]]/text())[1]','nvarchar(256)')  as HostName
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp2.PropertyGuid")]]/text())[1]','nvarchar(256)')  as cpucount
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp3.PropertyGuid")]]/text())[1]','nvarchar(256)')  as guestHostName
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp4.PropertyGuid")]]/text())[1]','nvarchar(256)')  as clustername
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp5.PropertyGuid")]]/text())[1]','nvarchar(256)')  as MaxMem
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp6.PropertyGuid")]]/text())[1]','nvarchar(256)')  as DiskAllocationGB
     ,p.PropertyXml.value('(/Root/Property[@Guid[. = sql:column("vmetp7.PropertyGuid")]]/text())[1]','nvarchar(256)')  as UsesStorage
     from vManagedEntity ME 
     inner join vManagedEntityManagementGroup MEMG on MEMG.ManagedEntityRowId = ME.ManagedEntityRowId
     inner join vManagedEntityType MET on MET.ManagedEntityTypeRowId = ME.ManagedEntityTypeRowId
     inner join vManagedEntityProperty p on p.ManagedEntityRowId=ME.ManagedEntityRowId and p.ToDateTime is Null
     Inner join vManagedEntityTypeProperty vmetp on vmetp.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp.PropertySystemname like 'hostname'
     Inner join vManagedEntityTypeProperty vmetp2 on vmetp2.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp2.PropertySystemname like 'cpucount'
     Inner join vManagedEntityTypeProperty vmetp3 on vmetp3.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp3.PropertySystemname like 'guestHostName'
     Inner join vManagedEntityTypeProperty vmetp4 on vmetp4.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp4.PropertySystemname like 'clustername'
     Inner join vManagedEntityTypeProperty vmetp5 on vmetp5.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp5.PropertySystemname like 'maxmem'
     Inner join vManagedEntityTypeProperty vmetp6 on vmetp6.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp6.PropertySystemname like 'DiskAllocationGB'
     Inner join vManagedEntityTypeProperty vmetp7 on vmetp7.ManagedEntityTypeRowId=ME.ManagedEntityTypeRowId and vmetp7.PropertySystemname like 'UsesStorage'
     order by MET.ManagedEntityTypeDefaultName
     ```

3. Edit the query statement in the **SQL statement** field. 
4. Click **Save**.