Vidhya Sagar – Blog

In this article we are going to configure alerts for policy management. Alerts are triggered when a violation takes place in a particular policy that uses any of the three automated evaluation mode. Each of these evaluation mode will return an error number starting with 340*. You can use these alerts to notify either policy administrator or the database administrator who needs to make sure these values are under company standards. Upon receiving these alerts they can work with the user who has violated the policies or company standards. Error number for each of these evaluation mode is given in the below table.

Ok, Now it’s time to test these alerts. I’ve already configured a policy (On change: prevent) in my local machine to check the table names which should be suffixed with ‘tbl_’, if this naming convention fails then it should throw a error. Here in this case I’m going to evaluate the policy as “On change: prevent (if automatic)” hence the error number will be 34050 (from the table above). Lets create a alert and an operator to check this.

Operator:

I’ve created a operator named Policycheck and provided a mail address to send the alerts to that mail ID. I’m going to use this operator in the alert that is going to be configured for PBM

Alert:

 

The next step in configuring the PBM alert is to create a alert and provide the error number.

I’ve created a alert named “Tbl-Policy Check”. In my testing case the error number will be 34050 since I’ve used On Change : Prevent mode with automatic evaluation

For more on error number see the table above

 

 

 

 

In the response page of the alert I’ve selected the operator which is created in the step1 and assigned to mail to that operator when this alert is invoked, i.e. when the policy fails this alert will be invoked.

 

 

 

 

 

Testing Policy Based Management Alert

Ok now we have created the alert for error number 34050, lets create a table without the naming convention mentioned above. When I create a that table PBM should automatically prevent the table creation and invoke the alert. Upon invoking the alert it should send a mail to the operator specified. When I created the table PBM thrown a error below

You can also find that an event is inserted in the application log of event viewer.

Cool, PBM has prevented the table creation. Ok lets check whether I got the mail for this failure, great I got the mail below from the operator

Consideration on PBM Alerts

Be aware of the following additional considerations about alerts:

• Alerts are raised only for policies that are enabled. Because On demand policies cannot be enabled, alerts are not raised for policies that are executed on demand.
• If the action you want to take includes sending an e-mail message, you must configure a mail account.
• Alert security: When policies are evaluated on demand, they execute in the security context of the user. To write to the error log, the user must have ALTER TRACE permissions or be a member of the sysadmin fixed server role. Policies that are evaluated by a user that has less privileges will not write to the event log, and will not fire an alert. The automated execution modes execute as a member of the sysadmin role. This allows the policy to write to the error log and raise an alert.

Policy based management is a new feature that allows the administration simpler, In addition to this if you configure the alerts for PBM then it will make the administration much more simpler to make the standards correct. Most of the critical business needs to be notified when there is a deviation in their standards, you can implement policy based management with alerts which will help the administrators to resolve this as soon as possible.

Need to discuss on this topic, please post your queries at Discussion Board.

Configuring Alerts to Notify Policy deviations in Policy Based Management, 5.0 out of 5 based on 1 rating