Below query will provide the Created policies , Conditions imposed and Policy evaluation history details :
Reference :
See Also :
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 :
See Also :
No comments:
Post a Comment