Motherofallgeeks

go
CREATE TYPE PolicyType AS TABLE
(PolicyName varchar(8000),
PolicyTarget varchar(8000),
ConditionName varchar(1000),
ConditionDesc varchar(1000)
)
GO
/******************************************************************************************************/
IF exists(SELECT 1 from sys.objects where name = ‘Get_Policy_Failure_Details’)
DROP PROCEDURE Get_Policy_Failure_Details
GO
/******************************************************************************************************
* Created By Mother of All Geeks
* Purpose is to provide more detailas about an evaluation failure in a policy
* Note A row is written to syspolicy_policy_execution_history_details only on exception
CREATE TYPE PolicyType AS TABLE
(PolicyName varchar(8000),
PolicyTarget varchar(8000),
ConditionName varchar(1000),
ConditionDesc varchar(1000)
)
SAMPLE EXEC CALL
DECLARE @FailedPolicies PolicyType
INSERT INTO @FailedPolicies
SELECT distinct Pol.name,
PolHistDet.target_query_expression,
cond.name,
cond.description –, PolHist.start_date,PolHist.end_date, PolHistDet.execution_date
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
and PolHistDet.execution_date > convert(date,gETDATE())
and datediff(mi,PolHistDet.execution_date,GETDATE()) < 10
exec Get_Policy_Failure_Details ‘MOFG@motherofallgeeks.com’,MyMailProfile, @FailedPolicies
*******************************************************************************************************/

CREATE PROCEDURE Get_Policy_Failure_Details

(@TO varchar(255)
,@profile_name varchar(255)
,@FailedPolicies POLICYTYPE READONLY
)
AS

BEGIN
DECLARE @PolicyName varchar(60)
DECLARE @Target varchar(255)
DECLARE @execution_date datetime
DECLARE @histId int
DECLARE @detailHistId int
IF exists (SELECT 1 from @FailedPolicies)
– If there is a policy in failure
BEGIN
DECLARE @body varchar(max)
SELECT @body = ‘The Following Policy has failed evaluation:
‘

SELECT @body = COALESCE(@body + ‘

‘)
FROM @FailedPolicies

exec msdb..sp_send_dbmail @profile_name = @profile_name
, @recipients = @TO
, @subject = ‘Policy Evaluation Failure’
, @body_format = ‘HTML’
, @body = @body

END
ELSE
RETURN
END