Veeam Home | Support | Downloads
Veeam Management Pack 8.0 for System Center Resource Kit Guide

Veeam Management Pack Help Center  > Veeam MP Resource Kit Guide >  Visualizing Veeam MP Data Using Microsoft PowerPivot > Veeam VMware PowerPivot Example > Step 2. Modify Sample SQL Statements

Step 2. Modify Sample SQL Statements

Previous page Next page Print this Topic

Table of contents

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

noteNote

All sample queries below return data recorded to the Ops Mgr 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

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

Table of contents