Export (0) Print
Expand All

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.

Applies to: SQL Server (SQL Server 2008 through current version).

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_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 ;

Requires membership in the PolicyAdministratorRole role in the msdb database.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft