Virtual Machine Performance Report
| Accessibility | Public |
| Visible | False |
Home<Report ID="Virtual_Machine_Performance" Accessibility="Public" Visible="false">
<Definition>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataWarehouseMain">
<DataSourceReference>Data Warehouse Main</DataSourceReference>
<rd:DataSourceID>e4235c51-407f-4065-8519-a1e57374bc45</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>0.5in</BottomMargin>
<RightMargin>0.5in</RightMargin>
<PageWidth>11in</PageWidth>
<ReportParameters>
<ReportParameter Name="StartDate1">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=DateAdd(DateInterval.Month, -1, Today())</Value>
</Values>
</DefaultValue>
<Prompt>StartDate1</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate1">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today()</Value>
</Values>
</DefaultValue>
<Prompt>EndDate1</Prompt>
</ReportParameter>
<ReportParameter Name="VMTypeID">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>VMTypeId</DataSetName>
<ValueField>typeid</ValueField>
</DataSetReference>
</DefaultValue>
<ValidValues>
<DataSetReference>
<DataSetName>VMTypeId</DataSetName>
<ValueField>typeid</ValueField>
<LabelField>typeid</LabelField>
</DataSetReference>
</ValidValues>
<Hidden>true</Hidden>
</ReportParameter>
<ReportParameter Name="HostTypeID">
<DataType>String</DataType>
<Nullable>true</Nullable>
<Prompt>HostTypeID</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
<ReportParameter Name="Hosts">
<DataType>String</DataType>
<Prompt>Hosts</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox2">
<Left>0.125in</Left>
<Top>0.5in</Top>
<ZIndex>4</ZIndex>
<Width>9.625in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="CPU in Use by VMs Compared with CPU Utilized by Host"</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.125in</Left>
<Top>3.5in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Width>9.625in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontWeight>700</FontWeight>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Memory in Use by VMs Compared with Available Memory on Host"</Value>
</Textbox>
<Chart Name="chart1">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title>
<Style>
<FontWeight>700</FontWeight>
<FontSize>12pt</FontSize>
</Style>
</Title>
<NoRows>No Data</NoRows>
<Height>2.625in</Height>
<CategoryAxis>
<Axis>
<Title/>
<Style/>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>VMCPUPerf</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart1_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!DisplayName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label/>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.75in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title/>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<ZIndex>2</ZIndex>
<Type>Line</Type>
<Width>9.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!DateTime.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label/>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!AVG1.Value</Value>
</DataValue>
</DataValues>
<DataLabel/>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
<Textbox Name="textboxReportTitle_tmp">
<Left>0.125in</Left>
<Top>0.125in</Top>
<ZIndex>1</ZIndex>
<Width>9.625in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>0.25pt</Default>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundImage>
<Value>/Microsoft Operations Manager Reporting/Images/9Inch_Banner.png</Value>
<Source>External</Source>
<BackgroundRepeat>NoRepeat</BackgroundRepeat>
</BackgroundImage>
<FontFamily>Verdana</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<Color>#000000</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Resource Utilization by Virtual Machines on Host: " &Parameters!Hosts.Value</Value>
</Textbox>
<Chart Name="chart2">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title>
<Style>
<FontWeight>700</FontWeight>
<FontSize>12pt</FontSize>
</Style>
</Title>
<NoRows>No Data</NoRows>
<Height>2.625in</Height>
<CategoryAxis>
<Axis>
<Title/>
<Style/>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>VMMemPerf</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<DynamicSeries>
<Grouping Name="chart2_SeriesGroup1">
<GroupExpressions>
<GroupExpression>=Fields!DisplayName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label/>
</DynamicSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>3.75in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title/>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Line</Type>
<Width>9.625in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart2_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!DateTime.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label/>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Fields!AVG1.Value</Value>
</DataValue>
</DataValues>
<DataLabel/>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>6.5in</Height>
</Body>
<rd:ReportID>717480b3-f4f4-4581-a9e8-ffc0df38704d</rd:ReportID>
<LeftMargin>0.5in</LeftMargin>
<DataSets>
<DataSet Name="VMUtilization">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>
SELECT Perf.Path, Perf.ManagedEntityRowId, Perf.ManagedEntityTypeRowId, Perf.CPUAvg1, Perf.CPUMax1,
Perf.DiskSpaceAvg1, Perf.RAMAvg1, Properties.Name, Properties.RAM, Properties.HDDSize, Properties.HostName
FROM (SELECT Path, ManagedEntityRowId, ManagedEntityTypeRowId,
MAX(CASE WHEN RName = N'Microsoft.Virtualization.VirtualServer.VM_Cpu_Utilization.Rule' THEN Entry.AVG1
END) AS CPUAvg1,
MAX(CASE WHEN RName = N'Microsoft.Virtualization.VirtualServer.VM_Cpu_Utilization.Rule' THEN Entry.MAX1
END) AS CPUMax1,
MAX(CASE WHEN RName = N'Microsoft.Virtualization.VirtualServer.VM_Disk_Space_Used.Rule' THEN Entry.AVG1
END) / (1024*1024) AS DiskSpaceAvg1,
MAX(CASE WHEN RName = N'Microsoft.Virtualization.VirtualServer.VM_RAM_Used.Rule' THEN Entry.AVG1
END) / (1024*1024) AS RAMAvg1
FROM (SELECT vRule.RuleSystemName AS RName, vManagedEntity.Path,
AVG(Perf.vPerfHourly.AverageValue) AS AVG1,
MAX(Perf.vPerfHourly.MaxValue) AS MAX1, vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityTypeRowId --, Host.FullName
FROM Perf.vPerfHourly INNER JOIN
vPerformanceRuleInstance ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON
vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfHourly.DateTime >= @StartDate1) AND (Perf.vPerfHourly.DateTime <= @EndDate1) AND
(vManagedEntity.ManagedEntityTypeRowId IN
(SELECT ManagedEntityTypeRowId
FROM dbo.ManagedEntityDerivedTypeHierarchy(@VMTypeID, 2) AS Base)) AND
(vRule.RuleSystemName IN (N'Microsoft.Virtualization.VirtualServer.VM_Cpu_Utilization.Rule',
N'Microsoft.Virtualization.VirtualServer.VM_Disk_Space_Used.Rule',
N'Microsoft.Virtualization.VirtualServer.VM_RAM_Used.Rule') )
GROUP BY vManagedEntity.Path, vPerformanceRuleInstance.InstanceName, vManagedEntity.ManagedEntityRowId,
vRule.RuleSystemName, vManagedEntity.ManagedEntityTypeRowId) AS Entry
GROUP BY Path, ManagedEntityRowId, ManagedEntityTypeRowId) AS Perf INNER JOIN
(SELECT vManagedEntity_1.Name, vManagedEntity_1.ManagedEntityRowId, vManagedEntity_1.ManagedEntityTypeRowId, Host.FullName as HostName,
MAX(CASE WHEN vManagedEntityTypeProperty.PropertySystemName = 'Memory' THEN vManagedEntityPropertySet.PropertyValue END)
AS RAM,
MAX(CASE WHEN vManagedEntityTypeProperty.PropertySystemName = 'HardDiskTotalSize' THEN vManagedEntityPropertySet.PropertyValue END) AS HDDSize
FROM vManagedEntityPropertySet INNER JOIN
vManagedEntity AS vManagedEntity_1 ON
vManagedEntityPropertySet.ManagedEntityRowId = vManagedEntity_1.ManagedEntityRowId INNER JOIN
vManagedEntityTypeProperty ON vManagedEntityPropertySet.PropertyGuid = vManagedEntityTypeProperty.PropertyGuid INNER JOIN
vRelationship ON vRelationship.TargetManagedEntityRowId = vManagedEntity_1.ManagedEntityRowid inner join
vManagedEntity AS Host ON vRelationship.SourceManagedEntityRowId = Host.ManagedEntityRowId inner join
vRelationshipType On vRelationshipType.Relationshiptyperowid = vRelationship.Relationshiptyperowid
WHERE (vManagedEntity_1.ManagedEntityTypeRowId IN
(SELECT ManagedEntityTypeRowId
FROM dbo.ManagedEntityDerivedTypeHierarchy(@VMTypeID, 2) AS ManagedEntityDerivedTypeHierarchy_1))
AND vRelationshipType.RelationshipTypeSystemName = N'Microsoft.Virtualization.VirtualServer.2005R2.VMHostHostsVirtualMachine'
GROUP BY vManagedEntity_1.Name, vManagedEntity_1.ManagedEntityRowId, vManagedEntity_1.ManagedEntityTypeRowId, Host.FullName) AS Properties ON
Properties.ManagedEntityRowId = Perf.ManagedEntityRowId
</CommandText>
<QueryParameters>
<QueryParameter Name="@StartDate1">
<Value>=Parameters!StartDate1.Value</Value>
</QueryParameter>
<QueryParameter Name="@EndDate1">
<Value>=Parameters!EndDate1.Value</Value>
</QueryParameter>
<QueryParameter Name="@VMTypeID">
<Value>=Parameters!VMTypeID.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="Path">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Path</DataField>
</Field>
<Field Name="ManagedEntityRowId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityRowId</DataField>
</Field>
<Field Name="ManagedEntityTypeRowId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityTypeRowId</DataField>
</Field>
<Field Name="CPUAvg1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>CPUAvg1</DataField>
</Field>
<Field Name="CPUMax1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>CPUMax1</DataField>
</Field>
<Field Name="DiskSpaceAvg1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>DiskSpaceAvg1</DataField>
</Field>
<Field Name="RAMAvg1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>RAMAvg1</DataField>
</Field>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
<Field Name="RAM">
<rd:TypeName>System.String</rd:TypeName>
<DataField>RAM</DataField>
</Field>
<Field Name="HDDSize">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HDDSize</DataField>
</Field>
<Field Name="HostName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HostName</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="HostGroups">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>
select ME.Name, ME.ManagedEntityrowid from vManagedEntityType CG inner join vManagedEntity ME
on ME.ManagedEntityTyperowId = CG.ManagedEntityTyperowId
where CG.ManagedEntityTypeSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.2007.HostGroup'
</CommandText>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
<Field Name="ManagedEntityrowid">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityrowid</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="VMTypeId">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>
(select top 1 cg.ManagedEntitytyperowid as typeid
from vManagedEntityType CG
where CG.ManagedEntityTypeSystemName = N'Microsoft.Virtualization.VirtualServer.2005R2.VirtualMachine')
</CommandText>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="typeid">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>typeid</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="VMCPUPerf">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>
SELECT distinct vRule.RuleSystemName AS RName, vManagedEntity.Path, vManagedEntity.Name, vManagedEntity.DisplayName, Perf.vPerfHourly.DateTime,
Perf.vPerfHourly.AverageValue AS AVG1, Perf.vPerfHourly.MinValue AS MIN1,
Perf.vPerfHourly.MaxValue AS MAX1, vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityTypeRowId
FROM Perf.vPerfHourly INNER JOIN
vPerformanceRuleInstance ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON
vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfHourly.DateTime >= @StartDate1) AND (Perf.vPerfHourly.DateTime <= @EndDate1) AND
(vManagedEntity.ManagedEntityRowId IN (select vm.managedentityrowid
from dbo.vManagedEntity as host inner join
dbo.vRelationship as relation on host.ManagedEntityrowid = relation.SourceManagedEntityRowId inner join
dbo.vManagedEntity as vm on vm.ManagedEntityrowid = relation.TargetManagedEntityRowid inner join
dbo.vRelationshipType as relationType on relationType.RelationshipTypeRowId = relation.RelationshipTypeRowId
where relationType.RelationshipTypeSystemName = N'Microsoft.Virtualization.VirtualServer.2005R2.VMHostHostsVirtualMachine' and
host.path in (@Hosts)) OR vManagedEntity.ManagedEntityRowId IN (select host.managedentityRowid
from vManagedEntity as host inner join
vManagedEntityType as htype on host.ManagedEntityTyperowid = htype.ManagedEntityTyperowid
where
htype.ManagedEntityTypeSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.2007.ManagedHost'
and host.Path IN (@Hosts))
) AND
(vRule.RuleSystemName IN (N'Microsoft.Virtualization.VirtualServer.VM_Cpu_Utilization.Rule',
N'Microsoft.SystemCenter.VirtualMachineManager.ManagedHost.PercentProcessorTime.Collection'))
order by Perf.vPerfHourly.DateTime
</CommandText>
<QueryParameters>
<QueryParameter Name="@StartDate1">
<Value>=Parameters!StartDate1.Value</Value>
</QueryParameter>
<QueryParameter Name="@EndDate1">
<Value>=Parameters!EndDate1.Value</Value>
</QueryParameter>
<QueryParameter Name="@Hosts">
<Value>=Parameters!Hosts.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="RName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>RName</DataField>
</Field>
<Field Name="Path">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Path</DataField>
</Field>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
<Field Name="DisplayName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DisplayName</DataField>
</Field>
<Field Name="DateTime">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateTime</DataField>
</Field>
<Field Name="AVG1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>AVG1</DataField>
</Field>
<Field Name="MIN1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>MIN1</DataField>
</Field>
<Field Name="MAX1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>MAX1</DataField>
</Field>
<Field Name="InstanceName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>InstanceName</DataField>
</Field>
<Field Name="ManagedEntityRowId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityRowId</DataField>
</Field>
<Field Name="ManagedEntityTypeRowId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityTypeRowId</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="VMMemPerf">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>
SELECT distinct vRule.RuleSystemName AS RName, vManagedEntity.Path, vManagedEntity.Name, vManagedEntity.DisplayName,Perf.vPerfHourly.DateTime,
(CASE WHEN vRule.RuleSystemName = N'Microsoft.Virtualization.VirtualServer.VM_RAM_Used.Rule' THEN Perf.vPerfHourly.AverageValue / (1024 * 1024)
Else Perf.vPerfHourly.AverageValue END) as avg1, Perf.vPerfHourly.MinValue AS MIN1,
Perf.vPerfHourly.MaxValue AS MAX1, vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityTypeRowId
FROM Perf.vPerfHourly INNER JOIN
vPerformanceRuleInstance ON
vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfHourly.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON
vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON
vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId
WHERE (Perf.vPerfHourly.DateTime >= @StartDate1) AND (Perf.vPerfHourly.DateTime <= @EndDate1) AND
(vManagedEntity.ManagedEntityRowId IN (select vm.managedentityrowid
from dbo.vManagedEntity as host inner join
dbo.vRelationship as relation on host.ManagedEntityrowid = relation.SourceManagedEntityRowId inner join
dbo.vManagedEntity as vm on vm.ManagedEntityrowid = relation.TargetManagedEntityRowid inner join
dbo.vRelationshipType as relationType on relationType.RelationshipTypeRowId = relation.RelationshipTypeRowId
where relationType.RelationshipTypeSystemName = N'Microsoft.Virtualization.VirtualServer.2005R2.VMHostHostsVirtualMachine' and
host.path in (@Hosts)) OR vManagedEntity.ManagedEntityRowId IN (select host.managedentityRowid
from vManagedEntity as host inner join
vManagedEntityType as htype on host.ManagedEntityTyperowid = htype.ManagedEntityTyperowid
where
htype.ManagedEntityTypeSystemName = N'Microsoft.SystemCenter.VirtualMachineManager.2007.ManagedHost'
and host.Path IN (@Hosts))
) AND
(vRule.RuleSystemName IN (N'Microsoft.Virtualization.VirtualServer.VM_RAM_Used.Rule',
N'Microsoft.SystemCenter.VirtualMachineManager.ManagedHost.MemoryAvailableMBytes.Collection'))
order by Perf.vPerfHourly.DateTime
</CommandText>
<QueryParameters>
<QueryParameter Name="@StartDate1">
<Value>=Parameters!StartDate1.Value</Value>
</QueryParameter>
<QueryParameter Name="@EndDate1">
<Value>=Parameters!EndDate1.Value</Value>
</QueryParameter>
<QueryParameter Name="@Hosts">
<Value>=Parameters!Hosts.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>DataWarehouseMain</DataSourceName>
</Query>
<Fields>
<Field Name="RName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>RName</DataField>
</Field>
<Field Name="Path">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Path</DataField>
</Field>
<Field Name="Name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Name</DataField>
</Field>
<Field Name="DisplayName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>DisplayName</DataField>
</Field>
<Field Name="DateTime">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>DateTime</DataField>
</Field>
<Field Name="AVG1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>avg1</DataField>
</Field>
<Field Name="MIN1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>MIN1</DataField>
</Field>
<Field Name="MAX1">
<rd:TypeName>System.Double</rd:TypeName>
<DataField>MAX1</DataField>
</Field>
<Field Name="InstanceName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>InstanceName</DataField>
</Field>
<Field Name="ManagedEntityRowId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityRowId</DataField>
</Field>
<Field Name="ManagedEntityTypeRowId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagedEntityTypeRowId</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code>
public function FormatNumber(fieldValue, formatString) as string
if (fieldValue is Nothing)
FormatNumber = "Not Available"
else
FormatNumber = Format(fieldValue,formatString)
end if
end function
</Code>
<Width>9.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>0.5in</TopMargin>
<PageHeight>8.5in</PageHeight>
</Report>
</Definition>
<ReportStrings>
<ReportString ID="R.Title"/>
<ReportString ID="R.Description"/>
<ReportString ID="R.DescriptionTitle"/>
<ReportString ID="R.PageFormat"/>
<ReportString ID="R.TimeZoneFormat"/>
<ReportString ID="R.GeneratedTimeLabel"/>
<ReportString ID="R.StartEndTimeLabel"/>
<ReportString ID="R.StartEndTimeFormat"/>
<ReportString ID="R.BusinessTimeFormat"/>
<ReportString ID="R.ObjectsLabel"/>
<ReportString ID="R.ObjectsFormat"/>
<ReportString ID="R.ObjectFormatLine1"/>
<ReportString ID="R.ObjectFormatLine2"/>
<ReportString ID="R.ObjectFormatLine2Path"/>
<ReportString ID="R.MonitorLabel"/>
<ReportString ID="R.UpTimeFormat"/>
<ReportString ID="R.DownTimeFormat"/>
<ReportString ID="RT.Column.ObjectName"/>
<ReportString ID="RT.Column.UptimeProc"/>
<ReportString ID="RT.Column.DowntimeProc"/>
<ReportString ID="RT.Column.Uptime"/>
<ReportString ID="RT.Column.Downtime"/>
<ReportString ID="RT.Legend.Title"/>
<ReportString ID="RT.Legend.Red"/>
<ReportString ID="RT.Legend.Green"/>
<ReportString ID="RT.Legend.Yellow"/>
<ReportString ID="RT.Legend.White"/>
<ReportString ID="RT.Legend.Gray"/>
<ReportString ID="RT.Legend.Black"/>
<ReportString ID="RT.Legend.Blue"/>
<ReportString ID="RT.Detail.ViewTitle"/>
<ReportString ID="RT.Detail.StateViewLabel"/>
<ReportString ID="RT.Detail.EventViewLabel"/>
<ReportString ID="RT.Detail.HealthViewLabel"/>
<ReportString ID="RT.Detail.ReportTitle"/>
<ReportString ID="RT.Detail.TimeDrillLabel"/>
<ReportString ID="RT.Detail.ChangesDrillLabel"/>
<ReportString ID="RT.Detail.MonitorDrillLabel"/>
<ReportString ID="RT.Detail.DowntimeDrillLabel"/>
</ReportStrings>
</Report>