WHEN c.name IS NULL THEN 'Default' ELSE c.name END as CategoryName , p.Name as PolicyName , CASE mandate_database_subscriptions WHEN 0 THEN 'No' ELSE 'Yes' END AS 'Mandated?' FROM syspolicy_policy_categories as c FULL OUTER JOIN syspolicy_policies as p ON c.policy_category_id = p.policy_category_id ORDER BY CategoryName We can extend this query so that we see the results of the most recent evaluation of every policy in every category: SELECT CASE WHEN c.name IS NULL THEN 'Default' ELSE c.name END as CategoryName , p.Name as PolicyName , CASE mandate_database_subscriptions WHEN 0 THEN 'No' ELSE 'Yes' END AS 'Mandated?' , target_query_expression , CASE WHEN result IS NULL THEN 'N/A' WHEN result = 1 THEN 'OK' ELSE 'Bad' END AS 'Last evaluation result' FROM syspolicy_policy_categories as c FULL OUTER JOIN syspolicy_policies as p ON c.policy_category_id = p.policy_category_id LEFT JOIN syspolicy_system_health_state AS h on p.policy_id = h.policy_id ORDER BY CategoryName The following script will show you the target for the policy that was created in the demonstration earlier in this paper: SELECT p.name, t.type_skeleton AS Target FROM dbo.syspolicy_policies AS p INNER JOIN dbo.syspolicy_object_sets as o ON p.object_set_id = o.object_set_id INNER JOIN dbo.syspolicy_target_sets as t ON o.object_set_id = t.object_set_id
WHEN c.name IS NULL THEN 'Default' ELSE c.name END as CategoryName , p.Name as PolicyName , CASE mandate_database_subscriptions WHEN 0 THEN 'No' ELSE 'Yes' END AS 'Mandated?' FROM syspolicy_policy_categories as c FULL OUTER JOIN syspolicy_policies as p ON c.policy_category_id = p.policy_category_id ORDER BY CategoryName We can extend this query so that we see the results of the most recent evaluation of every policy in every category: SELECT CASE WHEN c.name IS NULL THEN 'Default' ELSE c.name END as CategoryName , p.Name as PolicyName , CASE mandate_database_subscriptions WHEN 0 THEN 'No' ELSE 'Yes' END AS 'Mandated?' , target_query_expression , CASE WHEN result IS NULL THEN 'N/A' WHEN result = 1 THEN 'OK' ELSE 'Bad' END AS 'Last evaluation result' FROM syspolicy_policy_categories as c FULL OUTER JOIN syspolicy_policies as p ON c.policy_category_id = p.policy_category_id LEFT JOIN syspolicy_system_health_state AS h on p.policy_id = h.policy_id ORDER BY CategoryName The following script will show you the target for the policy that was created in the demonstration earlier in this paper: SELECT p.name, t.type_skeleton AS Target FROM dbo.syspolicy_policies AS p INNER JOIN dbo.syspolicy_object_sets as o ON p.object_set_id = o.object_set_id INNER JOIN dbo.syspolicy_target_sets as t ON o.object_set_id = t.object_set_id
SQLServer2008PolicyBasedManagement.docx