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