20
eMail Server Scripts

eMail Server provides scripts that help you automate routine tasks, such as collecting or replicating data. This chapter contains information on scripts for use with the eMail Server system, including:

• Scripts for Message-Billing Data Collection

• Parameters for Billing

• System Wide Virus Removal Script

• Bounce Back Messages Script (over recipient quota)

Scripts for Message-Billing Data Collection

eMail Server collects and stores information about the number and type of messages processed (sent) by the servers, and the number of messages received by users during a given time period. Using this data, you can create reports detailing items such as:

• Number of messages sent from your eMail Server system

• Number of messages sent by each user on your eMail Server system

• Number of messages received by your eMail Server system

• Number of messages received by each user on your eMail Server system

• Number of messages returned to each user on your eMail Server system

• Number of addresses sent by each user on your eMail Server system

• Average number of addresses for a message

• Average size message body sent

• Average size message header sent

• Average size message body received

• Average size message header received

To begin collecting billing data, you must set the delivery_stats, send_billing, recv_billing parameters of the postman process. The postman process manages your system's message routing tables and collects information about message traffic.

The Collector process automatically deletes data older than the number of days specified in the gc_billinfo_kept parameter of the collector process. The default value is 45 days.

See Also: "Parameters for Billing" for more information about setting the parameters available for billing

Activating Billing Information for Messages Sent

To begin collecting information about the messages sent to your eMail Server system, enable the send_billing parameter of the postman process. The default vaule of this parameter is 0 (diabled).

This task can only be performed through the OOMGR command-line interface.

In OOMGR

1. Start OOMGR.

2. Enter the following command at the OOMGR prompt:

   IOFCMGR> register paramvalue server=postman
   2>instance=<instance_value> parameter=send_billing value=1;
   

   See Also: "Parameters for Billing" for more information about other parameters available for billing

Activating Billing Information for Messages Received

To begin collecting information about the messages received by your eMail Server system, enable the recv_billing parameter of the postman process. The default vaule of this parameter is 0 (diabled).

This task can only be performed through the OOMGR command-line interface.

In OOMGR

1. Start OOMGR.

2. Enter the following command at the OOMGR prompt:

   IOFCMGR> register paramvalue server=postman
   2>instance=<instance_value> parameter=recv_billing value=1;
   

   See Also: "Parameters for Billing" for more information about other parameters available for billing

Viewing Billing Information for Messages Sent and Received

After you activate billing, the eMail Server system begins to collect billing data. You can retrieve the data using SQL scripts which query the views om_billing_sent_messages and om_billing_received_messages.

The following table shows the fields in the om_billing_sent_messages view:

Field Name	Type
TIMESTAMP	date
SENDER_NAME	varchar2 (limit of 255 characters)
MSG_ID	number
MSG_SIZE	number
MSG_TYPE	number
PRIORITY	number
LOCAL_RECIPS	number
REMOTE_RECIPS	number
GATEWAY_RECIPS	number

The following table shows the fields in the om_billing_received_messages view:

Field Name	Type
TIMESTAMP	date
SENDER_NAME	varchar2 (limit of 255 characters)
MSG_ID	number
PRIORITY	number
MSG_TYPE	number
RECIPIENT_NAME	not null; varchar2 (limit of 30 characters)
RECEIPIENT_LOCAL_ID	number
REMOTE_RECIPS	number
GATEWAY_RECIPS	number

Parameters for Billing

Parameter	Description and Values
billinfo_kept	Specifies the number of days message billing data is kept. Valid Values: 0 to 365 (365 days equals one year) Default value: 45
delivery_stats	Specifies whether delivery statistics information is collected. This information is used to collect billing information. Setting this parameter may slow down postman process performance because of the additional work being done by the process. Valid values: 0 (disables delivery statistics collection) 1 (enables delivery statistics collection) Default value: 0
recv_billing	Specifies whether billing information should be collected whenever a message is received. Setting this parameter may slow down postman process performance because of the additional work being done by the process. Valid values: 0 (disables billing information collection on received messages) 1 (enables billing information collection on received messages) Default value: 0
send_billing	Specifies whether billing information should be collected whenever a message is sent. Setting this parameter may slow down postman process performance because of the additional work being done by the process. Valid values: 0 (disables billing information collection on sent messages) 1 (enables billing information collection on sent messages) Default value: 0

System Wide Virus Removal Script

Oracle eMail Server comes with a PL/SQL package to help administrators identify and remove specific messages system wide based on its sender, subject or attachment name. A script is also provided to use this package interactively through SQLPLUS. The most useful application of this script is to remove virus infected messages in the system. This package provides option to specify the time range within which the virus infected message has been entering the system, as well as an option to copy all these messages to a folder instead of deleting them immediately.

To use the SQLPLUS script, locate the file virusscr.sql under
$ORACLE_ HOME/office/admin/rsql, edit the file using a text editor and follow the instructions inside the comments (comments are text preceded by '--') to customize the script for your environment. It is recommended a backup copy of the original file is made for future references. After editing the script, run SQLPLUS as user oo.

Example: SQLPLUS session script when running the script virusscr.sqlSQL> @virusscrMessage attribute(subject/attachment/sender)[subject]: attachmentPattern: ILoveYouEnd date(DD-MON-YYYY)[today]: 01-JAN-2000Number of days until end date[1]: 30Action(delete/move)[delete]: moveFolder name[/Infected]: /Wastebasketold 18: v_range := to_number('&&dayrange');new 18: v_range := to_number('30');old 20: IF '&&endday' = 'sysdate' THENnew 20: IF '01-JAN-2000' = 'sysdate' THEN...PL/SQL procedure successfully completed.

By default the script output to a log file /tmp/virus.log, which can be customized by editing the virusscr.sql file manually.

Example: log file output10/20 17:04:13 INF: Processing message in range 16085 - 1608510/20 17:04:13 INF: Deleted 10 messages10/20 17:04:13 DIA: Finished processing messages beyond 11085

If "move" is the chosen operation, all messages deleted by this script will also be copied into the target folder.

Bounce Back Messages Script (over recipient quota)

Server side rules is a feature of eMail Server that allows users, administrators or application developers to set up rules and filters for incoming email messages. Administrators can greatly benefit from this feature by setting up a variety of system wide email pocilies as rules, thereby greatly customize the server bevahior to suit the need of your particular requirements.

eMail Server comes with an example of such script, which can be used to set up a system wide rules to bounce back any incoming message if the recipient is over quota, thereby enforcing a hard quota policy. To use this script, locate the file bouncemsg.sql under $ORACLE_HOME/office/admin/rsql, run it using SQLPLUS as user oo.

Example: Script OutputSQL>@bouncemsgSelect rule management action item:(new/delete/disable/enable)[new]: newSelect action for over-quota messages(bounce/delete/both)[both]: bothold 7: IF '&&sel' = 'new' THENnew 7: IF 'new' = 'new' THEN...PL/SQL procedure successfully completed.

See Also: the Oracle eMail Server Developer's Guide for more information on how to programmatically use server side rules.