Enterpise Policy Management Framework – Skip the Report

Policy-Based Management in SQL Server is a wonderful and underused feature. The Enterprise Policy Management Framework available through Codeplex further enhances this with reports and history tables, but if you have ever used the main report, you know how distressingly slow it can be. Not only that, but drilling through the report to find failed policies on instances is an extremely painful process. Visual reports are pretty, and the aggregation is a really cool feature, but expanding nodes are not practical for a large environment.


It depressed me just pulling up the slow report to screen shot this. Expanding nodes is even worse.

So while I use EPMF, I do not check the reports daily. Instead, I script the daily failures and errors to quickly review them. Below are three scripts that can provide policy results, use whichever you prefer.

Script 1: Short and sweet. This provides all failures and errors for the current day, including the unshredded XML evaluation results. This is my go to script each morning. Find failures and investigate.

FROM EESqlReports.policy.PolicyHistoryDetail phd
INNER JOIN EESqlReports.policy.PolicyHistory ph ON ph.PolicyHistoryID = phd.PolicyHistoryID
PolicyResult <> 'PASS'
ORDER BY PolicyResult DESC,Policy_id,phd.EvaluatedServer;

Script 2: This script is essentially the same as above, but it includes the evaluated object or database along with a cleaner XML result. While still XML, it is easier to read, and the best solution I’ve found to read the evaluation results reliably for any policy. Sometimes the XML will not provide an actual value on a failed result. I’ve yet to discover what causes this. Theories include network issues or unexpected values.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08'AS DMF)
,[CheckParam1] = res.Expr.value('Function[1]/Constant[2]/Value[1]','nvarchar(100)')
,[ExpectedValue1] = res.Expr.value('Constant[1]/Value[1]','nvarchar(100)')
,[ActualValue1] = res.Expr.value('Function[1]/ResultValue[1]','nvarchar(100)')
FROM policy.PolicyHistoryDetail phd
INNER JOIN policy.PolicyHistory ph on ph.PolicyHistoryID = phd.PolicyHistoryID
CROSS APPLY ResultDetail.nodes('Operator') AS Res(Expr)
PolicyResult <> 'PASS'
ORDER BY PolicyResult DESC,EvaluatedServer;

Script 3: This script attempts to shred the XML results, showing you the check parameter, expected value, and actual value. This sample script is very short because shredding a policy result gets complicated fast. I’m only shredding the first line of a policy here. Since most policies have more than one parameter and thus values, this script gets excessive fast.  Further, if the policy doesn’t fit the XML format scripted perfectly, you’ll get NULL results. Before you know it, you have to tailor-make a script for almost every policy in your environment. It’s just not effective, and why I suggest just reading the XML from the above script if you really need to view results. As terrible as XML is to read, it’s better than crafting a script for dozens or even hundreds of policies.

<span style="color: #808080; font-family: Consolas; font-size: small;">Consolas; font-size: small;"><span style="color: #808080; font-family: Consolas; font-size: small;">WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/DMF/2007/08' AS DMF)
,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'
END) AS PolicyResult
,CAST(Expr.value('(../DMF:ResultDetail)[1]','nvarchar(max)') AS XML) AS ResultDetail
FROM policy.PolicyHistory PH
INNER JOIN policy.PolicyHistoryDetail phd ON ph.PolicyHistoryID = phd.PolicyHistoryID
INNER JOIN msdb.dbo.syspolicy_policies p ON p.name = PH.EvaluatedPolicy
CROSS APPLY EvaluationResults.nodes('
declare default element namespace "http://schemas.microsoft.com/sqlserver/DMF/2007/08";
//TargetQueryExpression') AS Res(Expr)
WHERE phd.PolicyResult = 'FAIL'
ORDER BY PolicyResult DESC,EvaluatedServer;</span>



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s