RedMine Customization: Service Catalog, SLA's, KPI, Description Templates and Workflow Coordinator

1. Introduction

If you use or you know or you intend to use RedMine project to control Issues of your projects like a Service Desk Tool, you could be interested in this customization. The Issue functionality from Redmine was originally built to serve as a management tool for development bugs. I had a need for a tool to manage Service Desk, Incident, Problems and Change Management, something in line with ITIL. Before deciding the use of another tool, we decided to analyze the software architecture. So, we concluded that it could be easily customized to meet our needs. Now, I am sharing with you.

2.  RedMine Customization Functional Requirement

2.1. Functional Requirement - Service Catalog

a) RedMine Issues category are now based on Services Catalogs. A Service Catalog of a project determine aspects like: 

• Description: Offers a brief description of the service in customer (non-technical) terms
• Standard Features: Describes features and functions of the service available to anyABC employee who receives the service . these are providedunder the ABC Base Service Level Agreement
• Optional Features: Describes features and functions of the service only available to ABC employees and hierarchies upon special request . these are only provided under ABC Extended Service Level Agreements
• Delivery Scope: Identifies which ABC hierarchies and business units are eligible to receive the service
• Delivery Channels: Identifies which delivery channels the service may be receivedwith . examples might be:  a) Laptops; b) Workstations; c) Intranet; d) Mobile phone, etc.
• Service Hours: Identifies timeframes and operating hours for which ABC employees can use the service
• User Requirements: Indicates pre-requisites that a ABC employee should have inorder to successfully receive the service
• Service Initiation: Identifies where ABC employees can go to obtain the service
• Service Support: Identifies where ABC employees can go to receive help in theevent that problems occur with the service
• Standard Costs: Indicates any ABC employee, division or business unit costsemployees with the Standard Features and Optional Features provided with the service
• Service Targets: Describes expectations for delivery of the service in customer(non-technical) terms
• Issue Description Template: User end best practice template of Issue Description
• FAQ: Frequently Asked Questions
• TechInfo: Techinical Information

Example: Issue Categry entity now export foreign key to Service Catalog entity

b) All Service Catalog information must be published and available for USER TEAM and SUPPORT TEAM everytime.

Example: Project Wiki Home Page introduces user to a page where Service Catalog is detailed.

c) Constraints of Required Information can be specified for field Description of a RedMine Issue.

Example: Issue Category of "Request for new user creation on system X" is based on a item of Service Catalog that has at least two required informations: a) user-login; b) system X role;

d) The user's life must be continually kept simple during issue creation

Example: RedMine Issue automatically retrieves Issue Description Template when user creates an Issue.

2.2. Functional Requirement - Measurements for SLA's, KPI's and SLO's

a) RedMine should record all state transitions and support measurements for key performance indicators. 

Example: We should know how long does a service "User account lock" take?

b) Elapsed Time Measurements should be calculated in working hours and no-working hours
Example: Some services are provided for business hours, so calculation should desconsider non-working hours

c) RedMine should SLA's and SLO's should be monitored

d) RedMine should classify data in the following dimensions: Time (Opened, Closed, Executed), Service Catalog, Author, Executor, Approver, SLA acomplishment, etc.

2.3. Functional Request - Workflow Coordinator

A Workflow Coordinator should, periodically and during status transitions, assess issue as a whole and perform automatic routing actions to improve efficiency to process:

• Issue created with no category, should be assigned to author user in a status "Waiting for Information"
• Issue in status "Execution Validation" for more than 7 days, should be updated to new status "Completed without validation" and author user be notified by e-mail;
• Issue in status "Execution Validation" for less than 7 days, should remember author user by e-mail to change status to completed or rejected;
• Issue in status "Waiting for Information" for more than 1 day, should remember assigned user by e-mail to complement information;
• Issue in status "Waiting for Approval" for more than 1 day, should remember approver user by e-mail to approve it;
• Issue in status "Completed" for more than 1 day, should remember user by e-mail to answer survey;

3. RedMine Development Specifications

3.1. RedMine Customization Deployment Diagram

3.2. RedMine Customization Issue Statuses Configuration

3.3. RedMine Customization Database Model Deployment

5. RedMine Customizagion Deployment Guide

5.1. Download package with "RedMine Customization - Service Catalog, SLA's, KPI, Description Template and WorkFlow Coordinator from my Google Drive

• Download package scripts from my Google Drive on: 

https://docs.google.com/folder/d/0Bw01CdQwHqwIeXZOQkJQdUlVNDA/edit

5.2. Deploying RedMine Customization database schema and objects

• Pre-Conditions: An existing instance of RedMine is already installed on "redmine_prod" database ( look for a post how to install RedMine )
• Execute Step-by-Step de following Scripts

# mysql -u root -pjosemarsilva redmine_prod < deploy_01_dml_redmine_custom.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_02_ddl_redmine_custom_fields.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_03_ddl_redmine_custom_ddl-tables_issue_service_catalog_sla_kpi.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_04_dml_redmine_custom_dml-inserts_issue_service_catalog_sla_kpi.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_05_ddl_redmine_custom_ddl-trigger_tr_b_i_er_issues.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_06_ddl_redmine_custom_ddl-trigger_tr_b_u_er_issues.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_07_ddl_redmine_custom_ddl-procedures_issue_service_catalog_sla_kpi.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_08_ddl_redmine_custom_ddl_view_service_catalog_kpi.sql
# mysql -u root -pjosemarsilva redmine_prod < deploy_09_ddl_redmine_custom-functions_issue_service_catalog_sla.sql
#

5.3. Deploying RedMine Worflow Coordinator

• Copy RedMine Workflow Coordinator scripts to appropriated path

root@SRV0004:~# ls  /root/python/redmine*

/root/python/redmine_wfc_issue_alerts.log

/root/python/redmine_wfc_issue_alerts.py

/root/python/redmine_wfc_issue_alerts.sh

• Add RedMine Workflow Coordinator to Crontab

# crontab -e
     :

#+------------------ 1: min(0-59)
#| +--------------- 2: hour(0-23)
#| | +------------ 3: day_of_month(1-31)
#| | | +--------- 4: month_of_year(1-12)
#| | | | +------ 5: day_of_week(0-6)(Sun-Sat)
#| | | | | +--- 6: command-line
#| | | | | |
#v v v v v v
30 23 * * 1-5 /root/python/redmine_wfc_issue_alerts.sh > /root/python/redmine_wfc_issue_alerts.log 2>&1

    :

5.4. Configure a Project to use RedMine Customization Service Catalog, SLA's, Description Template and Workflow Coordinator 

• Use the Excel Spreadsheet "RedMine_Service_Catalog_Configuration.xls" to configure off-line and generate .SQL scripts of your project

• Configure Project List for customization updating key issue_transitions_project_list from table settings

mysql> select id, name, description from projects where name = 'TI';

+----+------+-----------------------+

| id | name | description           |

+----+------+-----------------------+

|  1 | TI   | IT Support & HelpDesk |

+----+------+-----------------------+

1 row in set (0.00 sec)

mysql> select * from settings where name = 'issue_transition_projects_list';

+-----+--------------------------------+------------+---------------------+

| id  | name                           | value      | updated_on          |

+-----+--------------------------------+------------+---------------------+

| 108 | issue_transition_projects_list | 1,32,33,34 | 2012-11-28 20:06:15 |

+-----+--------------------------------+------------+---------------------+

1 row in set (0.00 sec)

 6. Glossary

• Service Catalog: List all detailed information from all services provided;
• Workflow Coordinator: Software component responsible to assess issue, analyze and perform routing actions;