HowToManageNotifications

version 1.5.0 or higher

How To Manage Notifications (Alerts)

The Plandora tool is able to send generic notifications/alerts. Through the 'root' main screen, it is possible to create notifications based on a SQL statement. The notification/alert form can be access using the 'root' user. Into the main form, select the option "Agent Form".

Each notification/alert agent record must contain:

• "Name": A short name used only for identification purposes;

• "Description": A content that describes what the agent must do;

• "Enable?": if true, the agent is ready to be used by Plandora, otherwise, Plandora will ignore this agent;

• "Retry Number": define how many times the agent must be executed, if the current try has failed.

• "Periodicity": define what kind of periodicity the agent must run. (daily, montly, weekly, yearly or eventual)

• "Hour": for periodicity different of "Eventual", this field defines the hour of the day (0 to 23) that the agent must wake-up.

• "Minute": define the minute of the hour (0 - 59) that the agent must wake-up. If the periodicity is "Eventual", this field defines how long the agent must wait to run again (minimun value is 10 minutes)

• "SQL": This field must contain the business rule (in SQL statement format) of agent. When a agent thread wake-up, the SQL is performed by Plandora. If the statement returns no value, the agent turn to sleep again, otherwise, Plandora starts the specific agent procedure.

In Plandora, a agent was designed to be an abstract entity. Then, a Java class implements the specific business rules based on Plandora interface. This kind of specialization is called "Agent Type":

The latest Plandora version contains the "agent types" below:

1. e-mail notification agent

This agent sends an email to one or many SMTP accounts in case of SQL statement returns some value.

• "Sender" : The mail account that is sending the notification;

• "Destination" : must contain the e-mail account or a list of recipients (separated by comma) that will be notified;

• "Subject": the text of e-mail subject;

• "Body" : the content of e-mail (html content is allowed, if the option "Html Format?" is True;

• "SMTP Host" : the SMTP URI. For example: smtp.myserver.com;

• "SMTP Port" : the SMTP Port;

• "SMTP User" : the user of SMTP account, that will be used by Plandora to send the e-mail;

• "SMTP User": the passoword of SMTP user;

• "Send one e-mail only?": If false, the Plandora will send one e-mail for each line returned by SQL. It is usefull in case of, notifications that must spreaded to many destinations with different information.

• "Html Format?": if true, the body of e-mail will be formated to html. The content of SQL query will be concatenated into e-mail body using a html table format.

The fields Sender, Destination, Subject and Body can contain the key word #COL_N#, where 'N' is a number of SQL column position starting in 1 (ex.: #COL_1#, #COL_2#, etc). This key word can be useful to change the fields of email dinamically according the SQL query results.

1. 'DBLog notification agent'

This agent save into Plandora data base (table event_log) some information that can be used for audit purposes. This table contain the coluns below:

   summary       varchar(10)
   description   text
   creation_date timestamp
   username      varchar(30)

The fields creation_date and username will be automatically set to current date/time. the content returned by SQL will be set at collumn 'description'.

• "Summary" : The content inserted here will be set at collumn summary of event_log table

• "User Creator of Log" : define the content of column username of event_log table

• 'HTTP notification agent'

This channel send a HTTP post (GET method) based on the content of a URL field. This channel is particulary useful to create triggers or integration procedures among other applications using the http.

The URL body can contain the key word #COL_N#, where 'N' is a number of SQL column position starting in 1 (ex.: #COL_1#, #COL_2#, etc). This key word can be useful to change the URL dinamically according the SQL query results.

1. 'File notification agent'

This channel performs parsing actions over a file that can be read in a specific location or from an URL destination. It is usefull, for example, to read a file in somewhere and to check if it contain an appropriate content.

• "Type" : Use this field to define the source of file. The allowed values for this field must be "HTTP" or "FILE"

• "Path" : This field define the path of file. For example, if the type of file is "HTTP" this field must contain something like <nowiki>http://myserver/myfile.txt</nowiki>. If the type is "FILE" this field must contain a value that seems like "\server\folder\myfile.txt"

• "Rule" : Use this field to define which rule must be applied into the file content. If the rule applied by tool return a TRUE, a new log record will be insert into Plandora "event_log" table.

• "Value" : This field is used to accomplishment the "Rule" field. The content of field "VALUE" will be used for differents purposes depending the type of rule. Optionally, it is possible to use the key word "#DATE|dd/MM/yyyy#" to make Plandora replace dinamically the current date using the date mask defined.

The acceptable rules for the field "Rule" are described below:

• "FIND_CONTENT" : Use this rule to find a specific content into the file;

• "NOT_FIND_CONTENT" : Use this rule to check if a specific content is NOT into the file;

• "SIZE_GREATER_THAN" : Use this rule to check if the size of file is greather than a value (in bytes);

• "SIZE_SMALLER_THAN" : Use this rule to check if the size of file is smaller than a value (in bytes);

• "SIZE_EQUAL" : Use this rule to check if the size of file is the same of a specific value (in bytes);

Other agents...

It is also possible to implement a specific agent Java class that extends the "Notification" abstract class and could contain customized business rules. All you have to do is:

1. to import the Plandora interface (plandoraInterface.jar)

2. to create a Java POJO class that extends a class called Notification.

3. to implement some methods

4. to create a Jar file with your customizations and copy the file to WEB-INF\lib\ of your Plandora deploy (Tomcat)

5. to set the name of your customized class into System Administrator Options (root).

6. It's done! Your customized agent will appear with a new Type combo box option at Agent Form

Examples of SQL statements

1. The query below returns the description of request that has been just created by customer for any projects, within the notification/alert time window. The query returns also the e-mail of leader(s) related to the requests.

   select distinct t.email, p.description 
   from notification n, requeriment r, planning p, leader e, tool_user t, requeriment_status rs
   where r.id = p.id
      and p.creation_date >= n.last_check 
      and p.creation_date <= n.next_notification
      and r.requeriment_status_id = rs.id
      and rs.state_machine_order = 1
      and r.project_id = e.project_id 
      and e.id = t.id
      and t.email is not null
      and n.id = ####

Obs.: the caracters #### must be replaced by agent ID record.

1. The query below returns the e-mail of assigned resources and the name of tasks that has been created within the the notification/alert time window.

   select distinct u.email, t.name
   from resource_task rt, task t, planning p, 
        resource r, tool_user u, task_status ts, notification n
   where rt.task_id = t.id
      and t.id = p.id 
      and rt.task_status_id = ts.id
      and ts.state_machine_order = 1
      and rt.project_id = r.project_id 
      and rt.resource_id = u.id
      and u.email is not null
      and u.username <> 'root'
      and p.creation_date >= n.last_check 
      and p.creation_date <= n.next_notification
      and n.id = ####

Obs.: the caracters #### must be replaced by agent ID record.

1. The query below returns the e-mail of project leader(s) and the tasks that has been finalized in any projects, within the notification/alert time window.

   select distinct u.email, t.name
   from resource_task rt, task t, planning p, 
        leader e, tool_user u, notification n
   where rt.task_id = t.id
      and t.id = p.id 
      and rt.project_id = e.project_id 
      and u.email is not null
      and p.final_date is not null
      and p.final_date >= n.last_check 
      and p.final_date <= n.next_notification
      and n.id = ####

Obs.: the caracters #### must be replaced by agent ID record.