System tables for Policy based Management

 System tables for Policy based Management

Metadata: 

Database: msdb

Db objects:

msdb .dbo.syspolicy_conditions
	syspolicy_conditions_internal
	syspolicy_execution_internal
	syspolicy_facet_events
	syspolicy_management_facets
	syspolicy_policies
	syspolicy_policies_internal
	syspolicy_policy_categories
	syspolicy_policy_execution_history
	syspolicy_policy_execution_history_details
	syspolicy_policy_execution_history_details_internal
	syspolicy_policy_execution_history_internal
	syspolicy_target_sets
	syspolicy_target_sets_internal

Querying meta data:

USE MSDB

GO

--/* What policies exist on the server? */

SELECT p.name AS 'policy_name',

       p.is_enabled,

       p.execution_mode,

       c.name AS 'condition_name',

       c.facet,

       p.date_created

FROM syspolicy_policies p

     INNER JOIN syspolicy_conditions c

     ON p.condition_id = c.condition_id

ORDER  BY p.name

--/* Execution history of policy violations */

SELECT

            h.history_id,

            pp.name  'policy name',

            pp.execution_mode,

            h.result,

            h.start_date,

            h.end_date,

            d.target_query_expression,

            CAST(d.result_detail AS XML) 'result'

FROM syspolicy_policies AS pp

INNER JOIN syspolicy_policy_execution_history  h

ON h.policy_id = pp.policy_id

INNER JOIN syspolicy_policy_execution_history_details  d

ON d.history_id = h.history_id

ORDER  BY h.history_id