<Report ID="Microsoft.Windows.DHCPServer.Library.Scopes.Report" Accessibility="Public" Target="Microsoft.Windows.DHCPServer.Library.Server" Visible="true">
<Dependencies>
<ReportParameterControl>ReportLibrary!Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.TextBox</ReportParameterControl>
<ReportParameterControl>ReportLibrary!Microsoft.SystemCenter.DataWarehouse.Report.ParameterControl.RelativeDateTimePicker</ReportParameterControl>
</Dependencies>
<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">
<rd:DataSourceID>e4235c51-407f-4065-8519-a1e57374bc45</rd:DataSourceID>
<DataSourceReference>Data Warehouse Main</DataSourceReference>
</DataSource>
</DataSources>
<InteractiveHeight>11in</InteractiveHeight>
<ReportParameters>
<ReportParameter Name="FilterOption">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>=0</Value>
</Values>
</DefaultValue>
<Prompt>Filter Option</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
<Label>All</Label>
</ParameterValue>
<ParameterValue>
<Value>1</Value>
<Label>Only DHCP 2003</Label>
</ParameterValue>
<ParameterValue>
<Value>2</Value>
<Label>Only DHCP 2008</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="SuperScopeName">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>SuperScopeList</DataSetName>
<ValueField>SuperScopeName</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Super Scope Name</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>SuperScopeList</DataSetName>
<ValueField>SuperScopeName</ValueField>
<LabelField>SuperScopeName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ScopeName">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>ScopeList</DataSetName>
<ValueField>ScopeName</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>Scope Name</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>ScopeList</DataSetName>
<ValueField>ScopeName</ValueField>
<LabelField>ScopeName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="DateTimeToReport">
<DataType>DateTime</DataType>
<Prompt>DateTimeToReport</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<BottomMargin>1in</BottomMargin>
<rd:ReportID>f84a272f-3064-41aa-9692-765994f8a9c9</rd:ReportID>
<DataSets>
<DataSet Name="SuperScopeList">
<Fields>
<Field Name="SuperScopeId">
<DataField>SuperScopeId</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="SuperScopeName">
<DataField>SuperScopeName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataWarehouseMain</DataSourceName>
<CommandText>
Select '0' as SuperScopeId,
'All' as SuperScopeName
Union
Select distinct RIn.PerformanceRuleInstanceRowId as SuperScopeId,
RIn.InstanceName as SuperScopeName
From vPerformanceRuleInstance RIn (NoLock)
Inner Join Perf.vPerfRaw PRW (NoLock)
On PRW.PerformanceRuleInstanceRowId = RIn.PerformanceRuleInstanceRowId
Inner Join Managedentity Met (NoLock)
On Met.ManagedEntityRowId = PRW.ManagedEntityRowId
Inner Join Managedentity TEN (NoLock)
On Met.TopLevelHostManagedEntityRowId = TEN.ManagedEntityRowId
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectSuperscopeAddressesInUse'
)
)
And TEN.Name in
(
Select Distinct MgEn.Name
From ManagedEntity MgEn (NoLock)
Inner Join vManagedEntityPropertySet MPR (NoLock)
On MPR.ManagedEntityRowId = MgEn.ManagedEntityRowId
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
WHERE
(
(
@FilterOption = 0 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
And MPR.ToDateTime is null
)
) Or
(
@FilterOption = 1 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
And MPR.ToDateTime is null
)
AND MPR.ManagedEntityRowId not in
(
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
And MPR.ToDateTime is null
)
)
) Or
(
@FilterOption = 2 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
And MPR.ToDateTime is null
)
)
)
) </CommandText>
<QueryParameters>
<QueryParameter Name="@FilterOption">
<Value>=Parameters!FilterOption.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="ScopeList">
<Fields>
<Field Name="ScopeId">
<DataField>ScopeId</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ScopeName">
<DataField>ScopeName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataWarehouseMain</DataSourceName>
<CommandText>
Select '0' as ScopeId,
'All' as ScopeName
Union
Select distinct RIn.PerformanceRuleInstanceRowId as ScopeId,
RIn.InstanceName as ScopeName
From vPerformanceRuleInstance RIn (NoLock)
Inner Join Perf.vPerfRaw PRW (NoLock)
On PRW.PerformanceRuleInstanceRowId = RIn.PerformanceRuleInstanceRowId
Inner Join Managedentity Met (NoLock)
On Met.ManagedEntityRowId = PRW.ManagedEntityRowId
Inner Join Managedentity TEN (NoLock)
On Met.TopLevelHostManagedEntityRowId = TEN.ManagedEntityRowId
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Scope.CollectScopeAddressesInUse'
)
)
And TEN.Name in
(
Select Distinct MgEn.Name
From ManagedEntity MgEn (NoLock)
Inner Join vManagedEntityPropertySet MPR (NoLock)
On MPR.ManagedEntityRowId = MgEn.ManagedEntityRowId
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
WHERE
(
(
@FilterOption = 0 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
And MPR.ToDateTime is null
)
) Or
(
@FilterOption = 1 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
And MPR.ToDateTime is null
)
AND MPR.ManagedEntityRowId not in
(
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
And MPR.ToDateTime is null
)
)
) Or
(
@FilterOption = 2 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
And MPR.ToDateTime is null
)
)
)
) </CommandText>
<QueryParameters>
<QueryParameter Name="@FilterOption">
<Value>=Parameters!FilterOption.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="SuperScopesInUse">
<Fields>
<Field Name="SuperScopesInUseCount">
<DataField>SuperScopesInUseCount</DataField>
<rd:TypeName>System.Double</rd:TypeName>
</Field>
<Field Name="SuperScopeName">
<DataField>SuperScopeName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DataDateTime">
<DataField>DataDateTime</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataWarehouseMain</DataSourceName>
<CommandText>
Select Prf.SampleValue As SuperScopesInUseCount,
Ins.InstanceName As SuperScopeName,
Max(Prf.[Datetime]) As DataDateTime
From Perf.vPerfRaw Prf (NoLock)
Inner Join
(
Select RIn.PerformanceRuleInstanceRowId,RIn.InstanceName
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectSuperscopeAddressesInUse'
)
)
) As Ins
On Prf.PerformanceRuleInstanceRowId = Ins.PerformanceRuleInstanceRowId
Inner Join
(
Select distinct RIn.PerformanceRuleInstanceRowId as SuperScopeId,
RIn.InstanceName as SuperScopeName
From vPerformanceRuleInstance RIn (NoLock)
Inner Join Perf.vPerfRaw PRW (NoLock)
On PRW.PerformanceRuleInstanceRowId = RIn.PerformanceRuleInstanceRowId
Inner Join Managedentity Met (NoLock)
On Met.ManagedEntityRowId = PRW.ManagedEntityRowId
Inner Join Managedentity TEN (NoLock)
On Met.TopLevelHostManagedEntityRowId = TEN.ManagedEntityRowId
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectSuperscopeAddressesInUse'
)
)
And TEN.Name in
(
Select Distinct MgEn.Name
From ManagedEntity MgEn (NoLock)
Inner Join vManagedEntityPropertySet MPR (NoLock)
On MPR.ManagedEntityRowId = MgEn.ManagedEntityRowId
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
WHERE
(
(
@FilterOption = 0 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
And MPR.ToDateTime is null
)
) Or
(
@FilterOption = 1 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
And MPR.ToDateTime is null
)
AND MPR.ManagedEntityRowId not in
(
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
And MPR.ToDateTime is null
)
)
) Or
(
@FilterOption = 2 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
And MPR.ToDateTime is null
)
)
)
)
) As FIL
On FIL.SuperScopeId = Prf.PerformanceRuleInstanceRowId
Where Prf.PerformanceRuleInstanceRowId in
(
Select RIn.PerformanceRuleInstanceRowId
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectSuperscopeAddressesInUse'
)
)
And (
(@SuperScopeName <> 'All' and RIn.InstanceName = @SuperScopeName) Or
(@SuperScopeName = 'All')
)
)
And DATEPART(year,Prf.[Datetime]) = DATEPART(year,@DateTimeToReport)
And DATEPART(month,Prf.[Datetime]) = DATEPART(month,@DateTimeToReport)
And DATEPART(day,Prf.[Datetime]) = DATEPART(day,@DateTimeToReport)
Group by Prf.PerformanceRuleInstanceRowId, Prf.SampleValue, Ins.InstanceName
Order by Ins.InstanceName </CommandText>
<QueryParameters>
<QueryParameter Name="@FilterOption">
<Value>=Parameters!FilterOption.Value</Value>
</QueryParameter>
<QueryParameter Name="@SuperScopeName">
<Value>=Parameters!SuperScopeName.Value</Value>
</QueryParameter>
<QueryParameter Name="@DateTimeToReport">
<Value>=Parameters!DateTimeToReport.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="SuperScopesFree">
<Fields>
<Field Name="SuperScopesFreeCount">
<DataField>SuperScopesFreeCount</DataField>
<rd:TypeName>System.Double</rd:TypeName>
</Field>
<Field Name="SuperScopeName">
<DataField>SuperScopeName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DataDateTime">
<DataField>DataDateTime</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataWarehouseMain</DataSourceName>
<CommandText>
Select Prf.SampleValue As SuperScopesFreeCount,
Ins.InstanceName As SuperScopeName,
Max(Prf.[Datetime]) As DataDateTime
From Perf.vPerfRaw Prf (NoLock)
Inner Join
(
Select RIn.PerformanceRuleInstanceRowId,RIn.InstanceName
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectFreeSuperscopeAddresses'
)
)
) As Ins
On Prf.PerformanceRuleInstanceRowId = Ins.PerformanceRuleInstanceRowId
Inner Join
(
Select distinct RIn.PerformanceRuleInstanceRowId as SuperScopeId,
RIn.InstanceName as SuperScopeName
From vPerformanceRuleInstance RIn (NoLock)
Inner Join Perf.vPerfRaw PRW (NoLock)
On PRW.PerformanceRuleInstanceRowId = RIn.PerformanceRuleInstanceRowId
Inner Join Managedentity Met (NoLock)
On Met.ManagedEntityRowId = PRW.ManagedEntityRowId
Inner Join Managedentity TEN (NoLock)
On Met.TopLevelHostManagedEntityRowId = TEN.ManagedEntityRowId
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectFreeSuperscopeAddresses'
)
)
And TEN.Name in
(
Select Distinct MgEn.Name
From ManagedEntity MgEn (NoLock)
Inner Join vManagedEntityPropertySet MPR (NoLock)
On MPR.ManagedEntityRowId = MgEn.ManagedEntityRowId
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
WHERE
(
(
@FilterOption = 0 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
)
) Or
(
@FilterOption = 1 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
)
AND MPR.ManagedEntityRowId not in
(
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
)
)
) Or
(
@FilterOption = 2 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
)
)
)
)
) As FIL
On FIL.SuperScopeId = Prf.PerformanceRuleInstanceRowId
Where Prf.PerformanceRuleInstanceRowId in
(
Select RIn.PerformanceRuleInstanceRowId
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Superscope.CollectFreeSuperscopeAddresses'
)
)
And (
(@SuperScopeName <> 'All' and RIn.InstanceName = @SuperScopeName) Or
(@SuperScopeName = 'All')
)
)
And DATEPART(year,Prf.[Datetime]) = DATEPART(year,@DateTimeToReport)
And DATEPART(month,Prf.[Datetime]) = DATEPART(month,@DateTimeToReport)
And DATEPART(day,Prf.[Datetime]) = DATEPART(day,@DateTimeToReport)
Group by Prf.PerformanceRuleInstanceRowId, Prf.SampleValue, Ins.InstanceName
Order by Ins.InstanceName </CommandText>
<QueryParameters>
<QueryParameter Name="@FilterOption">
<Value>=Parameters!FilterOption.Value</Value>
</QueryParameter>
<QueryParameter Name="@SuperScopeName">
<Value>=Parameters!SuperScopeName.Value</Value>
</QueryParameter>
<QueryParameter Name="@DateTimeToReport">
<Value>=Parameters!DateTimeToReport.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="ScopesInUse">
<Fields>
<Field Name="ScopesInUseCount">
<DataField>ScopesInUseCount</DataField>
<rd:TypeName>System.Double</rd:TypeName>
</Field>
<Field Name="ScopeName">
<DataField>ScopeName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DataDateTime">
<DataField>DataDateTime</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataWarehouseMain</DataSourceName>
<CommandText>
Select Prf.SampleValue As ScopesInUseCount,
Ins.InstanceName As ScopeName,
Max(Prf.[Datetime]) As DataDateTime
From Perf.vPerfRaw Prf (NoLock)
Inner Join
(
Select RIn.PerformanceRuleInstanceRowId,RIn.InstanceName
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Scope.CollectScopeAddressesInUse'
)
)
) As Ins
On Prf.PerformanceRuleInstanceRowId = Ins.PerformanceRuleInstanceRowId
Inner Join
(
Select distinct RIn.PerformanceRuleInstanceRowId as SuperScopeId,
RIn.InstanceName as SuperScopeName
From vPerformanceRuleInstance RIn (NoLock)
Inner Join Perf.vPerfRaw PRW (NoLock)
On PRW.PerformanceRuleInstanceRowId = RIn.PerformanceRuleInstanceRowId
Inner Join Managedentity Met (NoLock)
On Met.ManagedEntityRowId = PRW.ManagedEntityRowId
Inner Join Managedentity TEN (NoLock)
On Met.TopLevelHostManagedEntityRowId = TEN.ManagedEntityRowId
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Scope.CollectScopeAddressesInUse'
)
)
And TEN.Name in
(
Select Distinct MgEn.Name
From ManagedEntity MgEn (NoLock)
Inner Join vManagedEntityPropertySet MPR (NoLock)
On MPR.ManagedEntityRowId = MgEn.ManagedEntityRowId
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
WHERE
(
(
@FilterOption = 0 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
)
) Or
(
@FilterOption = 1 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
)
AND MPR.ManagedEntityRowId not in
(
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
)
)
) Or
(
@FilterOption = 2 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
)
)
)
)
) As FIL
On FIL.SuperScopeId = Prf.PerformanceRuleInstanceRowId
Where Prf.PerformanceRuleInstanceRowId in
(
Select RIn.PerformanceRuleInstanceRowId
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Scope.CollectScopeAddressesInUse'
)
)
And (
(@ScopeName <> 'All' and RIn.InstanceName = @ScopeName) Or
(@ScopeName = 'All')
)
)
And DATEPART(year,Prf.[Datetime]) = DATEPART(year,@DateTimeToReport)
And DATEPART(month,Prf.[Datetime]) = DATEPART(month,@DateTimeToReport)
And DATEPART(day,Prf.[Datetime]) = DATEPART(day,@DateTimeToReport)
Group by Prf.PerformanceRuleInstanceRowId, Prf.SampleValue, Ins.InstanceName
Order by Ins.InstanceName </CommandText>
<QueryParameters>
<QueryParameter Name="@FilterOption">
<Value>=Parameters!FilterOption.Value</Value>
</QueryParameter>
<QueryParameter Name="@ScopeName">
<Value>=Parameters!ScopeName.Value</Value>
</QueryParameter>
<QueryParameter Name="@DateTimeToReport">
<Value>=Parameters!DateTimeToReport.Value</Value>
</QueryParameter>
</QueryParameters>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="ScopesFree">
<Fields>
<Field Name="ScopesFreeCount">
<DataField>ScopesFreeCount</DataField>
<rd:TypeName>System.Double</rd:TypeName>
</Field>
<Field Name="ScopeName">
<DataField>ScopeName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="DataDateTime">
<DataField>DataDateTime</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>DataWarehouseMain</DataSourceName>
<CommandText>
Select Prf.SampleValue As ScopesFreeCount,
Ins.InstanceName As ScopeName,
Max(Prf.[Datetime]) As DataDateTime
From Perf.vPerfRaw Prf (NoLock)
Inner Join
(
Select RIn.PerformanceRuleInstanceRowId,RIn.InstanceName
From vPerformanceRuleInstance RIn
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Collect.FreeScopeAddresses'
)
)
) As Ins
On Prf.PerformanceRuleInstanceRowId = Ins.PerformanceRuleInstanceRowId
Inner Join
(
Select distinct RIn.PerformanceRuleInstanceRowId as SuperScopeId,
RIn.InstanceName as SuperScopeName
From vPerformanceRuleInstance RIn (NoLock)
Inner Join Perf.vPerfRaw PRW (NoLock)
On PRW.PerformanceRuleInstanceRowId = RIn.PerformanceRuleInstanceRowId
Inner Join Managedentity Met (NoLock)
On Met.ManagedEntityRowId = PRW.ManagedEntityRowId
Inner Join Managedentity TEN (NoLock)
On Met.TopLevelHostManagedEntityRowId = TEN.ManagedEntityRowId
Where RIn.RuleRowId In
(
Select Prl.RuleRowId
From vPerformanceRule Prl (NoLock)
Where Prl.RuleRowId in
(
Select Rls.RuleRowId
From vRule Rls (NoLock)
Where Rls.RuleSystemName = 'Microsoft.Windows.DHCPServer.Library.Collect.FreeScopeAddresses'
)
)
And TEN.Name in
(
Select Distinct MgEn.Name
From ManagedEntity MgEn (NoLock)
Inner Join vManagedEntityPropertySet MPR (NoLock)
On MPR.ManagedEntityRowId = MgEn.ManagedEntityRowId
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
WHERE
(
(
@FilterOption = 0 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
)
) Or
(
@FilterOption = 1 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
where MPR.ManagedEntityRowId
in
(
select ME.ManagedEntityRowId
from ManagedEntity ME (NoLock)
where ME.ManagedEntityTypeRowId in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.Library')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.Library.Server')
)
)
)
AND MPR.ManagedEntityRowId not in
(
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
)
)
) Or
(
@FilterOption = 2 And
MPR.ManagedEntityRowId In
(
select distinct MPR.ManagedEntityRowId
From vManagedEntityPropertySet MPR (NoLock)
Inner Join vManagedEntityTypeProperty PrNm (NoLock)
On MPR.PropertyGUID = PrNm.PropertyGUID
Inner Join vManagedEntityType MET (NoLock)
On MET.ManagedEntityTypeRowId = PrNm.ManagedEntityTypeRowId
where MET.ManagedEntityTypeRowId
in
(
select MT.ManagedEntityTypeRowId
from vManagedEntityType MT (NoLock)
where MT.managementpackrowid
in (
select MP.managementpackrowid
from vManagementPack MP (NoLock)
where
(
MP.managementPackSystemName in ('Microsoft.Windows.DHCPServer.2008')
)
)
And ManagedEntityTypeSystemName in ('Microsoft.Windows.DHCPServer.2008.Server')
)
Home
ESN