Package configuration

From cqwiki
Jump to navigationJump to search

Configuration guide

  • The package is implemented in Perl and can be used with Perl-based as well as VBScript-based schemas.
  • The package does not require Perl modules deployment on clients; all code elements are stored in ClearQuest schema.
  • The packages use ClearQuest Security Context feature and require metaschema version 22

(ClearQuest repository that created with CQ 2001a and higher or upgraded to 2001A from previous releases).

  • The package works with ClearQuest clients 2001A, 2002, 2003, and v7 (as of 12/07/2008, 7.1 has not been tested yet). The following platforms are supported: Windows, old CQWeb (ASP-based version), CQWeb (Java Web), ClearQuest UNIX.


Please refer to installation instructions page. You will need to install the latest version of EmailNotification package only. UDBProperties will be installed automatically for you.
EmailSubscription is an optional package delivering end-user self-subscription functionality.

Note: Staring 1.4 release, the package can be installed on ClearQuest 7.0 Designer machine, but still can be used with older clients, if necessary.


Email Notification properties

Email notification package properties are ClearQuest records of udb_property record type. Configure property usually means submitting record of that record type.

The following properties can be configured for Email Notifications package:


It is your SMTP mail relay. If you need to specify non-default port, it can be done in host:port combination.


email_login (optional)

login name that is used for password authentication with SMTP relay. If you SMTP relay does not require password authentication, you do not need to use this property, and it shoul not exist.

Note: If you would like, it can be a 'Secure' property.

email_password (optional)

password that is used for password authentication. If you SMTP relay does not require password authentication, you do not need this property, and it should not exist.

Note: It is a good idea to use 'secure' property feature when storing it in a ClearQuest database.

email_address (optional)

Static "From:" email address for notification. Email address of current ClearQuest user is used if property does not exist.

Note: This feature might be useful to bypass mail relaying restrictions: some SMTP relays may restrict mail relaying based on FROM email address. In case of password authentication, it might be also critical: FROM email address could be restricted to email address of the account used for authentication. Ask your SMTP relay admin for additional information.

email_reply_to (optional)

Static Reply-To address, if different from email_address can be specified. For example, you can configure mail replies to be sent to some distribution list.


It is the second mandatory property (besides email_host). It has to be set to '1' to enable email notification for user database. By setting it to 0 (or by deleting property), you can disable email notification on entire ClearQuest database

EN_DeliveryMode (optional)

The following delivery modes are available. Please check Email Notification delivery mode page for details.

Light deliver notifications "on the fly" and log only error messages for re-delivery,
Deferred (default) save all messages before attempting delivery, and deliver them in the same notification hook
Queue save all messages in the message queue, and do not attempt to deliver them. Delivery will be done by a separate script running by scheduler on the server. You can find simple delivery script on Download page.

EN_DeliveryTimeout (optional)

Delivery timeout interval in seconds. If message in the message queue was not delivered it will be re-delivered (by other user, for example) after that.

Default value: 300 (5 minutes)

EN_DeliveryAttempt (optional)

Maximum number of delivery attempts performed for a message in the queue.

Default value: 5

EN_DeliveryScript (script only)

Should never be set in a ClearQuest database. To be used in Delivery script only.


Email administrators group. It is used in en_email_message action access control hooks If it is not set, only user with superuser can modify or delete email messages in the queue.

Debug (optional)

Debug level 1-9. Setting this property forces script to trace execution. Output could be captured by using dbwin32.exe utility

Default value: NONE

Notification_Default_Security_Group (optional)

Default visibility value. Existing udb_visibility record name that is used as a security context default value in Email_Notification_Rule submit action.

EN_Disable_Variable_Name_Validation (optional)

By default syntax of all parameterized fields and variable names are validated. You can turn off variable names check by setting this property to non-zero value.

Notification Rules

To create notification rule, Submit new Email_Notification_Rule record. Here are some important fields of this record type

Name any unique name
Record_Type desirable record type for which notification package is applied
Order Notification Rules evaluation order, SHORT_STRING(10). The rules are sorted in ascending order.
Actions notification can be restricted for set of actions. Notification is Triggered for any action if empty
Field Change notification can be triggered by filed change.
Triggered regardless any of fields change if empty.
Fields changed in Action or Field initialization hooks are not counted.
Condition Notification condition. Notification is triggered if notification condition evaluation result is TRUE. See section expression field syntax for reference. Assumed "TRUE" when the field is empty.
Priority Message priority patameterized field, expression can be used. See expression field syntax section for reference.

if evaluation result

  • < 0, message priority is Low.
  • == 0, message priority is Normal
  • > 0, message priority is High

Condition result is assumed 0 when the field is empty ('Normal' priority).

From "From:" addresses (parameterized). see parameter fields section for syntax reference.
  1. when Notification Rule From field is not empty, it is expanded and used as from address on generated email
  2. when From field is empty, and email_from database property exists, email_from content is evaluated to be used as from address
  3. otherwise, email address of the user executing action triggering email is used as from email address

One email address is expected as evaluation result.

To "TO:" addresses (parameterized). see parameter fields section for syntax reference.

One email address per line is expected as evaluation result.

CC "CC:" addresses (parameterized). One email address per line is expected as evaluation result.
BCC "BCC:" addresses (parameterized).
One email address per line is expected as evaluation result.
Header Add-in Additional lines to be included in message header (envelope) - parameterized field. You can use it to specify content type, charset, etc. Please check notification examples page (HTML emails, attachments)
Subject Subject - parameterized field. Can be multiline, converted to single line and included into the message header after evaluation.
Body message body (parameterized).
Active Notification rule can be deactivated by clearing "active" flag.


  • Notification is triggered only if Action AND Field restrictions are satisfied AND Condition is true AND any of message recipients ("To:", "CC:", or "BCC") fields are not empty after evaluation.
  • You can create site specific properties in multisited environment as <SiteName>PropertyName. If you would like to disable a global property on specific site, you could do it by setting <SiteName>PropertyName value to "EN_NONE".

Parameterized Text and Condition fields Syntax

Parameterized Text fields

Text field can contain plain text, variables that are expanded during rule evaluation, SQL statements, user-defined, pre-defined and Perl build-in functions.

Any character in the field can be escaped with \;


Variable Syntax

${var_name} or

Variables are evaluated (expanded) in the following order:

  • Check if property with the same name defined in CQ database and return value
  • Check predefined variable names and return value.
  • Check record field with the same name.
    (you can use dot, ., in the name for reference field data type - $name1.name2.name3)

Predefined variable names

$Action current action name
$EntityDefName current entity definition.
$SiteName Site name in multisited environment
$UserLoginName Login name of user triggering email notification rule
$EmailNotificationRuleName Email Rule name that is currently evaluated


state field value
owner's email (REFERENCE field data type)
value of database property "Property_Name" (udb_property record)


  • Property names and Build-in Environment variables are case sensitive, while Field names are case insensitive!
  • Date_Time variables: format that is used for Date_Time variable type inside ClearQuest hooks is yyyy-mm-dd hh:mm:ss.

SQL statements

SQL statement that will be passed to the backend database when variable is expanded.

SQL(<statement>) SQL execution function

  • Statement could be Double-Quoted string with variables, functions and SQL statements, single-quoted non-expandable string, or result of function that returns scalar value. *Statement string (SQL function parameter) is not limited to single line.
  • Statement is executed in run-time using BuildSQLQuery API call and inherited all restrictions related to the API call. Please check ClearQuest API documentation.


  • Get owner's email (the same result as $


   FROM  users 
   WHERE T1.login_name = '$owner'


  • Notify 'CHCD_Approvers' ClearQuest group members


   FROM  parent_child_links T1, groups T3, users T2
   WHERE T1.parent_dbid = T2.dbid
     AND T1.child_dbid = T3.dbid 
     AND = 'CHCD_Approvers'
     AND T2.is_active = 1


  • Here is a little bit more complicated scenario: assume that we use AssignmentGroups stateless record type at our site. It has the following fields:
Unique group name
REFERENCE to users
group managers
REFERENCE to users group members
Active INT
obsolete groups marked as ‘0’, valid groups as ‘1’

"Request" record type has AssignedToGroup field (reference to "AssignmentGroups").
Request assigned to a Group first, then the Group Manager will assign it to somebody in the team.
If we would like to notify the Group Managers about new request that has been assigned to the Team,
we could set Email Notification Rule's "To:" field to the following value:

   FROM  parent_child_links T1, fielddef T2, users T3, assignmentgroups T4
   WHERE T1.parent_dbid = T4.dbid
     AND T1.child_dbid  = T3.dbid
     AND T1.parent_fielddef_id =
     AND = 'Managers'
     AND = '${AssignedToGroup}'



function_name(parameter [, paramater1 …])
Function name is a name of build-in, pre-defined or user-defined function. Spaces between function name and ( symbol are not allowed.

Parameter list is a comma-separated list of:

  • variables,
  • functions or SQL queries,
  • single-quoted strings,
  • double quoted strings with variables, functions, etc.
  • expressions
  • numeric constants

Scalar value expected as function's return value.

Function name lookup order

  • check a database property with the same name.
    • If found, property value assumed to be a valid Perl-syntax code, that will be executed as function.
    • Function parameters are passed as "@_" array to the function.
    • $entity global variable available in function.
      Return value becomes function result.
  • check predefined functions
  • function name is assumed to be a Perl function.


  • Limit Subject line to 100 characters (Perl buld-in function "substr" is used):
  substr( "some subject line with $parameters", 0, 100)

  • Condition function:
  IF($attempt > 0, 1, -1 )

  • Check whether "Description" field was updated during action and trigger a notification (an alternative way)
Notification Rule Condition filed:
  IsFieldUpdatedThisAction('Description') != 0

Property "IsFieldUpdatedThisAction" contains the following code:
  my ($name) = @_;
  my $result = 0 ;
  eval {
    my $fields = $entity->GetFieldsUpdatedThisAction(); 
    $result = 1 if defined( $fields->ItemByName($name) );
  $@ = '';
  return $result;

  • Use original value of "Description" field in the mail body
Email Notification Rule "Msg_Body" field:
 Original Description: 
 was changed to: 
Property "RT_GetFieldOriginalValue" contains the following code:
  my ($name) = @_;
  my $value = '';

  eval {
    $value = $entity->GetFieldOriginalValue($name)->GetValue();
  $@ = '';
  return $value;

Note: variables of CQPerlExt module in user defined functions scope can cause 'false' execution errors, because CQPerlExt objects usually do not clear $@ variable on destruction. It would be safer to destroy private objects references before leaving the function and clean $@ variable to avoid the problem, for example:

  my $session = $entity->GetSession();
  # ... <some code> 

  $session = undef;
  $@       = '';
  return $return_value;

Double-quoted string

Variables, functions, SQL statements are expanded within double-quoted strings.
Escape symbols are: "\n", "\r","\t", "\f", "\b", "\a"
"\" symbol by the end of new lines will escape new line symbol, i.e.
"\" in front of any other symbol escapes that symbol, i.e. \$, \", etc

  is equal to 
 "aaa bbb"


"some string here with $variable and function f1($par1, $SQL{'Statement'}, 'string') and others"

Single-quoted strings

Single quoted strings are not expanded. Escape symbols are \\ and \'.


Conditions are implemented in perl-like style (please check Perl manual for detailed syntax documentation).
They can contain variable, single- and double-quoted strings, function and other elements described in previous section.
You can group expressions with brackets ( ).

Operators (in priority order)

Operator Comments
!, ~, +, - Unary operators
=~, !~ regular expression at the right side can include modifiers (/m/i/…/)
*, /, %, x
mult, div, ...
+, -, .
plus, minus, string concatenation
gt, ge, lt, le, >, >=, <, <= String and numeric comparison
eq, ne, cmp, != , ==, <=>
String and numeric comparison
Logical AND
Logical OR

Note: it has to be a space between '/' operator and operand.


($field =~ /something/i) && ($another_field > function($param1, $param2) || $another_value)

Build-in (Predefined) Functions

IF( <expression>, <val1>, <val2) )


<expression> logical expression
parameter returned if expression is true
parameter returned if expression is false

<val1> or <val2> depending on expression result.


IF( $ ne '' , $, '' )



<fieldname> ClearQuest field name

ClearQuest field original value.


IF( GetFieldOriginalValue('owner') ne $owner, GetFieldOriginalValue(''), $ )

GetGroupEmailAddresses(<group> [, <group2> [, ... ]])


<group> ClearQuest groups list

ClearQuest field original value.


GetGroupEmailAddresses( 'Admins', 'Engineering' )

GetDateTime( [ <offset> ] )


<offset> offset from current date/time in the following format:

General offset format: [+-]?[\d+][smhd]?

none or ‘now’ offset = 0
+ 1 second
+ 5 seconds
- 2 minutes
+ 10 hours
+ 3 days

Current date/time plus offset as date_time string in 'yyyy-mm-dd hh:mm:ss' format


GetDateTime( -300 )
five minutes before

DateTimePlusOffset( <date_time> [, <offset> ] )


<date_time> string in yyyy-mm-dd [hh:mm:ss] format
<offset> offset from <date_time> in [+-]?[\d+][smhd]? format

Given date plus offset as a date_time string in 'yyyy-mm-dd hh:mm:ss' format


DateTimePlusOffset('2000-10-01 00:01:30', '+30m')

Returns: 2000-10-02 00:31:30

DateTimePlusOffset( $Eneter_Date, '3d' )

Returns: value of 'Enter_Date' field + 3 days.

GetDateTimeFromTimestamp( <timestamp>)


<timestamp> long number of seconds since 01/01/1970 (result of time() function)

Converts timestamp to local date/time.

string in yyyy-mm-dd hh:mm:ss format



Returns current date

DateTimeToDate( <date_time> )


<date_time> string in ‘yyyy-mm-dd [hh:mm:ss]’ format

Converts date/time string to date only string.

Date String in yyyy-mm-dd format.

GetDate( [ <offset> ] )


<offset> offset from current date/time in the following format:

General offset format: [+-]?[\d+][smhd]?

none or now offset = 0
+ 1 second
+ 5 seconds
- 2 minutes
+ 10 hours
+ 3 days

Current date/time plus <offset> as a date strings in yyyy-mm-dd format


GetDate('+1d' )

DateTimeDiff( <date_time1>, <date_time2> [ , <modifier>])


<date_time1> string in ‘yyyy-mm-dd [hh:mm:ss]’ format
<date_time2> string in ‘yyyy-mm-dd [hh:mm:ss]’ format
<modifier> none/’s’/’m’/’h’/’d’

Modifier changes returned result from difference in seconds, when it is s or absent, to minutes (m), hours (h) or days (d).

result in
'h' hours
'd' days

(<date_time1> - <date_time2>) in seconds or other metrics, according to modifier:


DateTimeDiff( $action_date, GetDateTime(), 'd')

Returns a difference between $action_date field and current date/time in days.

Access control

udb_property and Email_Notification_Rule records are protected using security context (udb_visibility). By default, security context field is not set, and superuser only can submit, update, or delete configuration records. You can delegate maintenance tasks by creating desirable security context records and setting PropertyDefaultSecurityGroup and NotificationDefaultSecurityGroup database properties to populate security context field automatically when records are created (records existing before setting these properties would need to be updated manually). Default security group properties also affect action permissions.

Record Type Action Permissions
udb_visibility Submit
  • Superuser
  • Superuser
  • Security Administrator
  • Security context group member
  • Superuser
udb_property Submit
  • Superuser
  • Security Administrator
  • Default Security context group member
(set by PropertyDefaultSecurityGroup database property)
  • Superuser
  • Security Administrator
  • Security context group member
  • Superuser
Email_Notification_Rule Submit
  • Superuser
  • Security Administrator
  • Default Security context group member
(set by NotificationDefaultSecurityGroup database property)
  • Superuser
  • Security Administrator
  • Security context group member
  • Superuser
en_email_message Submit
  • Superuser
  • A member of group specified by EN_AdminGroup database property
  • Superuser
  • A member of group specified by EN_AdminGroup database property
  • Superuser
  • A member of group specified by EN_AdminGroup database property
Email_Subscription Submit
  • Superuser
  • Security Administrator
  • Default Security context group member
(set by NotificationDefaultSecurityGroup database property)
  • Superuser
  • Security Administrator
  • Security context group member
  • Superuser
  • All users
  • All users

Submit action can be enabled for all users by setting Notification_Bypass_Access_Control database property.


Additional examples can be found on Examples page.