EPM and SCOM monitoring

Mar 18, 2013 at 3:51 PM
Hi All,

Is it possible to get SCOM to monitor if a job has returned some policy failures?

EG
I have a CMS setup for the EPM. A policy is created to check that a full backup has occured in the last 24 hours.

If there are 4 instances having this policy run against and 1 instance fails with some of the databases not being backed up in this time period is it possible to raise a single alert into SCOM with the instance and database which has failed ?

This is a bit of a vague request i know.

I know that the policies using this framework are "on demand" and that they do not raise an error if they fail so not sure how it can be done - I also know that if i create this policy/condition on each server then I can set up an alert to fire into the windows event log but then that defeats the point as I may as well have scom monitor for each individual job.

The point of this is to have a single alert raised with all the relevant details in it of the failures instead of being bombarded with hundreds of alerts (which ideally shouldn't happen but hey... not a perfect world :) )

Cheers,
Chris.
Mar 26, 2013 at 1:07 AM
I have a trigger setup on the PolicyHistoryDetail table that emails me when the category that is evaluated has failures.

Here's the trigger code
BEGIN -- trigger
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  DECLARE @ProfileName varchar(20) = '<< your database mail profile >>';
  DECLARE @emailRecipient varchar(200) = '<< your email address here >>';
  DECLARE @emailSubject varchar(150);
  DECLARE @emailMessage varchar(4000);
  DECLARE @policyCategory varchar(150);
  DECLARE @rtn int;
  DECLARE @err int;

  IF OBJECT_ID('Tempdb..#PolicyFailures') IS NOT NULL DROP TABLE #PolicyFailures;
  CREATE TABLE #PolicyFailures (
    [PolicyHistoryDetailID] [int] NULL,
      [EvaluatedServer] [nvarchar](128) NULL,
      [EvaluationDateTime] [datetime] NULL,
      [EvaluatedPolicy] [nvarchar](128) NULL,
      [CategoryName] [nvarchar](128) NULL,
      [EvaluatedObject] [nvarchar](256) NULL,
      [ResultDetail] [xml] NULL,
  );

  SET @emailSubject = '';
  SET @emailMessage = '';
  SET @policyCategory = '';

  -- check if a failure record was inserted
  IF EXISTS(SELECT 1 FROM inserted WHERE [PolicyResult] = 'FAIL')
  BEGIN -- failure record exists

    -- select out only the failure records
    INSERT INTO #PolicyFailures (
      [PolicyHistoryDetailID],[EvaluatedServer],[CategoryName],[EvaluatedPolicy]
      ,[EvaluatedObject],[EvaluationDateTime],[ResultDetail]
    )
    SELECT
      [PolicyHistoryDetailID],[EvaluatedServer],[CategoryName],[EvaluatedPolicy]
      ,[EvaluatedObject],[EvaluationDateTime],[ResultDetail]
    FROM inserted
    WHERE [PolicyResult] = 'FAIL';

    SET @policyCategory = ISNULL((SELECT TOP 1 dtl.[CategoryName] FROM #PolicyFailures dtl),'UNKNOWN');
    SET @emailSubject = CAST(SERVERPROPERTY('ServerName') as varchar(50)) + ': Policy evaluation failure in category ' + @policyCategory;

    -- extract details of policy failure and wrap in html TR/TD tags
    WITH errorDetail (
        [EvaluatedServer],[EvaluatedPolicy],[EvaluatedObject],[EvaluationDate]
        ,[OpType],[Value],[AttributeResultValue],[FunctionResultValue]
    )
    AS (
      SELECT 
        [EvaluatedServer],[EvaluatedPolicy],[EvaluatedObject],[EvaluationDateTime]
        ,isnull([ResultDetail].value('(/Operator/OpType)[1]', 'varchar(10)'), 'UNK') as [OpType]
        ,isnull([ResultDetail].value('(/Operator/Constant/Value)[1]', 'varchar(20)'), '0') as [Value]
        ,isnull([ResultDetail].value('(/Operator/Attribute/ResultValue)[1]', 'varchar(200)'), 'UNK') as [AttributeResultValue]
        ,isnull([ResultDetail].value('(/Operator/Function/ResultValue)[1]', 'varchar(200)'), 'UNK') as [FunctionResultValue]
      FROM #PolicyFailures dtl
    )
    SELECT @emailMessage = CAST(
        (
          SELECT 
            td = [EvaluatedServer], ''
            , td = REPLACE([EvaluatedPolicy], '_',' '), ''
            , td = REPLACE([EvaluatedObject], '\',' \'), ''
            , td = convert(varchar(30), [EvaluationDate], 120), ''
            , td = [OpType] + ' ' + [Value], ''
            , td = CASE 
                    WHEN [FunctionResultValue] = 'UNK' THEN [AttributeResultValue]
                    WHEN [FunctionResultValue] <> 'UNK' THEN [FunctionResultValue]
                    ELSE 'Undetermined result'
                    END, ''
          FROM errorDetail
          ORDER BY [EvaluationDate]
          FOR XML PATH('tr'), TYPE  
        ) 
        AS NVARCHAR(MAX));          
          
    -- wrap failure details in html body and table tags
    SET @emailMessage = N'<HTML><BODY><H3>Policy Failures in category ' + @policyCategory
      + N'</H3><table border="1"><tr><th>Evaluated Server</th>'
      + N'<th>Evaluated Policy</th><th>Evaluated Object</th>'
      + N'<th>Evaluation Date</th><th>Expected Result</th><th>Actual Result</th></tr>' 
      + @emailMessage + N'</table></BODY></HTML>';

    -- send the email
    exec [msdb].[dbo].[sp_send_dbmail]
      @profile_name = @ProfileName
      , @recipients = @emailRecipient
      , @subject = @emailSubject
      , @importance = 'HIGH'
      , @body_format = 'HTML'
      , @body = @emailMessage

    SET @err = @@ERROR;
    IF @rtn <> 0 OR @err <> 0
    BEGIN -- Error sending Policy failure message
      RAISERROR('Problem sending Policy failure message, Return Value: %i, SQL Error: %i', 16, 1, @err, @rtn);
      
    END -- Error sending Policy failure message
      
  END -- failure record exists
  
END -- trigger
Mar 27, 2013 at 1:43 PM
Thanks Phil,

This is certainly worth looking into.

Kind Regard,
Chris.