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

 

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 a list 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 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

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