How to retrieve the information from policies created by a user and policies failed and what caused it to fail and where it failed?

Problem: How to retrieve the information from policies created by a user and policies failed and what caused it to fail and where it failed?

Solution:

Policy-Based Management Views (Transact-SQL)

Policy-Based Management has the following views for displaying information about Policy-Based Management policies, conditions, expressions, groups, and filters. These views are in the msdb database and are owned by the dbo schema.

The following table of views shows the information about which views present what metadata.

System Object Catalog Views	Contains information about
syspolicy_conditions	Displays one row for each Policy-Based Management condition in the instance of SQL Server. syspolicy_conditions belongs to the dbo schema in the msdb database. The following table describes the columns in the syspolicy_conditions view.
syspolicy_policies	Displays one row for each Policy-Based Management policy in the instance of SQL Server. syspolicy_policies belongs to the dbo schema in the msdb database. The following table describes the columns in the syspolicy_policies view.
syspolicy_policy_execution_history	Displays the time when policies were executed, the result of each execution, and details about errors if any occurred. The following table describes the columns in the syspolicy_policy_execution_history view.
syspolicy_policy_execution_history_details	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.
syspolicy_policy_group_subscriptions	Displays one row for each Policy-Based Management policy category in the instance of SQL Server. Policy categories help you organize policies when you have many policies. The following table describes the columns in the syspolicy_policy_groups view.
syspolicy_policy_groups	Displays one row for each Policy-Based Management subscription in the instance of SQL Server. Each row describes a target and policy category pair. The following table describes the columns in the syspolicy_policy_group_subscriptions view.
syspolicy_system_health_state	Displays one row for each Policy-Based Management policy and target query expression combination. Use the syspolicy_system_health_state view to programmatically check the policy health of the server. The following table describes the columns in the syspolicy_system_health_state view.

Example 1:

In this example, I would like to show where a policy is failed and on which server it is executed and what time it is executed.

SELECT spc.NAME AS policy_condition_name,

                sp.NAME AS policy_name,

                CASE

                                WHEN speh.result = 0

                                                THEN 'Failure'

                                WHEN speh.result = 1

                                                THEN 'Success'

                                END AS Policy_condition_execution_Result

                                ,spehd.target_query_expression

                                ,spehd.result_detail

FROM msdb.dbo.syspolicy_policies sp

INNER JOIN msdb.dbo.syspolicy_conditions spc

                ON sp.condition_id = spc.condition_id

INNER JOIN msdb.dbo.syspolicy_policy_execution_history speh

                ON sp.policy_id = speh.policy_id

                LEFT JOIN msdb.dbo.syspolicy_policy_execution_history_details spehd

                ON speh.history_id=spehd.history_id

WHERE speh.result=0

OUTPUT:

policy_condition_name	policy_name	Policy_condition_execution_Result	target_query_expression
ProcedureCondition	StoredProcNamingConvention	Failure	SQLSERVER:\SQL\TROPPLTMSAMBU\MSSQLSERVER2014\Databases\Test\StoredProcedures\dbo.sp_testset

As we can see there is an error when a procedure with a name starting with sp_ it is blocked by the policy we created and the conditions are prevented to rollback this transaction.