/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeComputersFromBS] Script Date: 04/27/2010 20:54:23 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeComputersFromBS]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeComputersFromBS]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeComputersFromBS] Script Date: 04/27/2010 20:54:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MICROSOFT
-- Create date: 04-28-2010
-- Description: To get the list of computers associated with Program through Business Service
-- =============================================
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @Error int
IF OBJECT_ID(N'tempdb..#ProgramScopeFactvw', N'U') IS NOT NULL
BEGIN
DROP TABLE #ProgramScopeFactvw
END;
IF OBJECT_ID(N'tempdb..#GroupHasItemsFactvw', N'U') IS NOT NULL
BEGIN
DROP TABLE #GroupHasItemsFactvw
END;
SELECT GC.configitemdimkey AS ConfigItemDimKey, EC.configitemdimkey AS Group_ConfigItemDimKey
INTO #GroupHasItemsFactvw FROM
(SELECT CG.BaseManagedEntityId AS GroupEntity,E.BaseManagedEntityId as GroupConatinsEntity from ConfigItemGroupContainsEntitiesFactvw CGE
INNER JOIN ConfigItemGroupDimvw CG
ON CG.ConfigItemGroupDimKey = CGE.ConfigItemGroupDimKey
INNER JOIN EntityDimvw E
ON CGE.InstanceGroupContainsEntities_EntityDimKey = E.EntityDimKey
WHERE CGE.DeletedDate IS NULL) AS GroupHasEntities
LEFT JOIN ConfigItemDimvw GC on GroupHasEntities.GroupEntity = GC.BaseManagedEntityId
LEFT JOIN configitemdimvw EC on GroupHasEntities.GroupConatinsEntity = EC.BaseManagedEntityId;
WITH ProgramScopeFlatList (ProgramDimKey, ProgramScope_ConfigItemDimKey, CreatedDate)
AS
(
SELECT ProgramDimKey
,ProgramScope_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
,CreatedDate AS CreatedDate
FROM ProgramScopeFactvw
WHERE (-1 = @ProgramId OR ProgramDimKey = @ProgramId)
AND DeletedDate IS NULL
AND ProgramScope_ConfigItemDimKey = @ConfigItemDimKey
UNION ALL
SELECT ProgramDimKey
,CC.ProgramScope_ConfigItemDimKey
,CD.CreatedDate
FROM
(SELECT ConfigItemDimKey, ServiceHasGroups_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM ServiceHasGroupsFactvw WHERE DeletedDate IS NULL
UNION ALL
SELECT ConfigItemDimKey, ConfigItemContainsConfigItem_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM ConfigItemContainsConfigItemFactvw WHERE DeletedDate IS NULL
UNION ALL
SELECT ConfigItemDimKey, Group_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM #GroupHasItemsFactvw) AS CC
INNER JOIN ProgramScopeFlatList AS CD
ON CC.ConfigItemDimKey = CD.ProgramScope_ConfigItemDimKey
WHERE CC.ConfigItemDimKey IN
(SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ServiceDimvw S ON C.EntityDimKey = S.EntityDimKey
UNION ALL
SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ConfigItemGroupDimvw G ON C.BaseManagedEntityId = G.BaseManagedEntityId
UNION ALL
SELECT ServiceHasGroups_ConfigItemDimKey FROM ServiceHasGroupsFactvw
WHERE DeletedDate IS NULL)
)
SELECT ProgramDimKey, ProgramScope_ConfigItemDimKey, CreatedDate into #ProgramScopeFactvw FROM ProgramScopeFlatList;
DELETE FROM #ProgramScopeFactvw WHERE ProgramScope_ConfigItemDimKey IN
(SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ServiceDimvw S ON C.EntityDimKey = S.EntityDimKey
UNION
SELECT ServiceHasGroups_ConfigItemDimKey FROM ServiceHasGroupsFactvw
WHERE DeletedDate IS NULL
UNION
SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ConfigItemGroupDimvw G ON C.BaseManagedEntityId = G.BaseManagedEntityId)
SELECT DISTINCT
CID.DisplayName,
UDO.DisplayName AS OwnedBy,
UDS.DisplayName AS ServicedBy
FROM #ProgramScopeFactvw PSF
JOIN ConfigItemDimvw CID
ON PSF.ProgramScope_ConfigItemDimKey = CID.ConfigItemDimKey
AND CID.IsDeleted = 0
LEFT OUTER JOIN
ConfigItemOwnedByUserFactvw COU
ON CID.ConfigItemDimKey = COU.ConfigItemDimKey
AND COU.DeletedDate IS NULL
LEFT OUTER JOIN
UserDimvw UDO
ON UDO.UserDimKey = COU.ConfigItemOwnedByUser_UserDimKey
AND UDO.IsDeleted = 0
LEFT OUTER JOIN
ConfigItemServicedByUserFactvw CSU
ON CID.ConfigItemDimKey = CSU.ConfigItemDimKey
AND CSU.DeletedDate IS NULL
LEFT OUTER JOIN
UserDimvw UDS
ON UDS.UserDimKey = CSU.ConfigItemServicedByUser_UserDimKey
AND UDS.IsDeleted = 0
WHERE ProgramDimKey = @ProgramId
AND (DATEDIFF(DAY, PSF.CreatedDate, @StartDate) <= 0 AND DATEDIFF(DAY, PSF.CreatedDate, @EndDate) >=0)
SET @Error = @@ERROR
QuitError:
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_GetProgramScopeComputersFromBS TO reportuser
GO
Home