<DataWarehouseScript ID="ODR.Report.Script.ManagementGroupsReportServerRolesDataGet" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagementGroupReportServerRolesDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet]
@StartDate datetime,
@EndDate datetime,
@ManagementGroupGuid uniqueidentifier,
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @ManagementGroupRowId int
SET @ManagementGroupRowId =(Select ManagementGroupRowId from vManagementGroup where ManagementGroupGuid = @ManagementGroupGuid)
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
----------------------------------------------------------------------------------
-- Populate Computer list with instances of classes derived from 'System.Computer'
Create table #ComputerList
(
ComputerManagedEntityRowId int,
ComputerName nvarchar(255),
OperatingSystemManagedEntityRowId int,
OperatingSystemDisplayName nvarchar(255),
ServerRole nvarchar(255),
HostedProcessorRowId int
)
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
Insert #ComputerList
Select
HostingEntity.ManagedEntityRowId ,
HostingEntity.ManagedEntityDefaultName ,
HostedOS.ManagedEntityRowId ,
HostedOS.ManagedEntityDefaultName,
CASE vManagedEntityType.ManagedEntityTypeSystemName
when 'Microsoft.SystemCenter.ManagementServer' then 'MS'
when 'Microsoft.SystemCenter.GatewayManagementServer' then 'GMS'
when 'Microsoft.SystemCenter.ReportingServer.ServerRole' then 'RS'
END,
HostedProcessorRelationship.TargetManagedEntityRowId
from vManagedEntity
inner join vTypedManagedEntity
on vManagedEntity.ManagedEntityRowId= vTypedManagedEntity.ManagedEntityRowId
inner join vManagedEntityManagementGroup
on vManagedEntityManagementGroup.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
AND vManagedEntity.ManagementGroupRowId =@ManagementGroupRowId
AND vManagedEntityManagementGroup.FromDateTime <= @EndDate
AND (vManagedEntityManagementGroup.ToDateTime IS NULL OR vManagedEntityManagementGroup.ToDateTime <= @StartDate)
AND @StartDate <= @EndDate
AND @StartDate <= getdate()
inner join vManagedEntityType
on vManagedEntityType.ManagedEntityTypeRowId = vTypedManagedEntity.ManagedEntityTypeRowId
AND vManagedEntityType.ManagedEntityTypeSystemName IN
(
'Microsoft.SystemCenter.ManagementServer',
'Microsoft.SystemCenter.GatewayManagementServer',
'Microsoft.SystemCenter.ReportingServer.ServerRole'
)
--find Computer Hosting the Roles
inner join vRelationship
on vRelationship.TargetManagedEntityRowId = vManagedEntity.ManagedEntityRowId
inner join vRelationshipType
on vRelationship.RelationshipTypeRowId = vRelationshipType.RelationshipTypeRowId
AND vRelationshipType.RelationshipTypeSystemName in
(
'Microsoft.Windows.ComputerHostsLocalApplication',
'Microsoft.Windows.ComputerHostsComputerRole'
)
inner join vManagedEntity as HostingEntity
on vRelationship.SourceManagedEntityRowId = HostingEntity.ManagedEntityRowId
inner join vManagedEntityManagementGroup as HostingEntityManagementGroup
on HostingEntityManagementGroup.ManagedEntityRowId = HostingEntity.ManagedEntityRowId
AND HostingEntity.ManagementGroupRowId =@ManagementGroupRowId
AND HostingEntityManagementGroup.FromDateTime <= @EndDate
AND (HostingEntityManagementGroup.ToDateTime IS NULL OR HostingEntityManagementGroup.ToDateTime <= @StartDate)
-- find OS hosted on the Computer
left outer join vRelationship as HostedOSRelationship
on HostedOSRelationship.SourceManagedEntityRowId = HostingEntity.ManagedEntityRowId
AND HostedOSRelationship.RelationshipTypeRowId IN
(
Select RelationshipTypeRowId
from vRelationshipType
Where RelationshipTypeSystemName = 'Microsoft.Windows.ComputerHostsOperatingSystem'
)
inner join vManagedEntity as HostedOS
on HostedOSRelationship.TargetManagedEntityRowId = HostedOS.ManagedEntityRowId
inner join vManagedEntityManagementGroup as HosedOSManagementGroup
on HosedOSManagementGroup.ManagedEntityRowId = HostedOS.ManagedEntityRowId
AND HostedOS.ManagementGroupRowId =@ManagementGroupRowId
AND HosedOSManagementGroup.FromDateTime <= @EndDate
AND (HosedOSManagementGroup.ToDateTime IS NULL OR HosedOSManagementGroup.ToDateTime <= @StartDate)
left outer join vRelationship as HostedProcessorRelationship
on HostedProcessorRelationship.SourceManagedEntityRowId = HostingEntity.ManagedEntityRowId
AND HostedProcessorRelationship.RelationshipTypeRowId IN
(
Select RelationshipTypeRowId
from vRelationshipType
Where RelationshipTypeSystemName = 'Microsoft.Windows.ComputerHostsLogicalDevice'
)
AND
(
Select vManagedEntityType.ManagedEntityTypeSystemName
from vManagedEntity Inner Join
vManagedEntityType on vManagedEntityType.ManagedEntityTypeRowId=vManagedEntity.ManagedEntityTypeRowId
where vManagedEntity.ManagedEntityRowId = HostedProcessorRelationship.TargetManagedEntityRowId
) = 'Microsoft.Windows.Server.Processor'
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
--------------------------------------------------------------------------
Select
#ComputerList.OperatingSystemDisplayName,
--#ComputerList.ComputerName,
#ComputerList.ComputerManagedEntityRowId,
#ComputerList.ServerRole,
ProcessorDataWidth = PropertyList.Property.value('.','nvarchar(256)'),
DatabaseSize = NULL,
PercentFull = NULL
from #ComputerList
left outer join vManagedEntityProperty as ProcessorProperty
on #ComputerList.HostedProcessorRowId = ProcessorProperty.ManagedEntityRowId
outer APPLY ProcessorProperty.PropertyXml.nodes('/Root/Property') AS PropertyList(Property)
where
#ComputerList.HostedProcessorRowId IS NULL
OR
PropertyList.Property.value('@Guid','uniqueidentifier')IN
(
Select PropertyGuid
from vManagedEntityTypeProperty
where ManagedEntityTypeRowId =
(
Select ManagedEntityTypeRowId
from vManagedEntityType
Where ManagedEntityTypeSystemName = 'Microsoft.Windows.ComputerHostsLogicalDevice'
)
and PropertySystemName IN ('DataWidth')
)
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
QuitError:
DROP TABLE #ComputerList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagementGroupReportServerRolesDataGet')
BEGIN
DROP PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ODR_Report_Library_ManagementGroupReportServerRolesDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet]
@StartDate datetime,
@EndDate datetime,
@ManagementGroupGuid uniqueidentifier,
@LanguageCode varchar(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @ManagementGroupRowId int
SET @ManagementGroupRowId =(Select ManagementGroupRowId from vManagementGroup where ManagementGroupGuid = @ManagementGroupGuid)
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
----------------------------------------------------------------------------------
-- Populate Computer list with instances of classes derived from 'System.Computer'
Create table #ComputerList
(
ComputerManagedEntityRowId int,
ComputerName nvarchar(255),
OperatingSystemManagedEntityRowId int,
OperatingSystemDisplayName nvarchar(255),
ServerRole nvarchar(255),
HostedProcessorRowId int
)
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
Insert #ComputerList
Select
HostingEntity.ManagedEntityRowId ,
HostingEntity.ManagedEntityDefaultName ,
HostedOS.ManagedEntityRowId ,
HostedOS.ManagedEntityDefaultName,
CASE vManagedEntityType.ManagedEntityTypeSystemName
when 'Microsoft.SystemCenter.ManagementServer' then 'MS'
when 'Microsoft.SystemCenter.GatewayManagementServer' then 'GMS'
when 'Microsoft.SystemCenter.ReportingServer.ServerRole' then 'RS'
END,
HostedProcessorRelationship.TargetManagedEntityRowId
from vManagedEntity
inner join vTypedManagedEntity
on vManagedEntity.ManagedEntityRowId= vTypedManagedEntity.ManagedEntityRowId
inner join vManagedEntityManagementGroup
on vManagedEntityManagementGroup.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
AND vManagedEntity.ManagementGroupRowId =@ManagementGroupRowId
AND vManagedEntityManagementGroup.FromDateTime <= @EndDate
AND (vManagedEntityManagementGroup.ToDateTime IS NULL OR vManagedEntityManagementGroup.ToDateTime <= @StartDate)
AND @StartDate <= @EndDate
AND @StartDate <= getdate()
inner join vManagedEntityType
on vManagedEntityType.ManagedEntityTypeRowId = vTypedManagedEntity.ManagedEntityTypeRowId
AND vManagedEntityType.ManagedEntityTypeSystemName IN
(
'Microsoft.SystemCenter.ManagementServer',
'Microsoft.SystemCenter.GatewayManagementServer',
'Microsoft.SystemCenter.ReportingServer.ServerRole'
)
--find Computer Hosting the Roles
inner join vRelationship
on vRelationship.TargetManagedEntityRowId = vManagedEntity.ManagedEntityRowId
inner join vRelationshipType
on vRelationship.RelationshipTypeRowId = vRelationshipType.RelationshipTypeRowId
AND vRelationshipType.RelationshipTypeSystemName in
(
'Microsoft.Windows.ComputerHostsLocalApplication',
'Microsoft.Windows.ComputerHostsComputerRole'
)
inner join vManagedEntity as HostingEntity
on vRelationship.SourceManagedEntityRowId = HostingEntity.ManagedEntityRowId
inner join vManagedEntityManagementGroup as HostingEntityManagementGroup
on HostingEntityManagementGroup.ManagedEntityRowId = HostingEntity.ManagedEntityRowId
AND HostingEntity.ManagementGroupRowId =@ManagementGroupRowId
AND HostingEntityManagementGroup.FromDateTime <= @EndDate
AND (HostingEntityManagementGroup.ToDateTime IS NULL OR HostingEntityManagementGroup.ToDateTime <= @StartDate)
-- find OS hosted on the Computer
left outer join vRelationship as HostedOSRelationship
on HostedOSRelationship.SourceManagedEntityRowId = HostingEntity.ManagedEntityRowId
AND HostedOSRelationship.RelationshipTypeRowId IN
(
Select RelationshipTypeRowId
from vRelationshipType
Where RelationshipTypeSystemName = 'Microsoft.Windows.ComputerHostsOperatingSystem'
)
inner join vManagedEntity as HostedOS
on HostedOSRelationship.TargetManagedEntityRowId = HostedOS.ManagedEntityRowId
inner join vManagedEntityManagementGroup as HosedOSManagementGroup
on HosedOSManagementGroup.ManagedEntityRowId = HostedOS.ManagedEntityRowId
AND HostedOS.ManagementGroupRowId =@ManagementGroupRowId
AND HosedOSManagementGroup.FromDateTime <= @EndDate
AND (HosedOSManagementGroup.ToDateTime IS NULL OR HosedOSManagementGroup.ToDateTime <= @StartDate)
left outer join vRelationship as HostedProcessorRelationship
on HostedProcessorRelationship.SourceManagedEntityRowId = HostingEntity.ManagedEntityRowId
AND HostedProcessorRelationship.RelationshipTypeRowId IN
(
Select RelationshipTypeRowId
from vRelationshipType
Where RelationshipTypeSystemName = 'Microsoft.Windows.ComputerHostsLogicalDevice'
)
AND
(
Select vManagedEntityType.ManagedEntityTypeSystemName
from vManagedEntity Inner Join
vManagedEntityType on vManagedEntityType.ManagedEntityTypeRowId=vManagedEntity.ManagedEntityTypeRowId
where vManagedEntity.ManagedEntityRowId = HostedProcessorRelationship.TargetManagedEntityRowId
) = 'Microsoft.Windows.Server.Processor'
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
--------------------------------------------------------------------------
Select
#ComputerList.OperatingSystemDisplayName,
--#ComputerList.ComputerName,
#ComputerList.ComputerManagedEntityRowId,
#ComputerList.ServerRole,
ProcessorDataWidth = PropertyList.Property.value('.','nvarchar(256)'),
DatabaseSize = NULL,
PercentFull = NULL
from #ComputerList
left outer join vManagedEntityProperty as ProcessorProperty
on #ComputerList.HostedProcessorRowId = ProcessorProperty.ManagedEntityRowId
outer APPLY ProcessorProperty.PropertyXml.nodes('/Root/Property') AS PropertyList(Property)
where
#ComputerList.HostedProcessorRowId IS NULL
OR
PropertyList.Property.value('@Guid','uniqueidentifier')IN
(
Select PropertyGuid
from vManagedEntityTypeProperty
where ManagedEntityTypeRowId =
(
Select ManagedEntityTypeRowId
from vManagedEntityType
Where ManagedEntityTypeSystemName = 'Microsoft.Windows.ComputerHostsLogicalDevice'
)
and PropertySystemName IN ('DataWidth')
)
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
QuitError:
DROP TABLE #ComputerList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[ODR_Report_Library_ManagementGroupReportServerRolesDataGet] TO OpsMgrReader
GO
Home