Applies to: SQL Server
Displays the condition expressions that were executed, the targets of the expressions, the result of each execution, and details about errors if any occurred. The following table describes the columns in the syspolicy_execution_history_details view.
|Column name||Data type||Description|
|detail_id||bigint||Identifier of this record. Each record represents the attempt to evaluate or enforce one condition expression in a policy. If applied to multiple targets, each condition will have a detail record for each target.|
|history_id||bigint||Identifier of the history event. Each history event represents one try to execute a policy. Because a condition can have several condition expressions and several targets, a history_id can create several detail records. Use the history_id column to join this view to the syspolicy_policy_execution_history view.|
|target_query_expression||nvarchar(max)||Target of the policy and syspolicy_policy_execution_history view.|
|execution_date||datetime||Date and time that this detail record was created.|
|result||bit||Success or failure of this target and condition expression evaluation:
0 (success) or 1 (failure).
|result_detail||nvarchar(max)||Result message. Only available if provided by the facet.|
|exception_message||nvarchar(max)||Message generated by the exception if one occurred.|
|exception||nvarchar(max)||Description of the exception if one occurred.|
When you are troubleshooting Policy-Based Management, query the syspolicy_policy_execution_history_details view to determine which target and condition expression combinations failed, when they failed, and review related errors.
The following query combines the
syspolicy_policy_execution_history_details view with the
syspolicy_policies views to display the name of the policy, the name of the condition, and details about failures.
SELECT Pol.name AS Policy, Cond.name AS Condition, PolHistDet.target_query_expression, PolHistDet.execution_date, PolHistDet.result, PolHistDet.result_detail, PolHistDet.exception_message, PolHistDet.exception FROM msdb.dbo.syspolicy_policies AS Pol JOIN msdb.dbo.syspolicy_conditions AS Cond ON Pol.condition_id = Cond.condition_id JOIN msdb.dbo.syspolicy_policy_execution_history AS PolHist ON Pol.policy_id = PolHist.policy_id JOIN msdb.dbo.syspolicy_policy_execution_history_details AS PolHistDet ON PolHist.history_id = PolHistDet.history_id WHERE PolHistDet.result = 0 ;
Requires membership in the PolicyAdministratorRole role in the msdb database.