/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ManagedEntityResultList] Script Date: 08/06/2010 04:46:41 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ManagedEntityResultList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ManagedEntityResultList]
GO
/****** Object: StoredProcedure [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ManagedEntityResultList] Script Date: 08/06/2010 04:46:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 01/19/2010
-- Description: To get the MER Details
-- =============================================
CREATE PROCEDURE [dbo].[ServiceManager_Report_GRC_ControlManagement_SP_ManagedEntityResultList]
@ProgramId INT = null,
@CAId INT = null,
@ResultStartDate DATETIME,
@ResultEndDate DATETIME,
@ResultValue NVARCHAR (50) = '-1'
AS
BEGIN
-- To prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#ProgramScopeFactvw', N'U') IS NOT NULL
BEGIN
DROP TABLE #ProgramScopeFactvw
END
IF OBJECT_ID(N'tempdb..#ProgramScopeTemp', N'U') IS NOT NULL
BEGIN
DROP TABLE #ProgramScopeTemp
END
IF OBJECT_ID(N'tempdb..#MERList', N'U') IS NOT NULL
BEGIN
DROP TABLE #MERList
END
CREATE TABLE #MERList(ConfigItemDimKey INT NULL,
ID Int NULL,
Details NVARCHAR(MAX) NULL,
SourceName NVARCHAR(MAX) NULL,
SourceTestName NVARCHAR(MAX) NULL,
Result NVARCHAR(MAX) NULL,
ResultType NVARCHAR(MAX) NULL,
CIID UNIQUEIDENTIFIER NULL,
ManagedEntityName NVARCHAR(MAX) NULL)
CREATE TABLE #ProgramScopeFactvw
( ProgramDimKey int,
ProgramScope_ConfigItemDimKey int
) ;
IF OBJECT_ID(N'tempdb..#GroupHasItemsTemp', N'U') IS NOT NULL
BEGIN
DROP TABLE #GroupHasItemsTemp
END;
SELECT GC.configitemdimkey AS ConfigItemDimKey, EC.configitemdimkey AS Group_ConfigItemDimKey
INTO #GroupHasItemsTemp 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)
AS
(
SELECT ProgramDimKey,ProgramScope_ConfigItemDimKey AS ProgramScope_ConfigItemDimKey
FROM ProgramScopeFactvw
INNER JOIN ConfigItemDimvw C
ON C.ConfigItemDimKey = ProgramScope_ConfigItemDimKey
INNER JOIN
(
SELECT EntityDimKey from ServiceDimVw
UNION
SELECT EntityDimKey from ConfigItemGroupDim
)AS SG
ON C.EntityDimKey = SG.EntityDimKey
WHERE (-1 = @ProgramId OR ProgramDimKey = @ProgramId)
AND DeletedDate IS NULL
UNION ALL
SELECT ProgramDimKey
,CC.ProgramScope_ConfigItemDimKey
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 #GroupHasItemsTemp ) 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 into #ProgramScopeTemp FROM ProgramScopeFlatList;
INSERT INTO #ProgramScopeFactvw
SELECT DISTINCT ProgramDimKey, ProgramScope_ConfigItemDimKey FROM #ProgramScopeTemp
UNION
SELECT DISTINCT ProgramDimKey, ProgramScope_ConfigItemDimKey FROM ProgramScopeFactvw
WHERE (0 = @ProgramId OR ProgramDimKey = @ProgramId) AND DeletedDate IS NULL
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
)
DELETE FROM #ProgramScopeFactvw WHERE ProgramScope_ConfigItemDimKey NOT IN (SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ComputerDimvw S ON C.EntityDimKey = S.EntityDimKey)
IF OBJECT_ID(N'tempdb..#ApplicableGroupConfigItemFactvw', N'U') IS NOT NULL
BEGIN
DROP TABLE #ApplicableGroupConfigItemFactvw
END
IF OBJECT_ID(N'tempdb..#ApplicableGroupConfigItemTemp', N'U') IS NOT NULL
BEGIN
DROP TABLE #ApplicableGroupConfigItemTemp
END
CREATE TABLE #ApplicableGroupConfigItemFactvw
( ApplicabilityGroupDimKey int,
InstanceGroupContainsEntities_ConfigItemDimKey int
) ;
WITH ApplicableGroupConfigItemFlatList (ApplicabilityGroupDimKey, InstanceGroupContainsEntities_ConfigItemDimKey)
AS
(
SELECT ApplicabilityGroupDimKey
,InstanceGroupContainsEntities_ConfigItemDimKey
FROM ApplicableGroupConfigItemFactvw
INNER JOIN ConfigItemDimvw C
ON C.ConfigItemDimKey = InstanceGroupContainsEntities_ConfigItemDimKey
INNER JOIN
(
SELECT EntityDimKey from ServiceDimVw
UNION
SELECT EntityDimKey from ConfigItemGroupDim
) AS SG
ON C.EntityDimKey = SG.EntityDimKey
WHERE DeletedDate IS NULL
UNION ALL
SELECT ApplicabilityGroupDimKey
,CC.InstanceGroupContainsEntities_ConfigItemDimKey
FROM
(SELECT ConfigItemDimKey, ServiceHasGroups_ConfigItemDimKey AS InstanceGroupContainsEntities_ConfigItemDimKey
FROM ServiceHasGroupsFactvw WHERE DeletedDate IS NULL
UNION ALL
SELECT ConfigItemDimKey, ConfigItemContainsConfigItem_ConfigItemDimKey AS InstanceGroupContainsEntities_ConfigItemDimKey
FROM ConfigItemContainsConfigItemFactvw WHERE DeletedDate IS NULL
UNION ALL
SELECT ConfigItemDimKey, Group_ConfigItemDimKey AS InstanceGroupContainsEntities_ConfigItemDimKey
FROM #GroupHasItemsTemp) AS CC
INNER JOIN ApplicableGroupConfigItemFlatList AS CD
ON CC.ConfigItemDimKey = CD.InstanceGroupContainsEntities_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 ApplicabilityGroupDimKey, InstanceGroupContainsEntities_ConfigItemDimKey into #ApplicableGroupConfigItemTemp FROM ApplicableGroupConfigItemFlatList;
INSERT INTO #ApplicableGroupConfigItemFactvw
SELECT DISTINCT ApplicabilityGroupDimKey, InstanceGroupContainsEntities_ConfigItemDimKey FROM #ApplicableGroupConfigItemTemp
UNION
SELECT DISTINCT ApplicabilityGroupDimKey, InstanceGroupContainsEntities_ConfigItemDimKey FROM ApplicableGroupConfigItemFactvw
WHERE DeletedDate IS NULL
DELETE FROM #ApplicableGroupConfigItemFactvw WHERE InstanceGroupContainsEntities_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)
DELETE FROM #ApplicableGroupConfigItemFactvw WHERE InstanceGroupContainsEntities_ConfigItemDimKey NOT IN
(SELECT ConfigItemDimKey FROM ConfigItemDimvw C
JOIN ComputerDimvw S ON C.EntityDimKey = S.EntityDimKey)
IF OBJECT_ID(N'tempdb..#ProgramResultTemp', N'U') IS NOT NULL
BEGIN
DROP TABLE #ProgramResultTemp
END;
CREATE TABLE #ProgramResultTemp
(ConfigItemDimKey int
,ControlActivityDimKey int
,MaxResultDate DateTime
,ResultId int)
-- Get the max result date for the given time period for CA and CI combination
IF EXISTS (SELECT TOP 1 ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
FROM ComplianceControlActivityApplicabilityGroupFactvw
WHERE ControlActivityDimKey = @CAId
AND DeletedDate IS NULL)
BEGIN
INSERT INTO #ProgramResultTemp
(ConfigItemDimKey
,ControlActivityDimKey
,MaxResultDate
,ResultId)
SELECT MERFV.ConfigItemDimKey
, MERFV.ControlActivityDimKey
, MAX(MERFV.LastScannedTime) AS MaxResultDate
, NULL AS ResultId
FROM ManagedEntityResultFactvw MERFV
JOIN #ProgramScopeFactvw PSF
ON MERFV.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
JOIN #ApplicableGroupConfigItemFactvw AGC
ON PSF.ProgramScope_ConfigItemDimKey = AGC.InstanceGroupContainsEntities_ConfigItemDimKey
JOIN ComplianceControlActivityApplicabilityGroupFactvw CAG
ON AGC.ApplicabilityGroupDimKey = CAG.ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
AND CAG.ControlActivityDimKey = MERFV.ControlActivityDimKey
WHERE PSF.ProgramDimKey = @ProgramId
AND MERFV.ControlActivityDimKey = @CAId
AND (DATEDIFF(DAY, MERFV.LastScannedTime, @ResultStartDate) <= 0 AND DATEDIFF(DAY, MERFV.LastScannedTime, @ResultEndDate) >=0)
AND CAG.DeletedDate IS NULL
GROUP BY MERFV.ConfigItemDimKey, MERFV.ControlActivityDimKey
END
ELSE
BEGIN
INSERT INTO #ProgramResultTemp
(ConfigItemDimKey
,ControlActivityDimKey
,MaxResultDate
,ResultId)
SELECT MERFV.ConfigItemDimKey
, MERFV.ControlActivityDimKey
, MAX(MERFV.LastScannedTime) AS MaxResultDate
, NULL AS ResultId
FROM ManagedEntityResultFactvw MERFV
JOIN #ProgramScopeFactvw PSF
ON MERFV.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
WHERE PSF.ProgramDimKey = @ProgramId
AND MERFV.ControlActivityDimKey = @CAId
AND (DATEDIFF(DAY, MERFV.LastScannedTime, @ResultStartDate) <= 0 AND DATEDIFF(DAY, MERFV.LastScannedTime, @ResultEndDate) >=0)
GROUP BY MERFV.ConfigItemDimKey, MERFV.ControlActivityDimKey
END
-- Update the Result Id and MER key for the Program, CA and CI combination Manual entries
UPDATE TempResult
SET ResultId = MERFV.MERResultId
FROM #ProgramResultTemp TempResult
JOIN ManagedEntityResultFactvw MERFV
ON TempResult.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND TempResult.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND TempResult.MaxResultDate = MERFV.LastScannedTime
-- To get ALL the CI details in the given Program
DECLARE @MERResultId INT
IF EXISTS (SELECT TOP 1 ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
FROM ComplianceControlActivityApplicabilityGroupFactvw
WHERE ControlActivityDimKey = @CAId
AND DeletedDate IS NULL)
BEGIN
IF (-1 IN (Select value from @resultListReport))
BEGIN
INSERT INTO #MERList
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
ISNULL (MERR.MERResultValue, 'Unknown') AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
JOIN #ApplicableGroupConfigItemFactvw AGC
ON PSF.ProgramScope_ConfigItemDimKey = AGC.InstanceGroupContainsEntities_ConfigItemDimKey
JOIN ComplianceControlActivityApplicabilityGroupFactvw CAG
ON AGC.ApplicabilityGroupDimKey = CAG.ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
AND CAG.ControlActivityDimKey = MERFV.ControlActivityDimKey
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND CAG.ControlActivityDimKey = @CAId
AND CAG.DeletedDate IS NULL
END
-- To get the CI that has unknown as result or no result associated.
--ELSE IF ((Select MERResultId FROM MERResultVw Where MERResultValue LIKE '%Unknown%') IN (Select value from @resultListReport))
--BEGIN
-- SELECT DISTINCT
-- PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
-- NULL AS ID,
-- MERFV.Details AS Details,
-- MS.SourceSystemName AS SourceName,
-- NULL AS SourceTestName,
-- ISNULL (MERR.MERResultValue, 'Unknown') AS Result,
-- MERT.MERResultTypeValue AS ResultType,
-- CI.BaseManagedEntityId AS CIID,
-- CI.DisplayName AS ManagedEntityName
-- FROM #ProgramScopeFactvw PSF
-- JOIN #ApplicableGroupConfigItemFactvw AGC
-- ON PSF.ProgramScope_ConfigItemDimKey = AGC.InstanceGroupContainsEntities_ConfigItemDimKey
-- JOIN ComplianceControlActivityApplicabilityGroupFactvw CAG
-- ON AGC.ApplicabilityGroupDimKey = CAG.ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
-- LEFT JOIN #ProgramResultTemp PCF
-- ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
-- LEFT JOIN ManagedEntityResultFactvw MERFV
-- ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
-- AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
-- AND PCF.MaxResultDate = MERFV.LastScannedTime
-- AND CAG.ControlActivityDimKey = MERFV.ControlActivityDimKey
-- LEFT JOIN MERResultvw MERR ON
-- MERR.MERResultId = PCF.ResultId
-- LEFT JOIN MERResultTypevw MERT ON
-- MERT.MERResultTypeId = MERFV.MERResultTypeId
-- LEFT JOIN MERSourceDimvw MS ON
-- MS.MERSourceDimKey = MERFV.MERSourceDimKey
-- LEFT JOIN ConfigItemDimvw CI ON
-- CI.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
-- AND CI.IsDeleted = 0
-- WHERE PSF.ProgramDimKey = @ProgramId
-- AND (MERR.MERResultValue = 'Unknown' OR PCF.ResultId IS NULL)
-- AND CAG.ControlActivityDimKey = @CAId
-- AND CAG.DeletedDate IS NULL
--END
-- To get the CI that is Compliant or Noncompliant or Error.
ELSE
BEGIN
--Get the Result Id for the corresponding value
--SELECT @MERResultId = MERResultId FROM MERResultvw WHERE MERResultValue IN (Select value from @resultListReport)
IF(((Select COUNT(value) from @resultListReport WHERE value = (SELECT MERResultId FROM MERResultvw WHERE MERResultValue LIKE '%Unknown%'))) = 1)
BEGIN
INSERT INTO #MERList
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
ISNULL (MERR.MERResultValue, 'Unknown') AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
JOIN #ApplicableGroupConfigItemFactvw AGC
ON PSF.ProgramScope_ConfigItemDimKey = AGC.InstanceGroupContainsEntities_ConfigItemDimKey
JOIN ComplianceControlActivityApplicabilityGroupFactvw CAG
ON AGC.ApplicabilityGroupDimKey = CAG.ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
AND CAG.ControlActivityDimKey = MERFV.ControlActivityDimKey
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND (MERR.MERResultValue = 'Unknown' OR PCF.ResultId IS NULL)
AND CAG.ControlActivityDimKey = @CAId
AND CAG.DeletedDate IS NULL
UNION
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
MERR.MERResultValue AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
JOIN #ApplicableGroupConfigItemFactvw AGC
ON PSF.ProgramScope_ConfigItemDimKey = AGC.InstanceGroupContainsEntities_ConfigItemDimKey
JOIN ComplianceControlActivityApplicabilityGroupFactvw CAG
ON AGC.ApplicabilityGroupDimKey = CAG.ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
AND CAG.ControlActivityDimKey = MERFV.ControlActivityDimKey
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND MERFV.ControlActivityDimKey = @CAId
AND MERR.MERResultId = (Select value from @resultListReport WHERE value = (SELECT MERResultId FROM MERResultvw WHERE MERResultValue LIKE '%Unknown%'))
AND CAG.DeletedDate IS NULL
END
INSERT INTO #MERList
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
MERR.MERResultValue AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
JOIN #ApplicableGroupConfigItemFactvw AGC
ON PSF.ProgramScope_ConfigItemDimKey = AGC.InstanceGroupContainsEntities_ConfigItemDimKey
JOIN ComplianceControlActivityApplicabilityGroupFactvw CAG
ON AGC.ApplicabilityGroupDimKey = CAG.ControlActivityApplicabilityGroup_ApplicabilityGroupDimKey
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
AND CAG.ControlActivityDimKey = MERFV.ControlActivityDimKey
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND MERFV.ControlActivityDimKey = @CAId
AND MERR.MERResultId IN (Select value from @resultListReport WHERE value != (SELECT MERResultId FROM MERResultvw WHERE MERResultValue LIKE '%Unknown%'))
AND CAG.DeletedDate IS NULL
END
END
ELSE
BEGIN
IF (-1 IN (Select value from @resultListReport))
BEGIN
INSERT INTO #MERList
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
ISNULL (MERR.MERResultValue, 'Unknown') AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
END
-- To get the CI that has unknown as result or no result associated.
--ELSE IF ((Select MERResultId FROM MERResultVw Where MERResultValue LIKE '%Unknown%')IN (Select value from @resultListReport))
--BEGIN
-- SELECT DISTINCT
-- PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
-- NULL AS ID,
-- MERFV.Details AS Details,
-- MS.SourceSystemName AS SourceName,
-- NULL AS SourceTestName,
-- ISNULL (MERR.MERResultValue, 'Unknown') AS Result,
-- MERT.MERResultTypeValue AS ResultType,
-- CI.BaseManagedEntityId AS CIID,
-- CI.DisplayName AS ManagedEntityName
-- FROM #ProgramScopeFactvw PSF
-- LEFT JOIN #ProgramResultTemp PCF
-- ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
-- LEFT JOIN ManagedEntityResultFactvw MERFV
-- ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
-- AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
-- AND PCF.MaxResultDate = MERFV.LastScannedTime
-- LEFT JOIN MERResultvw MERR ON
-- MERR.MERResultId = PCF.ResultId
-- LEFT JOIN MERResultTypevw MERT ON
-- MERT.MERResultTypeId = MERFV.MERResultTypeId
-- LEFT JOIN MERSourceDimvw MS ON
-- MS.MERSourceDimKey = MERFV.MERSourceDimKey
-- LEFT JOIN ConfigItemDimvw CI ON
-- CI.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
-- AND CI.IsDeleted = 0
-- WHERE PSF.ProgramDimKey = @ProgramId
-- AND (MERR.MERResultValue = 'Unknown' OR PCF.ResultId IS NULL)
--END
-- To get the CI that is Compliant or Noncompliant or Error.
ELSE
BEGIN
--Get the Result Id for the corresponding value
--SELECT @MERResultId = MERResultId FROM MERResultvw WHERE MERResultValue IN (Select value from @resultListReport)
if((Select Count(value) from @resultListReport WHERE value = (SELECT MERResultId FROM MERResultvw WHERE MERResultValue LIKE '%Unknown%')) = 1)
BEGIN
INSERT INTO #MERList
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
ISNULL (MERR.MERResultValue, 'Unknown') AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = PSF.ProgramScope_ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND (MERR.MERResultValue = 'Unknown' OR PCF.ResultId IS NULL)
UNION
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
MERR.MERResultValue AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND MERFV.ControlActivityDimKey = @CAId
AND MERR.MERResultId = (Select value from @resultListReport WHERE value = (SELECT MERResultId FROM MERResultvw WHERE MERResultValue LIKE '%Unknown%'))
END
INSERT INTO #MERList
SELECT DISTINCT
PSF.ProgramScope_ConfigItemDimKey AS ConfigItemDimKey,
NULL AS ID,
MERFV.Details AS Details,
MS.SourceSystemName AS SourceName,
NULL AS SourceTestName,
MERR.MERResultValue AS Result,
MERT.MERResultTypeValue AS ResultType,
CI.BaseManagedEntityId AS CIID,
CI.DisplayName AS ManagedEntityName
FROM #ProgramScopeFactvw PSF
LEFT JOIN #ProgramResultTemp PCF
ON PSF.ProgramScope_ConfigItemDimKey = PCF.ConfigItemDimKey
LEFT JOIN ManagedEntityResultFactvw MERFV
ON PCF.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND PCF.ControlActivityDimKey = MERFV.ControlActivityDimKey
AND PCF.MaxResultDate = MERFV.LastScannedTime
LEFT JOIN MERResultvw MERR ON
MERR.MERResultId = PCF.ResultId
LEFT JOIN MERResultTypevw MERT ON
MERT.MERResultTypeId = MERFV.MERResultTypeId
LEFT JOIN MERSourceDimvw MS ON
MS.MERSourceDimKey = MERFV.MERSourceDimKey
LEFT JOIN ConfigItemDimvw CI ON
CI.ConfigItemDimKey = MERFV.ConfigItemDimKey
AND CI.IsDeleted = 0
WHERE PSF.ProgramDimKey = @ProgramId
AND MERFV.ControlActivityDimKey = @CAId
AND MERR.MERResultId IN (Select value from @resultListReport WHERE value != (SELECT MERResultId FROM MERResultvw WHERE MERResultValue LIKE '%Unknown%'))
END
END
SELECT MER.*,OwnedBy.DisplayName [CIOwner] FROM #MERList MER
LEFT JOIN ConfigItemOwnedByUserFactvw CIO
ON CIO.ConfigItemDimKey = MER.ConfigItemDimKey
AND CIO.DeletedDate IS NULL
LEFT JOIN UserDimvw OwnedBy
ON OwnedBy.UserDimKey = CIO.ConfigItemOwnedByUser_UserDimKey
AND OwnedBy.IsDeleted = 0
END
GO
GRANT EXECUTE ON dbo.ServiceManager_Report_GRC_ControlManagement_SP_ManagedEntityResultList TO reportuser
GO
Home