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. |
- Launch Microsoft Excel, open the PowerPivot tab and click Manage.
- 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 a list 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 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 |
- Edit the query statement in the SQL statement field.
- Click Save.