syspolicy_policy_execution_history_details (Transact-SQL)
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. |
Remarks
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_policy_execution_history_details and 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 ;
Permissions
Requires membership in the PolicyAdministratorRole role in the msdb database.
See Also
Reference
Policy-Based Management Views (Transact-SQL)