Step 2. Modify Sample SQL Statements

In this article

    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

     

    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

     

    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 alist of performance collection rules and management packs in which these rules are included.

    The MPs and Rules sample query

     

    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

     

    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

     

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

    1. Edit the query statement in the SQL statement field.
    2. Click Save.