SQL Server - Policy based management - System Views

Below query will provide the Created policies , Conditions imposed and Policy evaluation history details :

USE msdb
GO

SELECT P.name AS PolicyName, 
C.name AS ConditionName, 
C.facet AS Facet ,
tmp.Name [Option],
tmp.Operator,
C.Obj_name ExpressionToBeValidated,
tmp.ResultValue, 
tmp.Result ,
CASE WHEN P.execution_mode = 0 THEN  'On demand'
     WHEN P.execution_mode = 1 THEN  'On change: prevent'
     WHEN P.execution_mode = 2 THEN  'On change: log only'
     WHEN P.execution_mode = 4 THEN  'On schedule'
ELSE NULL END  EvaluationMode,
PEHD.execution_date, 
CASE WHEN PEHD.result = 0 THEN 'success'
     WHEN PEHD.result = 1 THEN 'failure' 
ELSE NULL END PolicyEvaluation ,
PEHD.target_query_expression ,
CASE WHEN P.is_enabled = 1 THEN 'Yes'
     WHEN P.is_enabled = 0 THEN 'No' 
ELSE NULL END PolicyEnabled  ,
P.created_by
--PEHD.result_detail, 
--PEHD.exception_message, 
--PEHD.exception 
FROM msdb.dbo.syspolicy_policies AS P
JOIN msdb.dbo.syspolicy_conditions AS C
    ON P.condition_id = C.condition_id
JOIN msdb.dbo.syspolicy_policy_execution_history AS PEH
    ON P.policy_id = PEH.policy_id
JOIN msdb.dbo.syspolicy_policy_execution_history_details AS PEHD
    ON PEH.history_id = PEHD.history_id
JOIN (SELECT DISTINCT i.value('Attribute[1]/Name[1]', 'NVARCHAR(50)') Name ,
    i.value('OpType[1]', 'NVARCHAR(50)') Operator ,
    --i.value('Constant[1]/Value[1]', 'NVARCHAR(50)') OpType ,
    i.value('Attribute[1]/ResultValue[1]', 'NVARCHAR(200)') ResultValue ,
    i.value('ResultValue[1]', 'NVARCHAR(10)') Result ,
    history_id  FROM (
SELECT CAST(result_detail AS XML) XmlData , history_id
FROM syspolicy_policy_execution_history_details ) PEHD
CROSS APPLY PEHD.XmlData.nodes('/Operator') x(i) ) tmp
   ON tmp.history_id = PEHD.history_id
WHERE  P.is_system = 0 
AND C.is_system = 0

Reference :

• http://technet.microsoft.com/en-us/library/bb510742.aspx

See Also :

• SQL Server - Miscellaneous