<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.Override" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet]
@StartDate datetime,
@EndDate datetime,
@ManagementGroupId uniqueidentifier,
@ExcludeSealedInd bit,
@OverriddenManagementPackInd bit,
@ManagementPack xml,
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ManagementPackList
SELECT ManagementPack.ManagementPackSystemName.value('.', 'nvarchar(256)')
FROM @ManagementPack.nodes('/Data/Value') AS ManagementPack(ManagementPackSystemName)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #OverrideList
(OverrideManagementPackVersionRowId, OverrideType, WorkflowRowId, ParameterPropertyName,
ManagementPackRowId, ManagementPackVersion,
ManagementPackInstalledDateTime, ManagementPackDeletedDateTime,
ManagementGroupRowId)
SELECT DISTINCT vOverrideManagementPackVersion.OverrideManagementPackVersionRowId,
CASE
WHEN vOverrideManagementPackVersion.CategoryOverrideInd = 1 THEN 'Category'
WHEN vOverrideManagementPackVersion.MonitoringOverrideInd = 1 THEN 'Monitoring'
WHEN vOverrideManagementPackVersion.MonitorPropertyOverrideInd = 1 THEN 'MonitorProperty'
WHEN vOverrideManagementPackVersion.MonitorConfigurationOverrideInd = 1 THEN 'MonitorConfiguration'
WHEN vOverrideManagementPackVersion.RulePropertyOverrideInd = 1 THEN 'RuleProperty'
WHEN vOverrideManagementPackVersion.RuleConfigurationOverrideInd = 1 THEN 'RuleConfiguration'
WHEN vOverrideManagementPackVersion.DiagnosticPropertyOverrideInd = 1 THEN 'DiagnosticProperty'
WHEN vOverrideManagementPackVersion.DiagnosticConfigurationOverrideInd = 1 THEN 'DiagnosticConfiguration'
WHEN vOverrideManagementPackVersion.RecoveryPropertyOverrideInd = 1 THEN 'RecoveryProperty'
WHEN vOverrideManagementPackVersion.RecoveryConfigurationOverrideInd = 1 THEN 'RecoveryConfiguration'
WHEN vOverrideManagementPackVersion.DiscoveryPropertyOverrideInd = 1 THEN 'DiscoveryProperty'
WHEN vOverrideManagementPackVersion.DiscoveryConfigurationOverrideInd = 1 THEN 'DiscoveryConfiguration'
END AS OverrideType,
CASE
WHEN vOverrideManagementPackVersion.CategoryOverrideInd = 1 THEN vOverrideManagementPackVersion.WorkflowCategoryRowId
WHEN vOverrideManagementPackVersion.MonitoringOverrideInd = 1 THEN vOverrideManagementPackVersion.ContextManagedEntityTypeRowId
ELSE vOverrideManagementPackVersion.WorkflowRowId
END AS WorkflowRowId,
CASE
WHEN vOverrideManagementPackVersion.MonitorConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.RuleConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.DiagnosticConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.RecoveryConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.DiscoveryConfigurationOverrideInd = 1
THEN vOverrideManagementPackVersion.OverridableParameterName
WHEN vOverrideManagementPackVersion.MonitorPropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.RulePropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.DiagnosticPropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.RecoveryPropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.DiscoveryPropertyOverrideInd = 1
THEN vOverrideManagementPackVersion.OverridablePropertyName
END AS ParameterPropertyName,
vManagementPack.ManagementPackRowId, vManagementPackVersion.ManagementPackVersion,
vManagementGroupManagementPackVersion.InstalledDateTime, vManagementGroupManagementPackVersion.DeletedDateTime,
vManagementGroup.ManagementGroupRowId
FROM vOverrideManagementPackVersion
INNER JOIN vManagementPackVersion ON vManagementPackVersion.ManagementPackVersionRowId = vOverrideManagementPackVersion.ManagementPackVersionRowId
INNER JOIN vManagementPack ON vManagementPack.ManagementPackRowId = vManagementPackVersion.ManagementPackRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vManagementPackVersion.ManagementPackVersionRowId
INNER JOIN vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagementGroupManagementPackVersion.ManagementGroupRowId
WHERE
((@ExcludeSealedInd = 0) OR (vManagementPack.SealedInd = 0)) AND
((@OverriddenManagementPackInd = 1) OR (vManagementPack.ManagementPackSystemName IN (SELECT ManagementPackSystemName FROM #ManagementPackList))) AND
vManagementGroup.ManagementGroupGuid = @ManagementGroupId AND
vManagementGroupManagementPackVersion.InstalledDateTime < @EndDate AND @StartDate <= ISNULL(vManagementGroupManagementPackVersion.DeletedDateTime, '99991231')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET ParameterPropertyName = 'Enabled',
WorkflowSystemName = vWorkflowCategory.WorkflowCategorySystemName,
WorkflowDefaultName = vWorkflowCategory.WorkflowCategorySystemName
FROM vWorkflowCategory
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vWorkflowCategory.WorkflowCategoryRowId
WHERE OverrideList.OverrideType = 'Category'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowManagementPackRowId = vManagedEntityType.ManagementPackRowId,
WorkflowSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vManagedEntityType
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vManagedEntityType.ManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType = 'Monitoring'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vMonitor.MonitorGuid,
WorkflowManagementPackRowId = vMonitor.ManagementPackRowId,
WorkflowSystemName = vMonitor.MonitorSystemName,
WorkflowDefaultName = ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) ,
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vMonitor
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vMonitor.MonitorRowId
INNER JOIN vMonitorManagementPackVersion ON vMonitorManagementPackVersion.MonitorRowId = vMonitor.MonitorRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vMonitorManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vMonitorManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('MonitorProperty', 'MonitorConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vRule.RuleGuid,
WorkflowManagementPackRowId = vRule.ManagementPackRowId,
WorkflowSystemName = vRule.RuleSystemName,
WorkflowDefaultName = ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName),
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vRule
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vRule.RuleRowId
INNER JOIN vRuleManagementPackVersion ON vRuleManagementPackVersion.RuleRowId = vRule.RuleRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vRuleManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vRuleManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid
AND vDisplayStringRule.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('RuleProperty', 'RuleConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vDiagnostic.DiagnosticGuid,
WorkflowManagementPackRowId = vDiagnostic.ManagementPackRowId,
WorkflowSystemName = vDiagnostic.DiagnosticSystemName,
WorkflowDefaultName = ISNULL(vDisplayString.Name,vDiagnostic.DiagnosticDefaultName),
WorkflowTargetGuid = vMonitor.MonitorGuid,
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vMonitor.MonitorSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName)
FROM vDiagnostic
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vDiagnostic.DiagnosticRowId
INNER JOIN vDiagnosticManagementPackVersion ON vDiagnosticManagementPackVersion.DiagnosticRowId = vDiagnostic.DiagnosticRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vDiagnosticManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vMonitor ON vMonitor.MonitorRowId = vDiagnosticManagementPackVersion.MonitorRowId
LEFT OUTER JOIN vMonitorManagementPackVersion ON vMonitorManagementPackVersion.MonitorRowId = vMonitor.MonitorRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vMonitorManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString ON vDiagnostic.DiagnosticGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('DiagnosticProperty', 'DiagnosticConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vRecovery.RecoveryGuid,
WorkflowManagementPackRowId = vRecovery.ManagementPackRowId,
WorkflowSystemName = vRecovery.RecoverySystemName,
WorkflowDefaultName = ISNULL(vDisplayString.Name,vRecovery.RecoveryDefaultName),
WorkflowTargetGuid = vMonitor.MonitorGuid,
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vMonitor.MonitorSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName)
FROM vRecovery
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vRecovery.RecoveryRowId
INNER JOIN vRecoveryManagementPackVersion ON vRecoveryManagementPackVersion.RecoveryRowId = vRecovery.RecoveryRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vRecoveryManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vMonitor ON vMonitor.MonitorRowId = vRecoveryManagementPackVersion.MonitorRowId
LEFT OUTER JOIN vMonitorManagementPackVersion ON vMonitorManagementPackVersion.MonitorRowId = vMonitor.MonitorRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vMonitorManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString ON vRecovery.RecoveryGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('RecoveryProperty', 'RecoveryConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vDiscovery.DiscoveryGuid,
WorkflowManagementPackRowId = vDiscovery.ManagementPackRowId,
WorkflowSystemName = vDiscovery.DiscoverySystemName,
WorkflowDefaultName = ISNULL(vDisplayStringDiscovery.Name,vDiscovery.DiscoveryDefaultName),
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vDiscovery
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vDiscovery.DiscoveryRowId
INNER JOIN vDiscoveryManagementPackVersion ON vDiscoveryManagementPackVersion.DiscoveryRowId = vDiscovery.DiscoveryRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vDiscoveryManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vDiscoveryManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringDiscovery ON vDiscovery.DiscoveryGuid = vDisplayString.ElementGuid
AND vDisplayStringDiscovery.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('DiscoveryProperty', 'DiscoveryConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
OverrideList.WorkflowManagementPackRowId,
WorkflowManagementPack.ManagementPackSystemName AS WorkflowManagementPackSystemName,
ISNULL(vDisplayStringWMP.Name, WorkflowManagementPack.ManagementPackDefaultName) AS WorkflowManagementPackDefaultName,
WorkflowManagementPack.SealedInd AS WorkflowManagementPackSealedInd,
vManagedEntityType.ManagedEntityTypeRowId AS ContextManagedEntityTypeRowId,
vManagedEntityType.ManagedEntityTypeGuid AS ContextManagedEntityTypeGuid,
vManagedEntityType.ManagedEntityTypeSystemName AS ContextManagedEntityTypeSystemName,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS ContextManagedEntityTypeDefaultName,
vOverrideManagementPackVersion.ManagedEntityGuid AS ContextManagedEntityGuid,
vManagedEntity.FullName AS ContextManagedEntityFullName,
vManagedEntity.ManagedEntityDefaultName AS ContextManagedEntityDefaultName
FROM #OverrideList AS OverrideList
INNER JOIN vOverrideManagementPackVersion ON vOverrideManagementPackVersion.OverrideManagementPackVersionRowId = OverrideList.OverrideManagementPackVersionRowId
INNER JOIN vOverride ON vOverride.OverrideRowId = vOverrideManagementPackVersion.OverrideRowId
INNER JOIN vManagementPack ON vManagementPack.ManagementPackRowId = OverrideList.ManagementPackRowId
INNER JOIN vManagementGroup ON vManagementGroup.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagementPack AS WorkflowManagementPack ON WorkflowManagementPack.ManagementPackRowId = OverrideList.WorkflowManagementPackRowId
LEFT OUTER JOIN vManagedEntityType ON vOverrideManagementPackVersion.ContextManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
LEFT OUTER JOIN vManagedEntity ON vOverrideManagementPackVersion.ManagedEntityGuid = vManagedEntity.ManagedEntityGuid
LEFT OUTER JOIN vDisplayString vDisplayStringMP ON vManagementPack.ManagementPackVersionIndependentGuid = vDisplayStringMP.ElementGuid
AND vDisplayStringMP.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringWMP ON WorkflowManagementPack.ManagementPackVersionIndependentGuid = vDisplayStringWMP.ElementGuid
AND vDisplayStringWMP.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringOverride ON vOverride.OverrideGuid = vDisplayStringOverride.ElementGuid
AND vDisplayStringOverride.LanguageCode = @LanguageCode
WHERE ((@OverriddenManagementPackInd = 0) OR
(OverrideList.WorkflowManagementPackRowId IS NULL) OR
(WorkflowManagementPack.ManagementPackSystemName IN (SELECT ManagementPackSystemName FROM #ManagementPackList)))
SET @Error = @@ERROR
QuitError:
DROP TABLE #ManagementPackList
DROP TABLE #OverrideList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet]
@StartDate datetime,
@EndDate datetime,
@ManagementGroupId uniqueidentifier,
@ExcludeSealedInd bit,
@OverriddenManagementPackInd bit,
@ManagementPack xml,
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ManagementPackList
SELECT ManagementPack.ManagementPackSystemName.value('.', 'nvarchar(256)')
FROM @ManagementPack.nodes('/Data/Value') AS ManagementPack(ManagementPackSystemName)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #OverrideList
(OverrideManagementPackVersionRowId, OverrideType, WorkflowRowId, ParameterPropertyName,
ManagementPackRowId, ManagementPackVersion,
ManagementPackInstalledDateTime, ManagementPackDeletedDateTime,
ManagementGroupRowId)
SELECT DISTINCT vOverrideManagementPackVersion.OverrideManagementPackVersionRowId,
CASE
WHEN vOverrideManagementPackVersion.CategoryOverrideInd = 1 THEN 'Category'
WHEN vOverrideManagementPackVersion.MonitoringOverrideInd = 1 THEN 'Monitoring'
WHEN vOverrideManagementPackVersion.MonitorPropertyOverrideInd = 1 THEN 'MonitorProperty'
WHEN vOverrideManagementPackVersion.MonitorConfigurationOverrideInd = 1 THEN 'MonitorConfiguration'
WHEN vOverrideManagementPackVersion.RulePropertyOverrideInd = 1 THEN 'RuleProperty'
WHEN vOverrideManagementPackVersion.RuleConfigurationOverrideInd = 1 THEN 'RuleConfiguration'
WHEN vOverrideManagementPackVersion.DiagnosticPropertyOverrideInd = 1 THEN 'DiagnosticProperty'
WHEN vOverrideManagementPackVersion.DiagnosticConfigurationOverrideInd = 1 THEN 'DiagnosticConfiguration'
WHEN vOverrideManagementPackVersion.RecoveryPropertyOverrideInd = 1 THEN 'RecoveryProperty'
WHEN vOverrideManagementPackVersion.RecoveryConfigurationOverrideInd = 1 THEN 'RecoveryConfiguration'
WHEN vOverrideManagementPackVersion.DiscoveryPropertyOverrideInd = 1 THEN 'DiscoveryProperty'
WHEN vOverrideManagementPackVersion.DiscoveryConfigurationOverrideInd = 1 THEN 'DiscoveryConfiguration'
END AS OverrideType,
CASE
WHEN vOverrideManagementPackVersion.CategoryOverrideInd = 1 THEN vOverrideManagementPackVersion.WorkflowCategoryRowId
WHEN vOverrideManagementPackVersion.MonitoringOverrideInd = 1 THEN vOverrideManagementPackVersion.ContextManagedEntityTypeRowId
ELSE vOverrideManagementPackVersion.WorkflowRowId
END AS WorkflowRowId,
CASE
WHEN vOverrideManagementPackVersion.MonitorConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.RuleConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.DiagnosticConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.RecoveryConfigurationOverrideInd = 1 OR
vOverrideManagementPackVersion.DiscoveryConfigurationOverrideInd = 1
THEN vOverrideManagementPackVersion.OverridableParameterName
WHEN vOverrideManagementPackVersion.MonitorPropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.RulePropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.DiagnosticPropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.RecoveryPropertyOverrideInd = 1 OR
vOverrideManagementPackVersion.DiscoveryPropertyOverrideInd = 1
THEN vOverrideManagementPackVersion.OverridablePropertyName
END AS ParameterPropertyName,
vManagementPack.ManagementPackRowId, vManagementPackVersion.ManagementPackVersion,
vManagementGroupManagementPackVersion.InstalledDateTime, vManagementGroupManagementPackVersion.DeletedDateTime,
vManagementGroup.ManagementGroupRowId
FROM vOverrideManagementPackVersion
INNER JOIN vManagementPackVersion ON vManagementPackVersion.ManagementPackVersionRowId = vOverrideManagementPackVersion.ManagementPackVersionRowId
INNER JOIN vManagementPack ON vManagementPack.ManagementPackRowId = vManagementPackVersion.ManagementPackRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vManagementPackVersion.ManagementPackVersionRowId
INNER JOIN vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagementGroupManagementPackVersion.ManagementGroupRowId
WHERE
((@ExcludeSealedInd = 0) OR (vManagementPack.SealedInd = 0)) AND
((@OverriddenManagementPackInd = 1) OR (vManagementPack.ManagementPackSystemName IN (SELECT ManagementPackSystemName FROM #ManagementPackList))) AND
vManagementGroup.ManagementGroupGuid = @ManagementGroupId AND
vManagementGroupManagementPackVersion.InstalledDateTime < @EndDate AND @StartDate <= ISNULL(vManagementGroupManagementPackVersion.DeletedDateTime, '99991231')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET ParameterPropertyName = 'Enabled',
WorkflowSystemName = vWorkflowCategory.WorkflowCategorySystemName,
WorkflowDefaultName = vWorkflowCategory.WorkflowCategorySystemName
FROM vWorkflowCategory
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vWorkflowCategory.WorkflowCategoryRowId
WHERE OverrideList.OverrideType = 'Category'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowManagementPackRowId = vManagedEntityType.ManagementPackRowId,
WorkflowSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vManagedEntityType
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vManagedEntityType.ManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType = 'Monitoring'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vMonitor.MonitorGuid,
WorkflowManagementPackRowId = vMonitor.ManagementPackRowId,
WorkflowSystemName = vMonitor.MonitorSystemName,
WorkflowDefaultName = ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) ,
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vMonitor
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vMonitor.MonitorRowId
INNER JOIN vMonitorManagementPackVersion ON vMonitorManagementPackVersion.MonitorRowId = vMonitor.MonitorRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vMonitorManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vMonitorManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('MonitorProperty', 'MonitorConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vRule.RuleGuid,
WorkflowManagementPackRowId = vRule.ManagementPackRowId,
WorkflowSystemName = vRule.RuleSystemName,
WorkflowDefaultName = ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName),
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vRule
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vRule.RuleRowId
INNER JOIN vRuleManagementPackVersion ON vRuleManagementPackVersion.RuleRowId = vRule.RuleRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vRuleManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vRuleManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid
AND vDisplayStringRule.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('RuleProperty', 'RuleConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vDiagnostic.DiagnosticGuid,
WorkflowManagementPackRowId = vDiagnostic.ManagementPackRowId,
WorkflowSystemName = vDiagnostic.DiagnosticSystemName,
WorkflowDefaultName = ISNULL(vDisplayString.Name,vDiagnostic.DiagnosticDefaultName),
WorkflowTargetGuid = vMonitor.MonitorGuid,
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vMonitor.MonitorSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName)
FROM vDiagnostic
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vDiagnostic.DiagnosticRowId
INNER JOIN vDiagnosticManagementPackVersion ON vDiagnosticManagementPackVersion.DiagnosticRowId = vDiagnostic.DiagnosticRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vDiagnosticManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vMonitor ON vMonitor.MonitorRowId = vDiagnosticManagementPackVersion.MonitorRowId
LEFT OUTER JOIN vMonitorManagementPackVersion ON vMonitorManagementPackVersion.MonitorRowId = vMonitor.MonitorRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vMonitorManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString ON vDiagnostic.DiagnosticGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('DiagnosticProperty', 'DiagnosticConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vRecovery.RecoveryGuid,
WorkflowManagementPackRowId = vRecovery.ManagementPackRowId,
WorkflowSystemName = vRecovery.RecoverySystemName,
WorkflowDefaultName = ISNULL(vDisplayString.Name,vRecovery.RecoveryDefaultName),
WorkflowTargetGuid = vMonitor.MonitorGuid,
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vMonitor.MonitorSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName)
FROM vRecovery
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vRecovery.RecoveryRowId
INNER JOIN vRecoveryManagementPackVersion ON vRecoveryManagementPackVersion.RecoveryRowId = vRecovery.RecoveryRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vRecoveryManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vMonitor ON vMonitor.MonitorRowId = vRecoveryManagementPackVersion.MonitorRowId
LEFT OUTER JOIN vMonitorManagementPackVersion ON vMonitorManagementPackVersion.MonitorRowId = vMonitor.MonitorRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vMonitorManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid
AND vDisplayStringMonitor.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString ON vRecovery.RecoveryGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('RecoveryProperty', 'RecoveryConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
UPDATE #OverrideList
SET WorkflowGuid = vDiscovery.DiscoveryGuid,
WorkflowManagementPackRowId = vDiscovery.ManagementPackRowId,
WorkflowSystemName = vDiscovery.DiscoverySystemName,
WorkflowDefaultName = ISNULL(vDisplayStringDiscovery.Name,vDiscovery.DiscoveryDefaultName),
WorkflowTargetTypeGuid = vManagedEntityType.ManagedEntityTypeGuid,
WorkflowTargetSystemName = vManagedEntityType.ManagedEntityTypeSystemName,
WorkflowTargetDefaultName = ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName)
FROM vDiscovery
INNER JOIN #OverrideList AS OverrideList ON OverrideList.WorkflowRowId = vDiscovery.DiscoveryRowId
INNER JOIN vDiscoveryManagementPackVersion ON vDiscoveryManagementPackVersion.DiscoveryRowId = vDiscovery.DiscoveryRowId
INNER JOIN vManagementGroupManagementPackVersion ON vManagementGroupManagementPackVersion.ManagementPackVersionRowId = vDiscoveryManagementPackVersion.ManagementPackVersionRowId AND
vManagementGroupManagementPackVersion.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vDiscoveryManagementPackVersion.TargetManagedEntityTypeRowId
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringDiscovery ON vDiscovery.DiscoveryGuid = vDisplayString.ElementGuid
AND vDisplayStringDiscovery.LanguageCode = @LanguageCode
WHERE OverrideList.OverrideType in ('DiscoveryProperty', 'DiscoveryConfiguration')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
OverrideList.WorkflowManagementPackRowId,
WorkflowManagementPack.ManagementPackSystemName AS WorkflowManagementPackSystemName,
ISNULL(vDisplayStringWMP.Name, WorkflowManagementPack.ManagementPackDefaultName) AS WorkflowManagementPackDefaultName,
WorkflowManagementPack.SealedInd AS WorkflowManagementPackSealedInd,
vManagedEntityType.ManagedEntityTypeRowId AS ContextManagedEntityTypeRowId,
vManagedEntityType.ManagedEntityTypeGuid AS ContextManagedEntityTypeGuid,
vManagedEntityType.ManagedEntityTypeSystemName AS ContextManagedEntityTypeSystemName,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS ContextManagedEntityTypeDefaultName,
vOverrideManagementPackVersion.ManagedEntityGuid AS ContextManagedEntityGuid,
vManagedEntity.FullName AS ContextManagedEntityFullName,
vManagedEntity.ManagedEntityDefaultName AS ContextManagedEntityDefaultName
FROM #OverrideList AS OverrideList
INNER JOIN vOverrideManagementPackVersion ON vOverrideManagementPackVersion.OverrideManagementPackVersionRowId = OverrideList.OverrideManagementPackVersionRowId
INNER JOIN vOverride ON vOverride.OverrideRowId = vOverrideManagementPackVersion.OverrideRowId
INNER JOIN vManagementPack ON vManagementPack.ManagementPackRowId = OverrideList.ManagementPackRowId
INNER JOIN vManagementGroup ON vManagementGroup.ManagementGroupRowId = OverrideList.ManagementGroupRowId
LEFT OUTER JOIN vManagementPack AS WorkflowManagementPack ON WorkflowManagementPack.ManagementPackRowId = OverrideList.WorkflowManagementPackRowId
LEFT OUTER JOIN vManagedEntityType ON vOverrideManagementPackVersion.ContextManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
LEFT OUTER JOIN vManagedEntity ON vOverrideManagementPackVersion.ManagedEntityGuid = vManagedEntity.ManagedEntityGuid
LEFT OUTER JOIN vDisplayString vDisplayStringMP ON vManagementPack.ManagementPackVersionIndependentGuid = vDisplayStringMP.ElementGuid
AND vDisplayStringMP.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringWMP ON WorkflowManagementPack.ManagementPackVersionIndependentGuid = vDisplayStringWMP.ElementGuid
AND vDisplayStringWMP.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid
AND vDisplayString.LanguageCode = @LanguageCode
LEFT OUTER JOIN vDisplayString vDisplayStringOverride ON vOverride.OverrideGuid = vDisplayStringOverride.ElementGuid
AND vDisplayStringOverride.LanguageCode = @LanguageCode
WHERE ((@OverriddenManagementPackInd = 0) OR
(OverrideList.WorkflowManagementPackRowId IS NULL) OR
(WorkflowManagementPack.ManagementPackSystemName IN (SELECT ManagementPackSystemName FROM #ManagementPackList)))
SET @Error = @@ERROR
QuitError:
DROP TABLE #ManagementPackList
DROP TABLE #OverrideList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_OverrideReportDataGet] TO OpsMgrReader
GO
Home
