Aggregate total for group of objects

[sdp]

Hi,

I initially tried doing this with VeeamOne and had a post over on the forum for it - http://forums.veeam.com/veeam-one-f28/s ... 23214.html - but it seems like it's not possible, so have turned my attention to SCOM and Veeam MP instead. I've spent the last few weeks trying to figure it out but I've hit a bit of a wall.

In summary what I am trying to achieve is to report on the daily IO usage for a group of virtual machines. Basically a sum total of the daily averages for a group of servers. So say I have 10 virtual machines in a group, I need to get an idea of the total amount of IO those servers use during a day. I think the best rule for this is "VM Storage Commands per Second", and I can get it to show me the data for individual machines, but I simply can't get it to show it as a total for a group. Ideally I would then have a report where I can report on multiple groups (clients in my case) and have an easy way to see total amount of IO per client over a period of time.

Any ideas on how we can achieve that? If it matters it's on a Hyper-V environment.

Thanks

[sergey.g]

Hello,

Sorry for a delay in our response,

We are currently working on an updated Resource Kit for our Management Packs and one of the items is going to be a Microsoft Excel PowerPivot example on how to extract data from SCOM DW to display it in any required shape or form. Right now it's designed for VMware data, but I'll try to come up with examples for Hyper-V too. Let me try some things, if it's relatevely easy I may describe the method here in the forum topic.

I'll update you soon.
Thanks.

[sdp]

Hi Sergey, That would be great, I'll keep an eye out for it.

[sergey.g]

Hi again,

Can't share all the details right now, but we are going to add more information and example on how to extract data collected by Veeam Management Packs.

The idea is similar to what is described in this blog post http://www.scom2k7.com/performance-repo ... ower-view/

But, we'll also provide more details for data discovered by our Management Packs.

For you I've created a couple of SQL queries which can be used for Hyper-V objects. You need to configure a spreadsheet according to the blog post mentioned above (create a PowerPivot connectin to your SCOM DW database) and then use the following queries to extract data which you requested:

Table for Hyper-V VMs with their basic properties (more properties could be added, but I wanted query to be relatively lightweight)

Code: Select all

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

This one I'm using to extract performance data from SCOM DW for past 7 days (I've modified it to extract daily performance rather than hourly like in the blog post example)

Code: Select all

SELECT     PERF.ManagedEntityRowId
,PR.RuleRowId
,PERF.DateTime
,PERF.AverageValue
,PERF.MinValue
,PERF.MaxValue
,PERF.SampleCount
,PR.ObjectName
,PR.CounterName
,PRI.InstanceName
FROM Perf.vPerfDaily PERF
INNER JOIN vPerformanceRuleInstance PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId
INNER JOIN vPerformanceRule PR ON PR.RuleRowId = PRI.RuleRowId
WHERE PERF.DateTime > (GETUTCDATE() - 7)

Then you just need to specify relationship (It's in diagram mode in PowerPivot), from Performance table ManagedEntityRowID to VMs with Properties table ManagedEntityRowID



Then just click in the PowerPivot to create a Pivot table (use flattened table to have VM properties side by side)



Then add necessary fields as on my screenshot below



And you should see something like this (make sure to filter out Hyper-V Virtual Machine class)



My example is not very interesting, commands per second is 0 because these VMs are doing nothing, but in your case you should get all IOPS for all VMs and then you can filter them by host name and even create subtotals for hostname field (I beleive this is what you are looking for), again sorry for bad example - I have only one hyper-v host in this lab.

Let me know if you have any questions, I'll be happy to help you with getting this data from SCOM DW.

[sdp]

Hi Sergey,

Apologies for not getting back to you sooner but I've had some other things to get off my plate. I've just picked this up again today and tried your example in Excel and it works perfectly! That is exactly what I am after, or at least it gives me the raw data to manipulate in Excel to get what I'm after.

Thanks again for your help on this - those SQL queries actually gives me a lot of other useful info as well.

Regards