Report Server Duplicating Policy Status Entries

Sep 29, 2010 at 4:14 PM
Edited Sep 29, 2010 at 4:25 PM

I am running SQL Server 2008 Standard x86 SP1 with CU3 with the 976761 FIX (10.0.2740) on Windows 2003 Server SP2.  Also using Powershell 2.0.  
After a fresh install of the EPM Framework 3.0 and configuration of the SQL job to run the Powershell script against a group of servers in my CMS, the report doesn't seem to be displaying properly.  If I drill down into a server name, then drill down into a database name or the DEFAULT node, I see duplicate policy evaluation results all with the same date.  There are always the same amount of entries per policy.  For instance, the Microsoft provided "Database Auto Close" policy always appears 9 times while other policies provided by Microsoft such as "Database Page Status" appear 7 times.  


Is there something wrong with the Microsoft supplied policies?  Or can you not change their category to a custom category name?  I changed a couple of them to a custom category name.  Perhaps that is my problem?  For my own custom policies, they appear under a new DB_NAME entry, but a duplicate of the original except they only contain my custom policies.  I wish I could provide a screen shot as this would be easier to describe.  Example of the formatting when the report displays:

Custom Category Name:  XYZ Standard Checks

- SQLSERVERNAME
--- DB_NAME_ABC  (Microsoft Supplied Policies with category name changed to XYZ Standard Checks) 
----- Policy status duplicated 'x' amount of times as shown above

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 

Database Auto Close PASS 9/29/2010 5:31 AM 


--- DB_NAME_ABC **duplicate** (Custom policies created by me with a custom category name XYZ Standard Checks)
----- Policy status not duplicated  


SQL Server File Growth Not Percent (Logs)   FAIL 9/29/2010 5:31 AM

SQL Server File Growth Not Percent (Data) FAIL 9/29/2010 5:31 AM

Anyone have any thoughts on this?  I can send a screenshot or a copy of the XML result files, if needed.  Thanks for your help.  Hopefully my explanation isn't too confusing. 


































Dec 11, 2011 at 12:12 AM
Edited Dec 11, 2011 at 12:14 AM

I've the same problem. I've CMS registered about 40 servers. While evaluating policies, if any error occurs the rest of evaluation results are added 1 more.
To make it clear let's say we have 10 registered servers. While evaluating  3, 5 and 8 servers some exception rised.
Server 1 and 2 has 1 result,
server 3 has exception,
server 4 has 2 evaluation results,
Server 5 has exception
Server 6, 7 has 3 evaluation results
Server 8 has exception
Server 9, 10 has 4 evaluation results

I've profiled the destination server to understand how many time policies are evaluated, as far as I understand the policies evaluated once but writen to OutputXML result file more then once. I don't know why.

If any body have solution please share it.

Thanks
Nizamettin Özpolat
SQL Server DBA.

Dec 13, 2011 at 3:18 PM

I have the same problem.  I could never figure it out, so I added this as a final step in my job to delete the duplicates...

-- Deletes duplicates created in this table due to a bug in how the system handles evaluation errors

SELECT  MIN([PolicyHistoryDetailID]) AS [PolicyHistoryDetailID]
      ,[PolicyHistoryID]
      ,[EvaluatedServer]
      ,[EvaluationDateTime]
      ,[MonthYear]
      ,[EvaluatedPolicy]
      ,[policy_id]
      ,[CategoryName]
      ,[EvaluatedObject]
      ,[PolicyResult]
      ,[ExceptionMessage]
      ,[PolicyHistorySource]
     INTO #rows_keep
  FROM [policy].[PolicyHistoryDetail]
  
  GROUP BY [PolicyHistoryID]
      ,[EvaluatedServer]
      ,[EvaluationDateTime]
      ,[MonthYear]
      ,[EvaluatedPolicy]
      ,[policy_id]
      ,[CategoryName]
      ,[EvaluatedObject]
      ,[PolicyResult]
      ,[ExceptionMessage]
      ,[PolicyHistorySource]
     
      DELETE
      FROM [policy].[PolicyHistoryDetail]
      WHERE not [PolicyHistoryDetailID] in (SELECT [PolicyHistoryDetailID] FROM #rows_keep)
Jan 3, 2012 at 11:09 AM

There is bug feedback for this problem, it can be voted or can be added watch list

https://connect.microsoft.com/SQLServer/feedback/details/714689/sqlpss-cmdlet-invoke-policyevaluation-multiplies-the-data-in-outputxml-file-while-multiple-servers-evaluated-when-some-error-occurs

Coordinator
Jan 4, 2012 at 2:13 PM

Thank you for submitting the bug!

Sep 27, 2012 at 8:27 PM
Edited Oct 4, 2012 at 3:34 PM

I changed the stored procedure to use a cursor that prevents duplicate row insertion.  Code to modify it is below.

 

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [policy].[epm_LoadPolicyHistoryDetail]
AS
	DECLARE @PolicyHistoryID		INT
	DECLARE @EvaluatedServer		NVARCHAR(128)
	DECLARE @EvaluationDateTime		DATETIME
	DECLARE @EvaluatedPolicy		NVARCHAR(128)
	DECLARE @EvaluatedObject		NVARCHAR(256)
	DECLARE @PolicyResult			NVARCHAR(5)
	DECLARE @ExceptionMessage		NVARCHAR(MAX)
	DECLARE @ResultDetail			XML
	DECLARE @Policy_id				INT
	DECLARE @CategoryName			NVARCHAR(128)
	DECLARE @MonthYear				NVARCHAR(14)
	DECLARE @PolicyHistorySource	NVARCHAR(50)
	DECLARE @EvaluationResults		NVARCHAR(MAX)

	DECLARE db_cursor CURSOR FOR
		WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF)
		SELECT 
			PH.PolicyHistoryID
			, PH.EvaluatedServer
			, PH.EvaluationDateTime
			, PH.EvaluatedPolicy
			, Res.Expr.value('(../DMF:TargetQueryExpression)[1]', 'nvarchar(150)') AS EvaluatedObject
			, (CASE WHEN Res.Expr.value('(../DMF:Result)[1]', 'nvarchar(150)')= 'FALSE' AND Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)') = ''
				   THEN 'FAIL' 
				   WHEN Res.Expr.value('(../DMF:Result)[1]', 'nvarchar(150)')= 'FALSE' AND Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)')<> ''
				   THEN 'ERROR'
				   ELSE 'PASS' 
				END) AS PolicyResult
			, Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)') AS ExceptionMessage
			, CAST(Expr.value('(../DMF:ResultDetail)[1]', 'nvarchar(max)')AS XML) AS ResultDetail
			, p.policy_id
			, c.name AS CategoryName
			, datename(month, EvaluationDateTime) + ' ' + datename(year, EvaluationDateTime)  AS MonthYear
			, 'PowerShell EPM Framework'
		FROM policy.PolicyHistory AS PH
		INNER JOIN msdb.dbo.syspolicy_policies AS p 
			ON p.name = PH.EvaluatedPolicy
		INNER JOIN msdb.dbo.syspolicy_policy_categories AS c 
			ON p.policy_category_id = c.policy_category_id
		CROSS APPLY EvaluationResults.nodes('
		declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
		//TargetQueryExpression'
		) AS Res(Expr)
		WHERE NOT EXISTS (SELECT * 
			FROM policy.PolicyHistoryDetail PHD
			WHERE PHD.PolicyHistoryID = PH.PolicyHistoryID);

	OPEN db_cursor
		FETCH NEXT FROM db_cursor INTO @PolicyHistoryID,@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluatedObject,@PolicyResult,@ExceptionMessage,@ResultDetail,@policy_id,@CategoryName,@MonthYear,@PolicyHistorySource

		WHILE @@FETCH_STATUS = 0   
		BEGIN
			IF NOT EXISTS(SELECT * 
						  FROM policy.PolicyHistoryDetail PHD
						  WHERE PHD.EvaluatedServer = @EvaluatedServer AND PHD.EvaluatedPolicy = @EvaluatedPolicy AND PHD.EvaluatedObject = @EvaluatedObject AND PHD.EvaluationDateTime = @EvaluationDateTime)
				BEGIN
					--Insert the evaluation results
					INSERT INTO policy.PolicyHistoryDetail	(PolicyHistoryID,EvaluatedServer,EvaluationDateTime,EvaluatedPolicy,EvaluatedObject,PolicyResult,ExceptionMessage,ResultDetail,policy_id,CategoryName,MonthYear,PolicyHistorySource)
						VALUES (@PolicyHistoryID,@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluatedObject,@PolicyResult,@ExceptionMessage,@ResultDetail,@policy_id,@CategoryName,@MonthYear,@PolicyHistorySource)
				END
			FETCH NEXT FROM db_cursor INTO @PolicyHistoryID,@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluatedObject,@PolicyResult,@ExceptionMessage,@ResultDetail,@policy_id,@CategoryName,@MonthYear,@PolicyHistorySource
		END

	CLOSE db_cursor
	DEALLOCATE db_cursor;


	DECLARE db_cursor CURSOR FOR
		WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF)
		SELECT 
			PH.EvaluatedServer
			, PH.EvaluationDateTime
			, PH.EvaluatedPolicy
			, Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)') AS ExceptionMessage
		FROM policy.PolicyHistory AS PH
		INNER JOIN msdb.dbo.syspolicy_policies AS p 
			ON p.name = PH.EvaluatedPolicy
		INNER JOIN msdb.dbo.syspolicy_policy_categories AS c 
			ON 	p.policy_category_id = c.policy_category_id
		CROSS APPLY EvaluationResults.nodes('
		declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
		//DMF:ServerInstance'
		) AS Res(Expr)
		WHERE EvaluationResults.exist('declare namespace DMF="http://schemas.microsoft.com/sqlserver/DMF/2007/08";
			 //DMF:EvaluationDetail'
			) = 0	
		AND Res.Expr.value('(../DMF:Result)[1]', 'nvarchar(150)')= 'FALSE' 
		AND Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)')<> ''
		AND NOT EXISTS (SELECT * 
		FROM policy.PolicyHistoryDetail PHD
		WHERE PHD.PolicyHistoryID = PH.PolicyHistoryID);

	OPEN db_cursor
		FETCH NEXT FROM db_cursor INTO 	@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluationResults

		WHILE @@FETCH_STATUS = 0   
		BEGIN
			IF NOT EXISTS(SELECT *
						  FROM policy.EvaluationErrorHistory EEH
						  WHERE EEH.EvaluatedServer = @EvaluatedServer
						    AND EEH.EvaluatedPolicy = @EvaluatedPolicy
							AND EEH.EvaluationResults = @EvaluationResults
							AND @EvaluationDateTime = @EvaluationDateTime)
				BEGIN
					--Insert the error records
					INSERT INTO policy.EvaluationErrorHistory(EvaluatedServer,EvaluationDateTime,EvaluatedPolicy,EvaluationResults)
						VALUES (@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluationResults)
				END

			FETCH NEXT FROM db_cursor INTO @EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluationResults
		END
	CLOSE db_cursor
	DEALLOCATE db_cursor;


	DECLARE db_cursor CURSOR FOR
	WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF)
	SELECT 
		 PH.PolicyHistoryID
		 , PH.EvaluatedServer
		 , PH.EvaluationDateTime
		 , PH.EvaluatedPolicy
		 , 'No Targets Found' AS EvaluatedObject
		 , 'PASS' AS PolicyResult
		 , Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)') AS ExceptionMessage
		 , NULL AS ResultDetail
		 , p.policy_id
		 , c.name AS CategoryName
		 , datename(month, EvaluationDateTime) + ' ' + datename(year, EvaluationDateTime)  AS MonthYear
		, 'PowerShell EPM Framework'
	FROM policy.PolicyHistory AS PH
	INNER JOIN msdb.dbo.syspolicy_policies AS p 
		ON p.name = PH.EvaluatedPolicy
	INNER JOIN msdb.dbo.syspolicy_policy_categories AS c 
		ON 	p.policy_category_id = c.policy_category_id
	CROSS APPLY EvaluationResults.nodes('
	declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
	//DMF:ServerInstance'
	) AS Res(Expr)
	WHERE EvaluationResults.exist('declare namespace DMF="http://schemas.microsoft.com/sqlserver/DMF/2007/08";
		 //DMF:EvaluationDetail'
		) = 0	
	AND Expr.value('(../DMF:Exception)[1]', 'nvarchar(max)')= ''
	AND NOT EXISTS (SELECT * 
	FROM policy.PolicyHistoryDetail PHD
	WHERE PHD.PolicyHistoryID = PH.PolicyHistoryID)

	OPEN db_cursor
		FETCH NEXT FROM db_cursor INTO @PolicyHistoryID,@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluatedObject,@PolicyResult,@ExceptionMessage,@ResultDetail,@policy_id,@CategoryName,@MonthYear,@PolicyHistorySource

		WHILE @@FETCH_STATUS = 0   
		BEGIN
			IF NOT EXISTS (SELECT * 
						   FROM policy.PolicyHistoryDetail PHD
						   WHERE PHD.EvaluatedServer = @EvaluatedServer
							 AND PHD.EvaluatedPolicy = @EvaluatedPolicy
							 AND PHD.EvaluatedObject = @EvaluatedObject
							 AND PHD.EvaluationDateTime = @EvaluationDateTime)
				BEGIN
					--Insert the policies that evaluated with no target	
					INSERT INTO policy.PolicyHistoryDetail	(PolicyHistoryID,EvaluatedServer,EvaluationDateTime,EvaluatedPolicy,EvaluatedObject,PolicyResult,ExceptionMessage,ResultDetail,policy_id,CategoryName,MonthYear,PolicyHistorySource)
						VALUES (@PolicyHistoryID,@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluatedObject,@PolicyResult,@ExceptionMessage,@ResultDetail,@policy_id,@CategoryName,@MonthYear,@PolicyHistorySource)
				END
			FETCH NEXT FROM db_cursor INTO @PolicyHistoryID,@EvaluatedServer,@EvaluationDateTime,@EvaluatedPolicy,@EvaluatedObject,@PolicyResult,@ExceptionMessage,@ResultDetail,@policy_id,@CategoryName,@MonthYear,@PolicyHistorySource
		END
	CLOSE db_cursor
	DEALLOCATE db_cursor;