COSEC INTEGRATE

User Manual

SE CURI TY SOLU TIO NS

 SECURITY SOLUTIONS

COSEC INTEGRATE
User Manual

COSEC Database Third Party Database

Documentation Disclaimer
Matrix Comsec reserves the right to make changes in the design or components of the product as engineering and
manufacturing may warrant. Specifications are subject to change without notice.

This is a general documentation for all variants of the product. The product may not support all the features and
facilities described in the documentation.

Information in this documentation may change from time to time. Matrix Comsec reserves the right to revise
information in this publication for any reason without prior notice. Matrix Comsec makes no warranties with respect
to this documentation and disclaims any implied warranties. While every precaution has been taken in the
preparation of this system manual, Matrix Comsec assumes no responsibility for errors or omissions. Neither is any
liability assumed for damages resulting from the use of the information contained herein.

Neither Matrix Comsec nor its affiliates shall be liable to the buyer of this product or third parties for damages,
losses, costs or expenses incurred by the buyer or third parties as a result of: accident, misuse or abuse of this
product or unauthorized modifications, repairs or alterations to this product or failure to strictly comply with Matrix
Comsec operating and maintenance instructions.

Warranty
For product registration and warranty related details visit us at:
https://www.matrixcomsec.com/warranty/#access-control-time-attendance

Copyright
All rights reserved. No part of this system manual may be copied or reproduced in any form or by any means
without the prior written consent of Matrix Comsec.

Version 20
Release date: January 7, 2025
 Contents

Introduction ....................................................................................................................................................... 1
Export to MS SQL/ORACLE ............................................................................................................................. 4
Export to Postgre Server ................................................................................................................................. 21
Export to CSV ................................................................................................................................................. 24
Export to MySQL ............................................................................................................................................. 38
Export to Text .................................................................................................................................................. 45
Export to DB2 .................................................................................................................................................. 63
Export to People Works .................................................................................................................................. 68
Export FP Template to File ............................................................................................................................. 75
Custom Export- FP Template .......................................................................................................................... 84
Export to Progress OpenEdge ........................................................................................................................ 88
Import from MS SQL/Oracle/Postgre .............................................................................................................. 96
Import from Active Directory .......................................................................................................................... 111
Importing Data from a Customized SAP ....................................................................................................... 115
Import Events ................................................................................................................................................ 117
Import from My SQL ...................................................................................................................................... 125
Import from Progress OpenEdge .................................................................................................................. 128
Import from Excel .......................................................................................................................................... 134
Integrate Alert ................................................................................................................................................ 138
BACnet Server Configuration ........................................................................................................................ 140

Table of Contents i
Introduction

The COSEC INTEGRATE module enables the administrator to configure the following functionalities.

• Export data from the COSEC database to a third party MS SQL or Oracle database.

• Import user data from external data sources like MSSQL, Oracle, Postgre and the Microsoft Active
Directory.

• Import User data from customized SAP table in MSSQL or Oracle.

• Export Device data and event logs to predefined tables in Postgre database.

• Import event data from 3rd party database (MS SQL and Oracle).

• Configure BACnet Server parameters to enable integration of BACnet with COSEC.

The COSEC installer utility has the COSEC INTEGRATE module option which needs to be selected for this
application to be installed.

To access this application, double click on the COSEC INTEGRATE Module icon on the computer desktop.

Enter the Master Service Address and click Next.

Enter the password of the admin user and click Login.

The Reset Password option enables the user to reset the password in the event of the admin user forgetting the set
password. Contact the authorized dealer or Matrix support for the reset code.

For COSEC Integrate to work properly, it must be ensured that actual DB fields as well as their renamed
versions (if any, i.e. if DB field is renamed with an alias during export template configuration) are both
included in the template configuration. This shall be applicable to all exports.

Matrix COSEC INTEGRATE User Guide 1

Click on the Login button after entering the password. The COSEC INTEGRATE home page appears as shown.

The COSEC INTEGRATE service needs to be stopped prior to starting the configuration of the various COSEC
INTEGRATE parameters. Click on the Stop Service button as shown.

To know about the Export and Import from other database and BACnet Server Configuration click on the following
links.
“Export to MS SQL/ORACLE”
“Export to MySQL”
“Export to CSV”
“Export to Text”
“Export to Postgre Server”
“Export to DB2”
“Export to People Works”
“Export FP Template to File”
“Custom Export- FP Template”
“Export to Progress OpenEdge”

“Import from MS SQL/Oracle/Postgre”

“Import from My SQL”
“Importing Data from a Customized SAP”
“Import from Active Directory”
“Import Events”

2 Matrix COSEC INTEGRATE User Guide

“Import from Progress OpenEdge”
“BACnet Server Configuration”

Matrix COSEC INTEGRATE User Guide 3

Export to MS SQL/ORACLE

The application allows the administrator to map the data fields of the COSEC application database to the data fields
of another SQL/Oracle database.

Integrate supports:
• Export to Oracle from version Oracle 10g to Oracle 23AI.
• Export to MS SQL from version SQL Server 2005 to SQL Server 2022

SQL Server 2005 and SQL Server 2008 R2:

• for installation you need Windows 7 or below versions.
• to establish remote connections in these versions from the PC in which Integrate is installed, make sure
you have enabled TLS 1.0 and 1.1 (that is, Best Practices settings).

Server Configuration
Click on the Server Configuration button to configure the Web server and Destination database for Export and
Source database for Import.

Depending on the Integration mode selected, you must configure Destination Database/ Destination Location/
Source Database.

Select the Export to MS SQL Server option in the Integration Mode field.

Click on the Edit button.

In the COSEC Web Server section:

• Specify the web URL of the API service of the COSEC WEB application as shown above.
• Enter the User Name and Password of the system administrator (sa) as set in the COSEC WEB
application.

4 Matrix COSEC INTEGRATE User Guide

In the Destination Database Server section:

• The Database Type will be SQL SERVER.

• Server: Enter the database server name in the following format - Database server name\Instance Name
e.g. dbserver\sqlexpress.

• Database Name: Specify the database name of the destination database as per the site settings. Eg:
COSECDB_V13R2 is the destination database which is newly created from Database Utility. You can
export the data to this database.

For newly created database, ensure that you have set the password in COSEC Web application. Then only
Test connection from COSEC Integrate with Web server will be successful.

• User Name: Specify the database administrator ID in this field. This is the user name which you have set
while installing SQL Server Management Studio in your computer.

• Password: Enter the password of the Database administrator as per the site settings. This is the password
which you have set while installing SQL Server Management Studio in your computer.

The Test Connection button is provided to test the connections with the web server as well as the SQL Server
database.

Click on Save once done.

In the event of selecting the Export to Oracle Server option in the Integration Mode field, specify the Oracle
server destination address as well as the user name and the password (case-sensitive for Oracle Server) in the
respective fields as shown.

Test the connection and Save the configuration.

Matrix COSEC INTEGRATE User Guide 5

Export Data Configuration
This option enables the Admin user to map the fields from the COSEC database tables to fields in a third party
database. Click on the Export Data button. The following page appears.

The COSEC INTEGRATE application provides four data templates in line with the default Database Views as
shown. The COSEC System provides the following four Database views which would provide the relevant field
options to be mapped with the fields of a destination database.

• Monthly Attendance Summary, for details refer to “Monthly Attendance Summary”

• Daily Attendance Detail, for details refer to “Daily Attendance Detail”
• Attendance Events, for details refer to “Attendance Events or Access Control Events -Schedule”
• Access Control Events, for details refer to “Attendance Events or Access Control Events -Schedule”

The following templates can be configured from COSEC Web which can be exported to other database.
• First and Last Attendance Events
• Daily Attendance Template1
• Daily Cafeteria Events
• Monthly Cafeteria Summary
• Monthly Job Summary
• User Details

For details, refer “Custom Template - User Details Template-Schedule”

6 Matrix COSEC INTEGRATE User Guide

Each of the above database views would provide the relevant fields whose values can be exported from the
COSEC database. Select the required data template and click on the Edit button.

Export
The Destination Table can be selected by clicking the Select Table button. Table Selection window appears as
shown below. The desired table can be selected by scrolling or searching.

Table Field Mapping

Now the Admin user can start the mapping of the fields from the source database to that of the destination
database as shown.

Matrix COSEC INTEGRATE User Guide 7

 • Select the Source field from the COSEC database.
• Select the Destination field from the drop down options to map with the source field.
• Click on the Add button. The mapped fields will be visible in the bottom grid.

In the case of Attendance Events and Access Control Events the user needs to map the UserID and the
EventDateTime_D source fields to fields in the destination table.

Map the UserID, PMonth and Pyear source fields to appropriate fields in the destination table in the case of
the Monthly Attendance Summary.

Map the UserID and ProcessDate_D source fields to appropriate fields in the destination table in the case
of the Daily Attendance Detail.

The mapping between the following data types is allowed. Only a warning message is shown in case of mismatch
in data types.

Source data type Destination data type

Text (char, varchar,varchar2...) Number (numeric, int, bigint, smallint, float, number, double,int32...)

Text Datetime formats(depending on the date format configured for DB server. If

format matches, the record will be accepted)

Number Text

Date Time Text

The mapping from Number and Date time to Date time and Number respectively is restricted.

8 Matrix COSEC INTEGRATE User Guide

Schedule
The Schedule section enables the Admin user to schedule the data export process. The schedule option vary
based on the selected Source Data Template.

Daily Attendance Detail

The Daily Attendance detail will have the following options as shown.

• Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the events/users whose data is to
be exported. Check this check box and click on the Filter button.

The Event Selection and User Selection Filters pop-up appears.

Event Selection is applicable when Template selected is API_Template_ATDEvents or

API_Template_ACSEvents or Template_ATDEvents or Template_ACSEvents.

Matrix COSEC INTEGRATE User Guide 9

 • User Selection: Select the desired option — All, Select Groups or Select Users.
• If you select All, data of all the users will be exported.
• If you select Selected Groups, the list of Groups appear in the grid.Select the check boxes of the
desired Groups whose data is to be exported.
• If you select Selected Users, the list of Users appear in the grid.Select the check boxes of the
desired Users whose data is to be exported.

• Click Apply to save the configurations done or click Cancel to discard. The Filter pop-up closes.

• In Schedule, select the desired option for data transfer— Daily or Monthly.

10 Matrix COSEC INTEGRATE User Guide

 • On selection of Monthly option, user can set data transfer process to run only once in a month. By default,
Monthly option would be selected.

• If you select Monthly, configure the Attendance Period, that is, the starting and the ending day of the
attendance period for which the data is to be exported. Select the from Day of the Current/Previous Month
to the Day of the Current/Previous Month

Matrix COSEC INTEGRATE User Guide 11

 • When selecting Daily option for Schedule, the options appears as shown.

• If you select Daily, configure the Daily Attendance of. Configure to transfer data for either Previous Day
attendance data or Current Day attendance data with respect to schedule run day.

• In Every __ Day of the Month, specify the day of the month on which the export process is to be run.

• Specify the Run time in HH:MM format when the export process is to be run.

• Set the Retry Count to retry again for export in case of export failure.

• Set the Retry Interval in hours from the drop down list. This parameter specifies the time period between
successive retries.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the check boxes as per your requirement:

Select Success check box to send an alert mentioning the details of successfully transferred records to
the configured COSEC Server.

Select Failure check box to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer, that is, if both the above check boxes are selected then the connection
status will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

12 Matrix COSEC INTEGRATE User Guide

 Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Monthly Attendance Summary

The Monthly Attendance Summary will have the following unique options as shown.

• The configuration of the parameters is similar to Daily Attendance Details, except the below mentioned
parameter.

• Select the Attendance Period for which the monthly Attendance summary data is to be exported.The
Admin user can select either the Previous Month or Current Month option.

For details of other parameters, refer to “Daily Attendance Detail”.

Matrix COSEC INTEGRATE User Guide 13

Attendance Events or Access Control Events -Schedule

The following schedule options will be available.

• Select the Active check box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the events/users whose data is to
be exported. Check this check box and click on the Filter button.

The Event Selection and User Selection Filters pop-up appears.

Event Selection is applicable when Template selected is API_Template_ATDEvents or

API_Template_ACSEvents or Template_ATDEvents or Template_ACSEvents.

14 Matrix COSEC INTEGRATE User Guide

 • Event Selection: Select the desired option — All, Allowed Events or Denied Events.
• If you select Both, all Allowed and Denied events will be exported.
• If you select Allowed Events, only Allowed events will be exported.
• If you select Denied Events, only Denied events will be exported.

• User Selection: Select the desired option — All, Select Groups or Select Users.
• If you select All, data of all the users will be exported.
• If you select Selected Groups, the list of Groups appear in the grid.Select the check boxes of the
desired Groups whose data is to be exported.
• If you select Selected Users, the list of Users appear in the grid.Select the check boxes of the
desired Users whose data is to be exported.

• Click Apply to save the configurations done or click Cancel to discard. The Filter pop-up closes.

• Include Previously Failed Records: By enabling this check box, the records which are failed to export
previously will be exported in the next retry.

The IN-OUT Attendance events and Access Control events of user which are failed to export during
database connection error will be exported when database connection is restored.

Only failed records due to Destination Table connectivity issue should be considered for this functionality.

Matrix COSEC INTEGRATE User Guide 15

 • Retry for Failed Records: Select the number of times for which the failed records will be tried again for
export.

• Select the desired option for the frequency at which the application will update the destination database —
Interval Based, Once a Day

Interval Based
If you select this option configure the Update Interval and Start Date.

• Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will update the destination database.

Once a Day
If you select this option configure the Run Time, Retry Count and Retry Interval.

• Select the Once a Day option to schedule the export once every day at a scheduled Run Time (HH:MM).

• Specify the Start Date from which the export process is to be initiated. The records from the start date to
the present date will be exported.

• Specify the Retry count as the number of times for which system will try again to export the failed records
with an interval gap of hours set in Retry Interval.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the check boxes as per your requirement:

Select Success check box to send an alert mentioning the details of successfully transferred records to
the configured COSEC Server.

Select Failure check box to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer, that is, if both the above check boxes are selected then the connection
status will be considered as Failure and Reason for Failure will be displayed in the Alert.

Custom Template - User Details Template-Schedule

This is not a default template.Custom Templates can be added from the COSEC Web. These are then visible
in the Integrate. To add Custom Templates refer to the User Guide, Admin Module > System Utilities > Export
Data.

16 Matrix COSEC INTEGRATE User Guide

 • Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the events/users whose data is to
be exported. Check this check box and click on the Filter button.

The Event Selection and User Selection Filters pop-up appears.

Event Selection is applicable when Template selected is API_Template_ATDEvents or

API_Template_ACSEvents or Template_ATDEvents or Template_ACSEvents.

Matrix COSEC INTEGRATE User Guide 17

 • User Selection: Select the desired option — All, Select Groups or Select Users.
• If you select All, data of all the users will be exported.
• If you select Selected Groups, the list of Groups appear in the grid.Select the check boxes of the
desired Groups whose data is to be exported.
• If you select Selected Users, the list of Users appear in the grid.Select the check boxes of the
desired Users whose data is to be exported.

• Click Apply to save the configurations done or click Cancel to discard. The Filter pop-up closes.

• Include Previously Failed: By enabling this check box, the records which are failed to export previously
will be exported in the next retry.

• Retry for Failed Records: Select the number of times for which the failed records will be tried again for
export.

• Export Modified Only: Enabling this check box will export only the changed or updated user details. Eg:
at 10:00 hrs, details of 100 users is exported. At 14:00 hrs (interval based export) the change in 20 users is
found. So details of only 20 users will be exported.

• Export Update Time: When the export of only modified records is done, then the time at which modified
records were exported will get updated for the respective records in destination fields. Eg: For the modified
20 users, the timing will be displayed as 14:00 hours in the destination table..

18 Matrix COSEC INTEGRATE User Guide

 • You can select the field from the drop down list where the export timing of modified records will be
updated. Eg: Suppose you select Edatetime from the options, then the date time of export process
will be displayed in Edatetime column of destination server.

• Set Deleted User Flag: Suppose the user is deleted from the COSEC system. But that user is already
exported in the destination table. So by enabling this you can set the flag to 1 for the respective deleted
users when next export will be done.

• Select the field where the flag is to be updated.

• Select the desired option for the frequency at which the application will update the destination database —
Interval Based, Once a Day

Interval Based
If you select this option configure the Update Interval and Start Date.

• Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will update the destination database.

Once a Day
If you select this option configure the Run Time, Retry Count and Retry Interval.

• Select the Once a Day option to schedule the export once every day at a scheduled Run Time (HH:MM).

• Specify the Retry count as the number of times for which system will try again to export the failed records
with an interval gap of hours set in Retry Interval.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the check boxes as per your requirement:

Select Success check box to send an alert mentioning the details of successfully transferred records to
the configured COSEC Server.

Select Failure check box to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer, that is, if both the above check boxes are selected then the connection
status will be considered as Failure and Reason for Failure will be displayed in the Alert.

Manual Transfer
The Manual Transfer option provides the Admin user the flexibility to export data of a specific time period as and
when required. In order to access this functionality click on the Stop Service button to stop the COSEC
INTEGRATE service.

Click the Export Data button.

On the Export Data page, edit and save the export settings as per your requirement.

Matrix COSEC INTEGRATE User Guide 19

Click the Manual Transfer button. The following window appears for Manual Transfer to MS SQL Server and for
Oracle Manual Transfer to Oracle.

Enter the Start Date and End Date.

Select the Transfer Data mode— All or Updated.

All: All the past data falling within the selected date range will be transferred.

Updated: All the updated data falling within the selected date range will be transferred.

Click the Process button. The data of the specified time period will be exported to the destination table.

20 Matrix COSEC INTEGRATE User Guide

 Export to Postgre Server

The application allows the administrator to export device data and event logs from the COSEC application
database to predefined tables of the Postgre SQL database. The Postgre SQL database needs to have the
following tables in the database:

• devices

• device logs

The devices table holds the basic data of the COSEC devices like deviceid, serial number (MAC address),
ipaddress. This information can be obtained by viewing the ControllerList view of the COSEC database. The MAC
addresses however can be directly obtained from the devices. This table holds information on the last ping time and
the last log download date for each of the devices.

The devicelogs table receives the event logs from the COSEC database received from each of the COSEC
devices.

Server Configuration

In order to configure this functionality click on the Server Configuration button. Select the Export to Postgre SQL
(CGG) option in the Integration Mode field.

To ensure smooth functioning, make sure appropriate rights for PostgreSQL have been provided explicitly.

The Postgre Configuration Settings are case-sensitive, hence configure the same cautiously. We
recommend you to use lower-case.

In Destination Field, the Data Type is displayed as provided by PostgreSQL. If no data is received then 0 /
-1 is displayed, that is, maximum / minimum length.

Refer to the table below for the PostgreSQL Versions supported in the COSEC and Integrate:
Cosec Version Supported PostgreSQL Authentications Supported in
Version PostgreSQL

COSEC V20R10 and earlier PostgreSQL 8 to 12 Plain Text and MD5

COSEC V20R11 and later PostgreSQL 12 to 17 Plain Text, MD5 and SCRAM-SHA-256

On upgrade
If you have COSEC V20R10 or earlier and PostgreSQL 12, and if you upgrade COSEC to V20R11
or later, then you will have to re-configure all the PostgreSQL Setting parameters in Integrate.

Matrix COSEC INTEGRATE User Guide 21

Click on Edit. The following page appears.

In the COSEC Web Server section:

• Specify the web url of the api service of the COSEC WEB application as shown.

• Enter the User Name and Password of the sa user as set in the COSEC WEB application.

In the Postgre SQL section:

• Server: Enter the Postgre database server IP address or its network name.

• Port: Specify the TCP connection port as configured in the Postgre database server.

• Database Name: Specify the destination database name of the Postgre database.

• User Name: Specify the database owner ID in this field.

• Password: Enter the password of the Database owner as per the site settings.

The Test Connection button is provided to test the connections with the web server as well as the Postgre SQL
Server database.

Click on Save once done.

The administrator can now start the COSEC INTEGRATE service by clicking on the Start Service button as
explained earlier.

The Admin can now perform Export Data Configuration which is same as MSSQL/Oracle. For more information,
refer “Export Data Configuration” of MSQL/Oracle Server.

Manual Transfer

The Manual Transfer option provides the Admin user the flexibility to export data of a specific time period as and
when required. In order to access this functionality click on the Stop Service button to stop the COSEC
INTEGRATE service.

22 Matrix COSEC INTEGRATE User Guide

Click the Export Data button.

On the Export Data page, edit and save the export settings as per your requirement.

Click the Manual Transfer button. The following window appears for Manual Transfer to Postgre.

Enter the Start Date and End Date.

Select the Transfer Data mode— All or Updated.

All: All the past data falling within the selected date range will be transferred.
Updated: All the updated data falling within the selected date range will be transferred.

Click the Process button. The data of the specified time period will be exported to the destination table.

Matrix COSEC INTEGRATE User Guide 23

Export to CSV

The application allows the administrator to export data related to various user events to a .csv file which can be
stored at a specific location on FTP/SFTP server or in a local folder on the hard disk.

Click Server Configuration.

In Integration Mode select Export to .csv.

Click Edit to configure the parameters.

Under COSEC Web Server configure the following:

• Web URL: Specify the Web URL of the API service of the COSEC WEB Server application.

• User Name: Enter the User Name as sa as set for the COSEC WEB Server application.

• Password: Enter the Password as set for the COSEC WEB Server application.

• Click Save.

• Click Test Connection to confirm that the connectivity is established with the COSEC WEB Server
application.

Under Destination Location, configure the following:

• Select the desired option — Local Folder, FTP or SFTP.

24 Matrix COSEC INTEGRATE User Guide

Local Folder
• If you select Local Folder, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. The maximum file size supported is 5MB.

• If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

Matrix COSEC INTEGRATE User Guide 25

FTP
• If you select FTP, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

26 Matrix COSEC INTEGRATE User Guide

 Click Authentication collapsible panel and configure the following:

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Password: Enter the Password to be used for authentication while accessing the Path.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

Matrix COSEC INTEGRATE User Guide 27

 • Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. Maximum file size supported is 5 MB.

If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

SFTP
• If you select SFTP, configure the following:

• Server: Specify the SFTP Server IP Address where the exported files are to be stored.

COSEC Integrate communicates with the SFTP Server using the default port number 22. If you
wish to use any other port, then you need to configure the same in the format Server IP
Address:Port. For example if Port 33 is to be used then configure the Server as 192.168.104.20:33.

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

• For additional authentication and security, you can select the desired option — With Key or
Without Key.

If you select With Key, then configure the SSH Host Key FP.

28 Matrix COSEC INTEGRATE User Guide

 The SSH Host Key FP servers as a unique identifier for the Server. This is a cryptographic
representation of the Servers Public Key. It is used to verify the authenticity of the Server during the
connection process. It is used to ensure that the Server the client is connecting to is the same
Server that it connected with previously.

This key is provided by the Administrator of the SFTP Server to the clients.

If you select Without Key, then no key is required.

Click Authentication collapsible panel and configure the following:

Matrix COSEC INTEGRATE User Guide 29

 • Mode: Select the desired mode for authentication — Password-Based, Key-Based.

If you select Password-Based, configure the following:

In Password-Based, when a client signs in, the Server checks the User Name and Password
combination and then approves/denies the request.

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Password: Enter the Password to be used for authentication while accessing the Path.

If you select Key-Based, configure the User Name, Private Key and Passphrase.

In Key-Based, when a client signs in, a pair of keys — Private and Public — are used for
authentication. The Public Key is uploaded in the SFTP Server while the Private Key is provided to the
client.

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Private Key: Click Choose and select the key file which is to be used for authentication.

• Passphrase: Enter the Passphrase. This is used in combination with the Private Key to acquire the
SFTP Server access.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

30 Matrix COSEC INTEGRATE User Guide

 • Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. Maximum file size supported is 5 MB.

If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

Matrix COSEC INTEGRATE User Guide 31

Export Data Configuration
This option enables the Admin user to specify the fields whose values are to be exported to the .csv file.

Each database views would provide the relevant fields whose values can be exported from the COSEC database.

Source Data Template: Select the required data template and click on the Edit button. Now the admin user can
start the selection of the fields from the source database as shown.

Select the Source field from the drop down list and click on the Add button. The field will be added to the grid as
shown above.You can remove a selected field by clicking on Clear button for the respective field.

32 Matrix COSEC INTEGRATE User Guide

The Schedule section enables the admin user to schedule the data export process. The schedule option vary
based on the selected Source Data Template. The Daily Attendance detail will have the following options as
shown.

• Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the users whose data is to be
exported. Check this box and click on the Filter button. The Multiple selection window appears. Select the
users whose data is to be exported.

• Destination FileName: Enter a filename in this field. The max characters can be 200. The file name can
include Alphanumeric characters, Special characters like !@#$%^&()_-+=[]{};',.<space> A-Z a-z 0-9 and
pair of asterisks(**).
You can type '*' in the text-area. This will show the list of variables as per the template type. Select
the desired variable. It will be added by appending '*' at the end. During the deployment of file, the current date-time
values will be fetched and replaced instead of these variables.

1. When '*' is typed in text area, another '*' will be added automatically as it is allowed only in pair.
2. When you are entering filename and the appearing variable list is disturbing then you can enter ESC key
to hide the variables for the instance.

Example of Variables:
*gdateDD*- This variable will fetch and show the date on which the data is exported and file is generated. Similarly
variables are available for month, year, hours. minute and seconds.

*fromDD*- This variable will fetch and show the date value of the Start Date. Similarly variables are available for
month and year. Suppose Start date is 1st date of current month. If current month is Feb so the value of variable will
be “1”.

Matrix COSEC INTEGRATE User Guide 33

*toDD*- This variable will fetch and show the date value of the End Date. Similarly variables are available for month
and year. Suppose End date is Last date of current month. If current month is Feb so the value of variable will be
“28”.

atdMM*- This variable will fetch and show the month value of attendance period. Similarly variables is available for
year.

Example : A new template has been configured with Destination FileName = 'Atd. Events_*fromDD*-*fromMM*-
*fromYY*_to_*toDD*-*toMM*-*toYY*

Now, this template is manually exported with date-range, 01-01-2016 to 02-01-2016. Thus, the exported file name
will be Atd. Events_01-01-16_to_02-01-16.csv

The system creates a folder named DailyAttendance in the path as specified in the server configuration.

• Schedule: Select the option as Daily or Monthly to run the schedule

• For monthly schedule specify the day of the month on which the export process is to be run. And
select the Attendance period i.e. starting and the ending day of the attendance period for which
the data is to be exported.

• For daily schedule select the Daily Attendance of Previous Day or Current Day for which the
attendance details is to be exported.

• Run time: Specify the Run time in HH:MM format when the export process is to be run.

• Retry Count: Set the Retry Count from the drop down list to retry the export if it gets failed.

• Retry Interval: Select the Retry Interval in hours from the drop down list. This parameter specifies the time
period between successive retries.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

34 Matrix COSEC INTEGRATE User Guide

 It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Then click on Save button to save the schedule. Now you can start service for running the export schedule. See
“Exporting Data” on page 36.

The Monthly Template will have the same options as mentioned above except the Attendance Period option.

The ATD Events and ACS Events templates’ schedule is shown below.

Matrix COSEC INTEGRATE User Guide 35

 • Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will export the data to the csv file.

• Specify the Start Date from which the export process is to be initiated.

In the csv format for exporting data, the records in the csv file would be sorted in the same order as they
were added.

Exporting Data

Schedule Export

For starting the export of file, after saving the schedule; click on Start Service button. The exported file will be
exported at the path specified in Server configuration.

The Daily Attendance exported file is shown below.

Manual Export

You can also do the Manual transfer of data by clicking Manual Transfer button.

36 Matrix COSEC INTEGRATE User Guide

The name of the exported text file will be as per the Destination File name and data will be as per the Table field
Mapping. The exported file will be as shown in Schedule export.

Matrix COSEC INTEGRATE User Guide 37

Export to MySQL

Select the Export to MySQL Server option in the Integration Mode field. Click on the Edit button.

Integrate supports Export to MySQL from version MySQL 8.0.27 to MySQL 8.3.0

In the COSEC Web Server section:

• Specify the web url of the api service of the COSEC WEB application as shown.

• Enter the User Name and Password of the sa user as set in the COSEC WEB application.

In the Destination Database Server section:

• The Database Type will be SQL SERVER.

• Server: Enter the database server name in the following format - Database server name\Instance Name
e.g. dbserver\sqlexpress.

• Database Name: Specify the database name of the destination database as per the site settings.

• User Name: Specify the database administrator ID in this field.

• Password: Enter the password of the Database administrator as per the site settings.

The Test Connection button is provided to test the connections with the web server as well as the SQL Server
database.

Click on Save once done.

38 Matrix COSEC INTEGRATE User Guide

Export Data Configuration
This option enables the Admin user to map the fields from the COSEC database tables to fields in a third party
database. Click on the Export Data button. The following page appears.

The COSEC INTEGRATE application provides four data templates in line with the default Database Views as
shown. The COSEC System provides the following four Database views which would provide the relevant field
options to be mapped with the fields of a destination database.

• Monthly Attendance Summary

• Daily Attendance Detail

• Attendance Events

• Access Control Events

Each of the above database views would provide the relevant fields whose values can be exported from the
COSEC database. Select the required data template and click on the Edit button.

Matrix COSEC INTEGRATE User Guide 39

Now the Admin user can start the mapping of the fields from the source database to that of the destination
database as shown.

• Select the Destination Table from the pull down list.

• Select the Source field from the COSEC database.

• Select the Destination field from the selected destination table.

• Click on the Add button. The mapped fields will be visible in the bottom grid as shown.

In the case of Attendance Events and Access Control Events the user needs to map the UserID and the
EventDateTime_D source fields to fields in the destination table.

Map the UserID, PMonth and Pyear source fields to appropriate fields in the destination table in the case of
the Monthly Attendance Summary.

Map the UserID and ProcessDate_D source fields to appropriate fields in the destination table in the case
of the Daily Attendance Detail.

The Schedule section enables the Admin user to schedule the data export process. The schedule option vary
based on the selected Source Data Template. The Daily Attendance detail will have the following options as
shown.

40 Matrix COSEC INTEGRATE User Guide

 • Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the users whose data is to be
exported. Check this box and click on the Filter button. The Multiple selection window appears. Select the
users whose data is to be exported.

• Specify the day of the month on which the export process is to be run.

• Specify the Run time in HH:MM format when the export process is to be run.

• Set the Retry Count from the pull down list.

• Set the Retry Interval in hours from the pull down list. This parameter specifies the time period between
successive retries.

• Specify the Attendance Period by specifying the starting and the ending day of the attendance period for
which the data is to be exported.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

Matrix COSEC INTEGRATE User Guide 41

 In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Select any one option for data transfer i.e, Daily or Monthly in the field Schedule.On selection of Monthly option,
user can set data transfer process to run only once in a month.By default, Monthly option would be selected.When
selecting Daily option from the drop down list, the options appears as shown.

User can configure to transfer data for either previous day’s attendance data or current day’s attendance data with
respect to schedule run day.

The Monthly Attendance Summary will have the following unique options:

• Select the Attendance Period for which the monthly Attendance summary data is to be exported.The
Admin user can select either the previous month or current month option.

42 Matrix COSEC INTEGRATE User Guide

On selecting the Attendance Events or the Access Control Events the following schedule options will be
available.

• Check the Active box to enable the schedule.

• Set the filter parameters as described earlier.

• Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will update the destination database.

• Specify the Start Date from which the export process is to be initiated.

After defining the above parameters, the Admin user has to click on the Start Service button.

The application will start the COSEC INTEGRATE service as shown.

Manual Transfer
The Manual Transfer option provides the Admin user the flexibility to export data of a specific time period as and
when required. In order to access this functionality click on the Stop Service button to stop the COSEC
INTEGRATE service.

Click the Export Data button.

Matrix COSEC INTEGRATE User Guide 43

On the Export Data page, edit and save the export settings as per your requirement.

Click the Manual Transfer button. The following window appears for Manual Transfer to MySQL.

Enter the Start Date and End Date.

Select the Transfer Data mode— All or Updated.

All: All the past data falling within the selected date range will be transferred.
Updated: All the updated data falling within the selected date range will be transferred.

Click the Process button. The data of the specified time period will be exported to the destination table.

44 Matrix COSEC INTEGRATE User Guide

Export to Text

The application allows the administrator to export data related to various user events as mentioned earlier to a text
file which can be stored at a specific location on FTP/SFTP server or in a local folder on the hard disk.

Click Server Configuration.

In Integration Mode select Export to Text File.

Click Edit to configure the parameters.

Under COSEC Web Server configure the following:

• Web URL: Specify the Web URL of the API service of the COSEC WEB Server application.

• User Name: Enter the User Name as sa as set for the COSEC WEB Server application.

• Password: Enter the Password as set for the COSEC WEB Server application.

• Click Save.

• Click Test Connection to confirm that the connectivity is established with the COSEC WEB Server
application.

Under Destination Location, configure the following:

• Select the desired option — Local Folder, FTP or SFTP.

Matrix COSEC INTEGRATE User Guide 45

Local Folder
• If you select Local Folder, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. The maximum file size supported is 5MB.

• If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

46 Matrix COSEC INTEGRATE User Guide

FTP
• If you select FTP, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

Matrix COSEC INTEGRATE User Guide 47

 Click Authentication collapsible panel and configure the following:

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Password: Enter the Password to be used for authentication while accessing the Path.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

48 Matrix COSEC INTEGRATE User Guide

 • Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. Maximum file size supported is 5 MB.

If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

SFTP
• If you select SFTP, configure the following:

• Server: Specify the SFTP Server IP Address where the exported files are to be stored.

COSEC Integrate communicates with the SFTP Server using the default port number 22. If you
wish to use any other port, then you need to configure the same in the format Server IP
Address:Port. For example if Port 33 is to be used then configure the Server as 192.168.104.20:33.

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

• For additional authentication and security, you can select the desired option — With Key or
Without Key.

If you select With Key, then configure the SSH Host Key FP.

Matrix COSEC INTEGRATE User Guide 49

 The SSH Host Key FP servers as a unique identifier for the Server. This is a cryptographic
representation of the Servers Public Key. It is used to verify the authenticity of the Server during the
connection process. It is used to ensure that the Server the client is connecting to is the same
Server that it connected with previously.

This key is provided by the Administrator of the SFTP Server to the clients.

If you select Without Key, then no key is required.

Click Authentication collapsible panel and configure the following:

• Mode: Select the desired mode for authentication — Password-Based, Key-Based.

50 Matrix COSEC INTEGRATE User Guide

 If you select Password-Based, configure the following:

In Password-Based, when a client signs in, the Server checks the User Name and Password
combination and then approves/denies the request.

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Password: Enter the Password to be used for authentication while accessing the Path.

If you select Key-Based, configure the User Name, Private Key and Passphrase.

In Key-Based, when a client signs in, a pair of keys — Private and Public — are used for
authentication. The Public Key is uploaded in the SFTP Server while the Private Key is provided to the
client.

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Private Key: Click Choose and select the key file which is to be used for authentication.

• Passphrase: Enter the Passphrase. This is used in combination with the Private Key to acquire the
SFTP Server access.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. Maximum file size supported is 5 MB.

Matrix COSEC INTEGRATE User Guide 51

 If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

Export Data Configuration

This option enables the admin user to specify the fields whose values are to be exported to the text file.
Each database views would provide the relevant fields whose values can be exported from the COSEC database.
Source Data Template: Select the required data template

Text File Template: The templates will be generated once the Text file configuration is done. For configuring Text
file template, click on Add button by scrolling the Text file Configuration section.

If the Text file template is available then you can select it and click on the Edit button for selecting the fields from
the source database.

52 Matrix COSEC INTEGRATE User Guide

Text File Configuration

Template: Specify the name of the Template to which the source file is to be mapped.

File Header: Click on the check box to enable this option. Click on the drop down arrow to select the options as
mentioned below

• Column Name: Enable the checkbox. You can specify the name of the columns while selecting Data type
and the related fields which is described later. The Column Name will then be exported as the header.
• Custom: For a common header of the page, you can select the custom option and specify a file header
name in adjacent box.

The allowed functions are mentioned below:

AVG: Returns the average. Syntax: AVG(arg1)

CONVERT: Converts particular expression to a specified .NET framework type. Syntax: Convert(expression, type)

COUNT: Counts how many numbers are in the list of arguments. Syntax: COUNT(arg1,)

IIF: Specifies a logical test to perform. Syntax:IIF(expression, [value_if_true],[value_if_false])

ISNULL: Checks an expression and either returns the checked expression or a replacement value.
Syntax: ISNULL(expression,replacement value)
MAX:Returns the maximum value in a list of arguments. Syntax: MAX(arg1)

MIN: Returns the minimum value in a list of arguments. Syntax: MIN(arg1)

LEN: Gets the length of a String. Syntax: CONVERT(expression)

SUBSTRING:Gets a sub-string of a specified length,starting at a specified point in the string.

Syntax:SUBSTRING(expression, start, length)

SUM: Adds its arguments.Syntax: SUM(arg1)

STDEV:Estimates standard deviation based on a sample. Syntax: STDDEV(arg1)

VAR: Estimates variance based on a sample. Syntax: VAR(arg1)

TRIM: Removes all leading and trailing blank charaters like \r, \n, \t. Synatx: TRIM(arg1)

Matrix COSEC INTEGRATE User Guide 53

File Footer: Click on the check box to enable this option. Specify the desired footer in the adjacent box as shown
below.

Export Type: Click on the drop down arrow to select the export type as Fixed Position based or Separator based.

The Export type option can be selected when the template is created for the first time. When the template is
edited then Export type will be disabled.

1. Fixed Position Based: Select this option if you want the columns to be fixed position based.

• Start-End Position: Enter the position value. Eg: 0 to 10 position is set by entering end position as 10

• Length will be updated automatically according to the fixed position value. You can manually enter the
Length value less than the position value.

• Alignment can be selected as Left or Right.

• Padding: Select the Padding value from the drop down options for appending it after the data end point
upto the position end point.

• Data formatting: Select the Data formatting if required.

54 Matrix COSEC INTEGRATE User Guide

 2. Separator Based: Select this option if you want the columns to be Separator based.

• Select the separator options from the drop down list as shown below. You can also select the custom
separator option and specify the separator in the adjacent box.

Data Type: Click on the drop down arrow to select the type of data to be exported.

• Fixed: Select this option and enter the value in the Fixed value field as shown below. For eg: the
organization name remains common to all the employees, so it is considered as fixed value.

• Custom Field: Select this option and enter the value in the Custom value field as shown below.

Matrix COSEC INTEGRATE User Guide 55

 • Filler: If Export type is selected as Fixed position based, then the data type can be selected as Filler. This
is used as the gap between the columns. Filler itself acts as a column, which has value other than data for
eg: space, comma etc. Specify the filler value from the drop down list.

• Database Field: Click the drop down arrow and select the database field options.

When Data Type is selected as Database Field, then you have the option to Replace value. “Example: Replace
Value”
When Data Type is selected as Custom field, the user can create an expression with the fields available in the
selected export view template. The expression should be validated same as in the Text File Configuration section of
Export to Text.

"Field Conditions support replacing value after performing comparison of field value with some fixed value /
database field.

Example: Replace Value

To compare punch event date-time (EDateTime) and insertion date-time (IDateTime).

During door offline condition suppose only first punch(9:00 hrs) is recorded and after that punch was not recorded
for that day. So punch can be inserted after the first punch.

56 Matrix COSEC INTEGRATE User Guide

The system will compare the inserted punch> event punch, then the Noteflag column will be replaced with the value
1. You can give any name to the column for the fields.

Click the Field Replace button to replace the desired field value.

Field Value can be Database Field value or fixed value with which the database field value (selected from the Text
file configuration) will be compared.

Replace Value can be a fixed value or any custom value. (Here If IDATETIME > EDATETIME, then column will be
replace with value 1)

Matrix COSEC INTEGRATE User Guide 57

Schedule Section

The Schedule section enables the Admin user to schedule the data export process. The schedule option vary
based on the selected Source Data Template.

The Daily Template will have the following options as shown. (Attendance Period for monthly schedule and Daily
Attendance for Daily schedule.)

• Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the users whose data is to be
exported. Check this box and click on the Filter button. The Multiple selection window appears. Select the
users whose data is to be exported.

• File Generation: Select Single or Multiple configuration for the file generation based on Enterprise
groups. On Selecting Multiple option, second drop-down of File Generation will be activated. It enables to
generate different files based on enterprise groups as shown below.

58 Matrix COSEC INTEGRATE User Guide

 • Destination FileName: Enter a filename in this field. The max characters can be 200. The file name can
include Alphanumeric characters, Special characters like !@#$%^&()_-+=[]{};',.<space> A-Z a-z 0-9 and
pair of asterisks(**).

You can type '*' in the text-area. This will show the list of variables as per the template type. Select the desired
variable. It will be added by appending '*' at the end. During the deployment of file, the current date-time values will
be fetched and replaced instead of these variables.

1. When '*' is typed in text area, another '*' will be added automatically as it is allowed only in pair.
2. When you are entering filename and the appearing variable list is disturbing then you can enter ESC key
to hide the variables for the instance.

Example of Variables:
*gdateDD*- This variable will fetch and show the date on which the data is exported and file is generated. Similarly
variables are available for month, year, hours. minute and seconds.

*fromDD*- This variable will fetch and show the date value of the Start Date. Similarly variables are available for
month and year. Suppose Start date is 1st date of current month. If current month is Feb so the value of variable will
be “1”.

*toDD*- This variable will fetch and show the date value of the End Date. Similarly variables are available for month
and year. Suppose End date is Last date of current month. If current month is Feb so the value of variable will be
“28”.

atdMM*- This variable will fetch and show the month value of attendance period. Similarly variables is available for
year.

Example : A new template has been configured with Destination FileName = 'Atd. Events_*fromDD*-*fromMM*-
*fromYY*_to_*toDD*-*toMM*-*toYY*

Now, this template is manually exported with date-range, 01-01-2016 to 02-01-2016. Thus, the exported file name
will be Atd. Events_01-01-16_to_02-01-16.txt

• Schedule: Select the option as Daily or Monthly to run the schedule.

• For monthly schedule specify the day of the month on which the export process is to be run. And
select the Attendance period i.e. starting and the ending day of the attendance period for which
the data is to be exported.

• For daily schedule select the Daily Attendance of Previous Day or Current Day for which the
attendance details is to be exported.

• Run time: Specify the Run time in HH:MM format when the export process is to be run.

• Retry Count: Set the Retry Count from the drop down list to retry the export if it gets failed.

• Retry Interval: Select the Retry Interval in hours from the drop down list. This parameter specifies the time
period between successive retries.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Matrix COSEC INTEGRATE User Guide 59

 Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Then click on Save button to save the schedule. Now you can start service for running the export schedule. See
“Exporting Data” on page 61.

The Monthly Template will have the same options as mentioned above except the Attendance Period option.

60 Matrix COSEC INTEGRATE User Guide

The ATD Events and ACS Events templates’ schedule is shown below.

Exporting Data

Schedule Export

For starting the export of file, after saving the schedule; click on Start Service button. The exported file will be
exported at the path specified in Server configuration.

The data in the exported file is as per the Text file configuration shown below.

Matrix COSEC INTEGRATE User Guide 61

Manual Export

You can also do the Manual transfer of data by clicking Manual Transfer button.

The name of the exported text file is as per the Destination File name.

The data in the exported file is as per the Text file configuration shown below.

62 Matrix COSEC INTEGRATE User Guide

Export to DB2

The application allows the administrator to export access events from the COSEC application database to a
destination DB2 database server.

Click on the Server Configuration button to start the configuring process.

The following page appears. Select the Export to DB2 option in the Integration Mode field.

Integrate supports Export to DB2 version DB2 11.5.9 Z/OS

Click on the Edit button.

In the COSEC Web Server section:

• Specify the web url of the API service of the COSEC WEB application as shown.

• Enter the User Name and Password of the system administrator (sa) as set in the COSEC WEB
application.

In the DB2 destination database server section:

• Enter the database server name and port.

• Specify the database name of the destination database as per the site settings.

• Specify the database administrator ID as user name.

• Enter the password of the database administrator as per the site settings.

Matrix COSEC INTEGRATE User Guide 63

The Test Connection button is provided to test the connections with the web server as well as the DB2 database.

Click on Save once done.

Export Data Configuration

Click the Export Data button to map fields between source and destination tables.

For the DB2 Server Database, select a source template from the system-defined data templates or other custom
export templates defined on COSEC that appear for selection.

Each of the above database views would provide the relevant fields whose values can be exported from the
COSEC database. Select the required data template and click on the Edit button.

64 Matrix COSEC INTEGRATE User Guide

Now the admin user can start the mapping of the fields from the source database to that of the destination database
as shown.

• Select the Destination Table from the pull down list.

• Select the Source field from the COSEC database.

• Select the Destination field from the selected destination table.

• Click on the Add button. The mapped fields will be visible in the bottom grid as shown.

In the Schedule panel, user can set up a schedule for data update between source and destination databases or
update data manually using the Manual Transfer option.

Matrix COSEC INTEGRATE User Guide 65

 • Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the users whose data is to be
exported. Check this box and click on the Filter button. The Multiple selection window appears. Select the
users whose data is to be exported.

• To use the exported data for SAP Integration, enable the SAP Integration checkbox.

• To set Interval Based data update, specify the Update Interval.

• To schedule data update Once a Day, specify the Run time in HH:MM format, Start Date (i.e. date since
when export data is to be fetched from COSEC database), Retry Count and Retry Interval.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

66 Matrix COSEC INTEGRATE User Guide

 Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.
• Click Save and start service.

Matrix COSEC INTEGRATE User Guide 67

Export to People Works

This feature allows the user to export data from the COSEC application database to a user-defined FTP location in
the .XLSX format. The export filename will follow the below format:

“PeopleWorks<ddmmyyyy><hhmm>.xlsx”

Click Server Configuration.

In Integration Mode select Export to PeopleWorks.

Click Edit to configure the parameters.

Under COSEC Web Server configure the following:

• Web URL: Specify the Web URL of the API service of the COSEC WEB Server application.

• User Name: Enter the User Name as sa as set for the COSEC WEB Server application.

• Password: Enter the Password as set for the COSEC WEB Server application.

• Click Save.

• Click Test Connection to confirm that the connectivity is established with the COSEC WEB Server
application.

68 Matrix COSEC INTEGRATE User Guide

Under Destination Location, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the export
destination for the exported file.

Click Authentication collapsible panel and configure the following:

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Password: Enter the Password to be used for authentication while accessing the Path.

Matrix COSEC INTEGRATE User Guide 69

 Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the Public
Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be provided
by the Third Party. Maximum file size supported is 5 MB.

If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

70 Matrix COSEC INTEGRATE User Guide

PeopleWorks Export Configuration
Click the Export Data button to map fields between source and destination tables. Select the Database type as
Export to PeopleWorks.

Click on the Edit button.

Select a Data Type to configure fields. There are four data types to choose from:

• Database Field: Select a Database Field from the drop-down list to be added to the export file and define
a new column name against it. Click Add.

Matrix COSEC INTEGRATE User Guide 71

 • Fixed: Select this option to set a fixed value for a field as shown below. For eg: the organization name
remains common to all the employees, so it is considered as fixed value. Click Add.

• Custom Field: Select this option to assign a custom value to a field. Click Add.

• Serial No: Select this option to set serial number as value for a column. Click Add.

In the Schedule section, user can set up a daily or monthly schedule for data export or export data manually using
the Manual Transfer option.

72 Matrix COSEC INTEGRATE User Guide

The description of above fields is given in Export to MSSQL.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Matrix COSEC INTEGRATE User Guide 73

 Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Click Save and start service.

74 Matrix COSEC INTEGRATE User Guide

Export FP Template to File

It is required to export FP Template of selected user to some directory path in physical file.

From COSEC Custom Export

Web DB FP Template

FP Templates Other DB

Click Server Configuration.

In Integration Mode select Export FP Template to File.

Click Edit to configure the parameters.

Under COSEC Web Server configure the following:

• Web URL: Specify the Web URL of the API service of the COSEC WEB Server application.

• User Name: Enter the User Name as sa as set for the COSEC WEB Server application.

• Password: Enter the Password as set for the COSEC WEB Server application.

• Click Save.

• Click Test Connection to confirm that the connectivity is established with the COSEC WEB Server
application.

Matrix COSEC INTEGRATE User Guide 75

Under Destination Location, configure the following:

• Select the desired option — Local Folder, FTP or SFTP.

Local Folder
• If you select Local Folder, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. The maximum file size supported is 5MB.

• If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

76 Matrix COSEC INTEGRATE User Guide

FTP
• If you select FTP, configure the following:

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

Click Authentication collapsible panel and configure the following:

Matrix COSEC INTEGRATE User Guide 77

 • User Name: Enter the User Name to be used for authentication while accessing the Path.

• Password: Enter the Password to be used for authentication while accessing the Path.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. Maximum file size supported is 5 MB.

If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

78 Matrix COSEC INTEGRATE User Guide

SFTP
• If you select SFTP, configure the following:

• Server: Specify the SFTP Server IP Address where the exported files are to be stored.

COSEC Integrate communicates with the SFTP Server using the default port number 22. If you
wish to use any other port, then you need to configure the same in the format Server IP
Address:Port. For example if Port 33 is to be used then configure the Server as 192.168.104.20:33.

• Saved Path: Specify the Path at which the exported files are to be stored.

• Sub-Folders: Select the Sub-Folders check box to enable a folder hierarchy to be created at the
export destination for the exported file.

• Separator: Select the separator which will be used to separate the fields in the exported file.

• For additional authentication and security, you can select the desired option — With Key or
Without Key.

If you select With Key, then configure the SSH Host Key FP.

The SSH Host Key FP servers as a unique identifier for the Server. This is a cryptographic
representation of the Servers Public Key. It is used to verify the authenticity of the Server during the
connection process. It is used to ensure that the Server the client is connecting to is the same
Server that it connected with previously.

This key is provided by the Administrator of the SFTP Server to the clients.

If you select Without Key, then no key is required.

Matrix COSEC INTEGRATE User Guide 79

 Click Authentication collapsible panel and configure the following:

• Mode: Select the desired mode for authentication — Password-Based, Key-Based.

If you select Password-Based, configure the following:

In Password-Based, when a client signs in, the Server checks the User Name and Password
combination and then approves/denies the request.

• User Name: Enter the User Name to be used for authentication while accessing the Path.

80 Matrix COSEC INTEGRATE User Guide

 • Password: Enter the Password to be used for authentication while accessing the Path.

If you select Key-Based, configure the User Name, Private Key and Passphrase.

In Key-Based, when a client signs in, a pair of keys — Private and Public — are used for
authentication. The Public Key is uploaded in the SFTP Server while the Private Key is provided to the
client.

• User Name: Enter the User Name to be used for authentication while accessing the Path.

• Private Key: Click Choose and select the key file which is to be used for authentication.

• Passphrase: Enter the Passphrase. This is used in combination with the Private Key to acquire the
SFTP Server access.

Click File Encryption collapsible panel and configure the following:

The file will be encrypted using PGP (Pretty Good Privacy) encryption method using AES algorithm.

• Enable: Select this check box to enable encryption. If enabled the file will be encrypted using the
Public Key provided by the Third Party and then will be saved on the Path.

• Public Key: Click Choose and select the key file which is to be used for encryption. This will be
provided by the Third Party. Maximum file size supported is 5 MB.

If File Encryption check box is enabled, then Third Party must take the last updated file based on time
stamping from the Saved Path.

• Click Save.

Matrix COSEC INTEGRATE User Guide 81

Export Data Configuration
This option enables the admin user to specify the fields whose values are to be exported to the FP template file.

Enter the User ID of the user whose FP templates are to be backed up. And click on Select button.
The user ID and name will be displayed in the grid.

82 Matrix COSEC INTEGRATE User Guide

Now click on Export button. The export completion will be shown as below:

Here you can find 5 FP templates of selected user which are exported as individual files at the Destination location.

Matrix COSEC INTEGRATE User Guide 83

Custom Export- FP Template

Provision is required in Integrate from where user can select physical file of FP templates and insert them to
configured database's table with various user details like user id, name, user photo and finger print location. This
purpose is served through “Custom Export of FP Template”.

Click on the Server Configuration option and select the Custom Export- FP Template option as the Integration
Mode.

Click on the Edit button.

In the COSEC Web Server section:

• Specify the web url of the API service of the COSEC WEB application as shown.
• Enter the User Name and Password of the sa user as set in the COSEC WEB application.

The Test Connection button is provided to test the connections with the web server

IIn the Template Location section:

• The Database Type can be selected as SQL SERVER or ORACLE server.

• Server: Enter the database server name in the following format - Database server name\Instance Name
e.g. dbserver\sqlexpress.

• Database Name: Specify the database name of the destination database as per the site settings. Eg:
COSEC_HO is the destination database to which you can export the data.

84 Matrix COSEC INTEGRATE User Guide

 For newly created database, ensure that you have set the password in COSEC Web application. Then only
Test connection from COSEC Integrate with Web server will be successful.

• User Name: Specify the database administrator ID in this field. This is the username which you have set
while installing SQL Server Management Studio in your computer.

• Password: Enter the password of the Database administrator as per the site settings. This is the password
which you have set while installing SQL Server Management Studio in your computer.

The Test Connection button is provided to test the connections with the web server as well as the SQL Server
database.

Click on Save once done.

In the event of selecting the Export to Oracle Server option in the Integration Mode field, specify the Oracle
server destination address as well as the username and the password (case-sensitive for Oracle Server) in the
respective fields as shown below:

Test the connection and Save the configuration.

Export Data Configuration

This option enables the admin user to specify the fields whose values are to be exported.

Select the Database “Custom Export-FP Tempate SQL Server”.

Select the Mode as Add Templates to add the FP templates or Delete Templates to delete the FP templates.

Click the Table Mapping button. Then select the table where the user details are to be mapped.

Matrix COSEC INTEGRATE User Guide 85

You have to map all the user fields shown below to the desired field selected from “Field Name” in above table
mapping.

Eg: Name is mapped with locationmac as shown in second row.

After mapping all the user fields, click on Save button.

Now click on Browse button and select the FP template from the path (Local folder/FTP/SFTP) where the
templates are stored.

86 Matrix COSEC INTEGRATE User Guide

The template with User photo, ID, Name and FP location will appear as shown above.

Finally click Add button. System will insert/update records in mapped template table.
Clicking Remove will remove all records from mapped template table with selected User ID.

Matrix COSEC INTEGRATE User Guide 87

Export to Progress OpenEdge

Click on the Server Configuration button to configure the Web server and Destination database for Export.

Select the Export to Progress OpenEdge option in the Integration Mode field. Click on the Edit button.

Integrate supports Export to Progress OpenEdge version OpenEdge 12.8

In the COSEC Web Server section:

• Specify the web url of the api service of the COSEC WEB application as shown.

• Enter the User Name and Password of the sa user as set in the COSEC WEB application.

In the Progress OpenEdge section:

• Server: Enter the server name with which the Progress OpenEdge database is started.

• Driver Name: Select the Progress OpenEdge driver from the picklist. It is the software driver which will
appear in picklist, once the database is installed.

88 Matrix COSEC INTEGRATE User Guide

 • Port: Enter the port number at which the Progress OpenEdge database is running. It is the port
number(eg: 5003) entered in command prompt while starting the database as shown below.

• Database Name: Enter the database name with which Progress OpenEdge database is started. It is case
sensitive. Thus if Database is started with name cosecDB as shown below then enter the name as
“cosecDB”.

• User Name: Specify the username as entered while creating the database.

• Password: Specify the password as entered while creating the database. Eg: Username is cosec and
Password is cosec.

The Test Connection button is provided to test the connections with the web server as well as the Progress
OpenEdge database.

Matrix COSEC INTEGRATE User Guide 89

Click on Save button to save the server configuration.

90 Matrix COSEC INTEGRATE User Guide

Export Data Configuration
This option enables the admin user to map the fields from the COSEC database tables to fields in a third party
database. Click on the Export Data button. The following page appears.

The COSEC INTEGRATE application provides four data templates in line with the default Database Views as
shown. The COSEC System provides the following four Database views which would provide the relevant field
options to be mapped with the fields of a destination database.

• Monthly Attendance Summary

• Daily Attendance Detail
• Attendance Events
• Access Control Events

Each of the above database views would provide the relevant fields whose values can be exported from the
COSEC database. Select the required data template and click on the Edit button at the bottom of the page.

Now the admin user can start the mapping of the fields from the source database to that of the destination
database as shown.

• Select the Destination Table by clicking Select Table button. The table selection picklist appears as
below.

Matrix COSEC INTEGRATE User Guide 91

 • Select the Source field from the COSEC database.

• Select the Destination field from the selected destination table.

• Click on the Add button. The mapped fields will be visible in the bottom grid as shown.

In the case of Attendance Events and Access Control Events the user needs to map the UserID and the
EventDateTime_D source fields to fields in the destination table.

Map the UserID, PMonth and Pyear source fields to appropriate fields in the destination table in the case of
the Monthly Attendance Summary.

Map the UserID and ProcessDate_D source fields to appropriate fields in the destination table in the case
of the Daily Attendance Detail.

The Schedule section enables the admin user to schedule the data export process. The schedule option varies
based on the selected Source Data Template. The Daily Attendance detail will have the following options as
shown.

92 Matrix COSEC INTEGRATE User Guide

 • Check the Active box to enable the schedule.

• The Enable Filter option is provided to enable the administrator to filter the users whose data is to be
exported. Check this box and click on the Filter button. The Multiple selection window appears. Select the
users whose data is to be exported.

• Specify the day of the month on which the export process is to be run.

• Specify the Run time in HH:MM format when the export process is to be run.

• Set the Retry Count from the drop down list.

• Set the Retry Interval in hours from the drop down list. This parameter specifies the time period between
successive retries.

• Specify the Attendance Period by specifying the starting and the ending day of the attendance period for
which the data is to be exported.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Matrix COSEC INTEGRATE User Guide 93

 Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Select any one option for data transfer i.e, Daily or Monthly in the field Schedule.On selection of Monthly option,
user can set data transfer process to run only once in a month.

User can configure to transfer data for either previous day’s attendance data or current day’s attendance data with
respect to schedule run day.

The Monthly Attendance Summary will have the following unique options:

• Select the Attendance Period for which the monthly Attendance summary data is to be exported.The
Admin user can select either the previous month or current month option.

On selecting the Attendance Events or the Access Control Events the following schedule options will be
available.

94 Matrix COSEC INTEGRATE User Guide

 • Check the Active box to enable the schedule.

• Set the filter parameters as required.

• For Interval based schedule; Specify the Update Interval in seconds,minutes or hours to define the
frequency at which the application will update the destination database.
Specify the Start Date from which the export process is to be initiated.

• For Once a Day schedule, Specify the Run Time at which export will be intiated. You can mention Retry
count and Retry Interval for which the retry for export will be done in case of failure.

After defining the above parameters, the admin user has to click on the Start Service button.

Matrix COSEC INTEGRATE User Guide 95

Import from MS SQL/Oracle/Postgre

The application allows the user to import user data from an external SQL, Oracle or Postgre database.

Click on the Server Configuration button to start the configuring process. The following page appears.

Select the Import from MS SQL Server option in the Integration Mode field. Click on the Edit button.

Integrate supports Import from MS SQL from version SQL Server 2005 to SQL Server 2022.

SQL Server 2005 and SQL Server 2008 R2:

• for installation you need Windows 7 or below versions.
• to establish remote connections in these versions from the PC in which Integrate is installed, make sure
you have enabled TLS 1.0 and 1.1 (that is, Best Practices settings).

In the COSEC Web Server section:

• Specify the web URL of the API service of the COSEC WEB application as shown.

• Enter the User Name and Password of the SA user as set in the COSEC WEB application.

96 Matrix COSEC INTEGRATE User Guide

In the Source Database Server section:

• Select the Database Type as SQL SERVER.

• Server: Enter the database server name in the following format:

• Database server name\Instance Name e.g. dbserver\sqlexpress.

• Database Name: Specify the database name of the source SQL database as per the site settings.

• User Name: Specify the database administrator ID in this field.

• Password: Enter the password of the Database administrator as per the site settings.

The Test Connection button is provided to test the connections with the web server as well as the MS SQL or
Oracle Server database.

Click on Save once done.

Matrix COSEC INTEGRATE User Guide 97

In the event of selecting the Import from Oracle Server option in the Integration Mode field, specify the source
Oracle server address as well as the user name and the password (case-sensitive for Oracle server) in the
respective fields as shown.

Integrate supports Import from Oracle from version Oracle 10g to Oracle 23AI.

In the event of selecting the Import from Postgre SQL option in the Integration Mode field, specify the source
Postgre SQL server address, Port number as well as the user name and the password (case-sensitive for Postgre
SQL server) in the respective fields as shown.

To ensure smooth functioning, make sure appropriate rights for PostgreSQL have been provided explicitly.

The Postgre Configuration Settings are case-sensitive, hence configure the same cautiously. We
recommend you to use lower-case.

In Destination Field, the Data Type is displayed as provided by PostgreSQL. If no data is received then 0 /
-1 is displayed, that is, maximum / minimum length.

Refer to the table below for the PostgreSQL Versions supported in the COSEC and Integrate:
Cosec Version Supported PostgreSQL Version Authentications Supported in
PostgreSQL

COSEC V20R10 and PostgreSQL 8 to 12 Plain Text and MD5

earlier

COSEC V20R11 and later PostgreSQL 12 to 17 Plain Text, MD5 and SCRAM-SHA-256

On upgrade
If you have COSEC V20R10 or earlier and PostgreSQL 12, and if you upgrade COSEC to V20R11 or
later, then you will have to re-configure all the PostgreSQL Setting parameters in Integrate.

98 Matrix COSEC INTEGRATE User Guide

Import Data Configuration
After completing the server configuration, click on Import Data button. The page appears as shown below.

Matrix COSEC INTEGRATE User Guide 99

Source: If import from SQL server is to be done then the source will be MS SQL Server. If import from Oracle is to
done then the source will be Oracle.

Data: You can select the option as User Details to import the details of user, User-Wise Shift Assignment to import
the shifts assigned to user on specific dates, Leave Transactions to import leave transactions and Leave Balance
to import the available leave balance.

Now click on the Edit button. The Admin user can start mapping of the fields from the external source database
table to that of the destination COSEC database table as shown.

100 Matrix COSEC INTEGRATE User Guide

 • Select the Source Table whose fields are to be mapped.

• Select the Source field from the COSEC database.Source field is dependent on the Source selected from
the Source Table.

E.g: If Mx_LeaveBal is selected as source from the Source table then the Source field will show all the
fields related to leave balance as shown below.

• Select the Destination field from the drop down options to map with the source field. Destination field will
always be fixed. It is dependent neither on Source table nor on Source field.

Matrix COSEC INTEGRATE User Guide 101

 • Click on the Add button. The mapped fields will be visible in the bottom grid as shown.

102 Matrix COSEC INTEGRATE User Guide

 The mapping between the following data types is allowed. Only a warning message is shown in case of
mismatch in data types.

Source data type Destination data type

Text(char, Number (numeric, int, bigint, smallint, float, number, double,int32... )

varchar,varchar2...)

Text Datetime formats(depending on the date format configured for DB

server. If format matches, the record will be accepted)

Number Text

Date Time Text

The mapping from Number and Date time to Date time and Number respectively is restricted.

Source field’s data type must always match with the Destination field’s data type. In case of mismatch, a
warning is generated as shown below and mapping will not be done.

In case of mismatch of lengths of data types of source field and destination field, a warning is generated
but if you click on Yes, mapping will be done.

Matrix COSEC INTEGRATE User Guide 103

 In the case of selecting data as User details, the user needs to map the UserID, Name and
BLNUPDATEFIELD/LASTUPDATEDTIME destination fields to fields in the source table.

In the case of selecting Data as User-Wise Shift Assignment, the user needs to map UserID, Shift-ID,
StartDate and EndDate destination fields to fields in the source table.

In the case of selecting Data as Leave Transaction, the user needs to map UserID, StartDate, Leave-
Code and LASTUPDATEDTIME destination fields to fields in the source table.

In the case of selecting Data as Leave Balance, the user needs to map UserID, Entry-Type, Days,
Accrual-Policy, Leave-Code, Date, C-Offhrs and LASTUPDATEDTIME source fields to fields in the
destination table.

The Schedule section enables the Admin user to schedule the data import process. After saving the data you can
manually transfer the data.

Check the Active box to enable the schedule.

Select the Schedule for data transfer as Daily or Monthly.

• On selection of Monthly option, user can select the day for data transfer process to run once in a month.
By default, Monthly option would be selected.

• On selection of Daily option, you can run the schedule “once in a day” or “Interval based”. Enter the value
in minutes or hours for interval based schedule.

104 Matrix COSEC INTEGRATE User Guide

 • Specify the Run time in HH:MM format when the import process is to be run.

• Enter the Start date from which import process is to be started.

• Update Records: You can select the option as Duration- Based or Differential based on which records is
to be updated.

Duration-Based
• Duration Prior Current Date (Days): Enter the no. of days to be considered for import before current
date. Suppose Current date or the process date is 20th, and duration prior is set as 5 so the import will
run from 15th.

• Duration Post Current Date (Days): Enter the no. of days to be considered for import after current
date. Suppose Current date or the process date is 20th, and duration post is set as 8 so the import will
run upto 28th.

Differential

If Update Records is switched from Duration-Based to Differential, a pop-up (with close icon and OK button) will be
displayed with the message: All Records will be imported for First Time. Thereafter, only updated ones will be
imported.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Matrix COSEC INTEGRATE User Guide 105

 Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Import Leave Transactions

When leave data is in Human Resource software and T&A calculations are done by COSEC; then it is must to
import leave data from Human Resource software to COSEC to perform calculations of T&A management.

The leave transactions can be imported from MS SQL database, Oracle database or Excel file via Integrate.
Select the Data as Leave Transactions.

106 Matrix COSEC INTEGRATE User Guide

Then map the source and destination fields

The fields USERID,START-DATE, LEAVE-CODE and LASTUPDATEDTIMESTAMP are mandatory for mapping.
If TID is mapped; then ACTION is required to be mapped for importing based on TID.

After mapping, click on Save button.

Matrix COSEC INTEGRATE User Guide 107

The import can be scheduled or manual transfer can be done.

Client database should have a field containing primary key.

108 Matrix COSEC INTEGRATE User Guide

Import Leave Balance
Import Leave Balance allows user to perform Leave Encashment as the ‘Credit/Debit’, ‘C-OFF Encashment’ and
‘Overflow Management’ of the leave records. It can be done through the Import as shown below.

The Import can be done through the sources; MS SQL/Oracle, Excel, Progress Open Edge, Postgre,
Customized SAP and My SQL. Select the respective source, the Data as Leave balance and configure the rest
parameters the same way as described in Import Leave Transaction.

Map the source and destination fields from Fields Mapping.

Map the source and destination fields from Fields Mapping.

The fields USERID,START-DATE, LEAVE-CODE and LASTUPDATEDTIMESTAMP are mandatory for mapping.
If TID is mapped; then ACTION is required to be mapped for importing based on TID.

Matrix COSEC INTEGRATE User Guide 109

After mapping, click on Save button.

The import can be scheduled or manual transfer can be done.

The Import with the other sources are explained in further examples.

110 Matrix COSEC INTEGRATE User Guide

Import from Active Directory

The COSEC INTEGRATE application provides the functionality to import user data from the Active Directory
service of Windows.

The application allows the administrator to map the data fields of the COSEC application database to the data fields
of the active directory.

Now user can click on the Server Configuration button to start the configuring process. The following page
appears. Select the Import from Active Directory option in the Integration Mode field.

In the COSEC Web Server section:

• Specify the web url of the api service of the COSEC WEB application as shown.
• Enter the User Name and Password of the sa user as set in the COSEC WEB application.

In the Active Directory section:

• Specify the IP address or the network name of the Domain Controller.
• Port: Enter the port no. if configured.
• Domain Name: Specify the domain name as shown. For e.g. if the domain name is matrix.com the domain
name is specified as: dc=matrix,dc=com.
• User Name: Specify the username having administrator rights in this field. e.g. matrix\administrator.
• Password: Enter the password of the administrator as per the site settings.
• Enable SSL: To establish secured connection and protecting Data, click on check-box given next to
Enable SSL. It is compulsory to enable this check-box.

The Test Connection button is provided to test the connections with the web server as well as the Active Directory
service.

Matrix COSEC INTEGRATE User Guide 111

Click on Save once done.

Active Directory Import Configuration

This option enables the Admin user to map the fields from the Active Directory service to the appropriate fields in
the COSEC database. The application will list all the fields in the COSEC database which are relevant to the User
Configuration. The administrator needs to select the relevant fields in the Active Directory and then map the same
to the relevant fields of the COSEC database.

Click on the Import Data button. The following page appears.

• Select Active Directory from the Source drop down list.

• Click on Edit.
• In the Fields Mapping section, select the Source Field and Destination Field from the respective drop
down lists.

Click on the Add button. The mapped fields will be visible in the bottom grid as shown. The schedule section
enables the administrator to set the frequency at which the COSEC service will check the active directory for
updates.

• Check the Active box to enable the schedule.

112 Matrix COSEC INTEGRATE User Guide

 • Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will update the destination COSEC database.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

• Filter Records: You can filter the records that are to be imported at scheduled runtime by selecting All or
Custom option.
• If Custom option is selected then select the option for “Apply Filter As Per” and enter the respective
value in the text box.
Click on Save once done.

Manual Transfer

The manual transfer option provides the admin user the flexibility to import user data from the active directory
database as and when required. In order to access this functionality click on the Stop service button to stop the
COSEC INTEGRATE service.

Matrix COSEC INTEGRATE User Guide 113

Click on the Active Directory button followed by the Manual Transfer button. The following window appears.

Select from one of the following options.

• All - Imports all users from the active directory database.
• Recently Added/Updated - Imports only those users whose records have been updated after the last
import.
• Custom - Imports users as per the defined filter.

Click on the Process button. The application will import data of the users as per the specified filter and displays the
status of the import process as shown.

Click on OK to close the window.

114 Matrix COSEC INTEGRATE User Guide

Importing Data from a Customized SAP

The application allows the user to import user data from a customized external MS SQL or Oracle database table.

Integrate supports Import from Oracle upto version Oracle 23AI and Import from MS SQL upto version MS
SQL 16.0.1000.6.

The following figure depicts a sample of the source table structure.

The user can map the required fields as explained earlier. In this case too, the following destination fields need to
be compulsorily mapped with appropriate fields of the source table as shown.
• id
• name
• BLNUPDATEFIELD

Matrix COSEC INTEGRATE User Guide 115

The import data options have the following Schedule options.

• Check the Active box to enable the schedule.

• Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will import the data from the source table.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Click on Save once done.

116 Matrix COSEC INTEGRATE User Guide

Import Events

The COSEC INTEGRATE application provides the functionality to import event data from 3rd party database i.e.
MS SQL and Oracle as well as from Excel.

Integrate supports Import from Oracle from version Oracle 10g to Oracle 23AI.

Integrate supports Import from MS SQL from version SQL Server 2005 to SQL Server 2022.

SQL Server 2005 and SQL Server 2008 R2:

• for installation you need Windows 7 or below versions.
• to establish remote connections in these versions from the PC in which Integrate is installed, make sure
you have enabled TLS 1.0 and 1.1 (that is, Best Practices settings).

Now user can click on the Server Configuration button to start the configuring process. The following page
appears. Select the Import Events option in the Integration Mode field.

In the COSEC Web Server section:

• Specify the web URL of the api service of the COSEC WEB application as shown.

• Enter the User Name and Password of the SA user as set in the COSEC WEB application.

In the Source Details section:

• Select the Import From option as SQL SERVER, ORACLE SERVER or EXCEL.

• Server: Enter the database server name in the following format - Database server name\Instance Name
e.g. dbserver\sqlexpress.

• Database Name: Specify the database name of the source database as per the site settings.

Matrix COSEC INTEGRATE User Guide 117

 • User Name: Specify the database administrator ID in this field.

• Password: Enter the password of the Database administrator as per the site settings.

The Test Connection button is provided to test the connections with the web server as well as the MS SQL or
Oracle Server database.

Click on Save once done.

Now click on Import Data button.The following page appears.This option enables the Admin user to select the
external source database from where the data is to be imported in the destination COSEC table.

Select the source table from the drop down list. The system will get the details of the fields from the source table
and display the same in the Source Field parameter.

118 Matrix COSEC INTEGRATE User Guide

Now the Admin user can start the mapping of the fields from the source database to that of the destination
database as shown. Select the appropriate source and the destination fields and click on the Add button.

The mapped fields will be visible in the bottom grid as shown. The following destination fields need to be
compulsorily mapped with appropriate fields of the source table as shown.

• userid
• event-datetime
• BLNUPDATEFIELD

Click on Save button to commit the changes.

The Schedule section enables the administrator to set the frequency at which the COSEC service will check the
active directory for updates.

• Check the Active box to enable the schedule.

• Specify the Update Interval in seconds, Minutes or Hours to define the frequency at which the application
will update the destination COSEC database.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

Matrix COSEC INTEGRATE User Guide 119

 In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Click on Save once done.

Import Events from Excel

• Select the Import From option as Excel.

• Click Test connection button to test the connection with COSEC web server.

• Then click Save button.

120 Matrix COSEC INTEGRATE User Guide

Import Data

In Import Data page, select the Source as Import Events from Excel.

You can click on to import the sample Import Events file. Then save the sample file at desired location.
Click on Browse button and select the excel file for importing events. You can import excel file of max 15 MB.

For importing excel file, you must have drivers to import.

You must install Access Database Engine compatible to your computer as shown below.

Matrix COSEC INTEGRATE User Guide 121

Now the Excel file is selected as shown below.

Then click on Import Data button. The import process is shown below.

You can view the imported records in User events as shown below.

122 Matrix COSEC INTEGRATE User Guide

Manual Transfer

The manual transfer option provides the Admin user the flexibility to import events from the source database as and
when required. In order to access this functionality click on the Stop service button to stop the COSEC
INTEGRATE service.

Click on the Import Data button followed by the Manual Transfer button. The following window appears.

Select from one of the following options.

• All - Imports all events from the source database.
• Recently Added/Updated - Imports only those events whose records have been updated after the last
import.
• Custom - Imports events as per the defined filter.

Matrix COSEC INTEGRATE User Guide 123

Click on the Process button. The application will import data of the users as per the specified filter and displays the
status of the import process as shown.

Click on OK to close the window.

124 Matrix COSEC INTEGRATE User Guide

Import from My SQL

Select the Import from My SQL Server option in the Integration Mode field. Click on the Edit button.

Integrate supports Import from MySQL from version MySQL 8.0.27 to MySQL 8.3.0.

In the COSEC Web Server section:

• Specify the web URL of the api service of the COSEC WEB application as shown.
• Enter the User Name and Password of the SA user as set in the COSEC WEB application.

In the Source Database Server section:

• The Database Type will be SQL SERVER.
• Server: Enter the database server name in the following format - Database server name\Instance Name
e.g. dbserver\sqlexpress.
• Database Name: Specify the database name of the source SQL database as per the site settings.
• User Name: Specify the database administrator ID in this field.
• Password: Enter the password of the Database administrator as per the site settings.

The Test Connection button is provided to test the connections with the web server as well as MS SQL.
Click on Save once done.

Matrix COSEC INTEGRATE User Guide 125

Import Data Configuration
This option enables the Admin user to map the fields from the external source database table to fields in the
destination COSEC User table. Click on the Import Data button. The following page appears.

Select the source table from the pull down list. The system will get the details of the fields from the source table and
display the same in the Source Field parameter.

Now the Admin user can start the mapping of the fields from the source database to that of the destination
database as shown. Select the appropriate source and the destination fields and click on the Add button.

126 Matrix COSEC INTEGRATE User Guide

The mapped fields will be visible in the bottom grid as shown. The following destination fields need to be
compulsorily mapped with appropriate fields of the source table as shown.

• id
• name
• BLNUPDATEFIELD

This is common to all the import options except the Active directory option.

Matrix COSEC INTEGRATE User Guide 127

Import from Progress OpenEdge

Select the Import from Progress OpenEdge option in the Integration Mode field. Click on the Edit button.

Integrate supports Import from Progress OpenEdge version OpenEdge 12.8.

In the COSEC Web Server section:

• Specify the web URL of the api service of the COSEC WEB application as shown.
• Enter the User Name and Password of the SA user as set in the COSEC WEB application.

In the Progress OpenEdge section:

• Server: Enter the server name with which the Progress OpenEdge database is started.

• Driver Name: Select the Progress OpenEdge driver from the picklist. It is the software driver which will
appear in picklist, once the database is installed.

128 Matrix COSEC INTEGRATE User Guide

 • Port: Enter the port number at which the Progress OpenEdge database is running. It is the port number
(eg: 5003) entered in command prompt while starting the database as shown below.
• Database Name: Enter the database name with which Progress OpenEdge database is started. It is case
sensitive. Thus if Database is started with name cosecDB as shown below then enter the name as
“cosecDB”.

• User Name: Specify the user name as entered while creating the database.

• Password: Specify the password as entered while creating the database. Eg: User name is cosec and
Password is cosec.

The Test Connection button is provided to test the connections with the web server as well as the Progress
OpenEdge database.

Matrix COSEC INTEGRATE User Guide 129

Click on Save button to save the server configuration.

130 Matrix COSEC INTEGRATE User Guide

Import Data Configuration
This option enables the Admin user to map the fields from the external source database table to fields in the
destination COSEC User table. Click on the Import Data button. The following page appears.

Select the Source and click on the Edit button at the bottom of the page.

Select the Source Table from the drop down list. The system will get the details of the fields from the source table
and display the same in the Source Field parameter.

Select the appropriate destination field and click on the Add button. The mapped fields will be visible in the
bottom grid as shown.

Matrix COSEC INTEGRATE User Guide 131

 The following destination fields need to be compulsorily mapped with appropriate fields of the source table
as shown.

• id
• name
• BLNUPDATEFIELD

This is common to all the import options except the Active directory option.

Schedule

• Check the Active box to enable the schedule.

• Specify the Update Interval in seconds, minutes or hours to define the frequency at which the application
will update the destination database.

• Enable Alert For: When scheduled process gets completed then it will send an Alert to the configured
COSEC Server. The Alert can be sent for both Successful as well as Failed transfers.

Select the checkboxes as per your requirement:

Select Success checkbox to send an alert mentioning the details of successfully transferred records to the
configured COSEC Server.

Select Failure checkbox to send an alert mentioning the details of failure in transferring records to the
configured COSEC Server.

If you require Alerts for both the above events, select both the check boxes.

132 Matrix COSEC INTEGRATE User Guide

 In case of partial data transfer i.e. if both the above checkboxes are selected then the connection status
will be considered as Failure and Reason for Failure will be displayed in the Alert.

Example: There are in total 100 records which are to be transferred.

Now, out of 100 records, only 60 records are transferred Successfully and the remaining 40 records have
Failed. Such data transfers are known as partial data transfers.

So here the connection status will be Failure and an alert will be sent to the configured COSEC Server
along with the reason for failed data transfer.

It is to be supported for all integrate modes defined in COSEC Integrate where enable alerts provision is
present i.e. for all the integration modes except for Custom Export-FP Template & Export FP Template to
File.

Matrix COSEC INTEGRATE User Guide 133

Import from Excel

The COSEC INTEGRATE application provides the functionality to import leave transaction data from Excel.

Now user can click on the Server Configuration button to start the configuring process. The following page
appears. Select the Import From Excel option in the Integration Mode field.

In the COSEC Web Server section:

• Specify the web URL of the api service of the COSEC WEB application as shown.

• Enter the User Name and Password of the SA user as set in the COSEC WEB application.

In the Source Details section:

• Select the Import From option as Excel.

The Test Connection button is provided to test the connections with the web server.

Click on Save once done.

134 Matrix COSEC INTEGRATE User Guide

Now click on Import Data button.The following page appears.This option enables the admin user to select the
external source database from where the data is to be imported in the destination COSEC table.

Select the source as Excel. The Data field shows the Leave Transaction.

You can click on to import the sample file for leave transaction. Then save the sample file at desired location.
The leave records can be entered in the sample file.

Then click on Browse button to browse the file from where leave transactions are to be imported. Only .xls and
.xlsx file of maximum size15 MB is allowed for import.

Matrix COSEC INTEGRATE User Guide 135

Then click on Import Data. The leave transaction data will be imported from the selected Excel file to the
configured web server’s database.

For importing excel file, you must have drivers to import.

You must install Access Database Engine compatible to your computer as shown below.

Then click on Import Data button. The import process is shown below.

136 Matrix COSEC INTEGRATE User Guide

Matrix COSEC INTEGRATE User Guide 137
Integrate Alert

When the Integrate scheduled process gets completed, an API will be triggered to the configured COSEC server.

An Alert would be generated for the same and sent to those users as SMS or Email, for whom alert message is
configured in Alert Message Configuration as shown below.

The Contact number and Email ID on which the alert is to be sent; must be specified in User Configuration.

An alert log with error/status message would be generated and displayed in Admin> Views/Logs > Alert View.

Consider a scenario, with configurations: Include Previously Failed is enabled and Retry for failed records =3,
then count will be shown in Alert Message Configuration as displayed in below table.

138 Matrix COSEC INTEGRATE User Guide

 Successfully
Currently Previously Transferred To be displayed in
Process Successfully
Scheduled Failed (from Alert Message
number Transferred
Records Records previously
Failed Records)

1st Scheduled 100 70 0 0 Scheduled|Success

process =100|70

2nd Scheduled 50 20 30 (1st 10 (1st Scheduled|Success

process scheduler) scheduler) =50|20

3rd Scheduled 100 90 20(1st 5(1st scheduler) Scheduled|Success

Process scheduler) 15 (2nd =100|90
30(2nd scheduler) Previously
scheduler) Total=20 Failed|Success =
Total=50 50|20

4th scheduled 50 50 15(2nd 10(2nd Scheduled|Success

Process scheduler) scheduler) =50|50
10(3rd 5 (3rd scheduler) Previously
scheduler) Total=15 Failed|Success =
Total=25 25|15

If the Integrate version and COSEC Server version are different then no alert would be sent to Server and
assigned users even though Alert is configured.

Matrix COSEC INTEGRATE User Guide 139

BACnet Server Configuration

BACnet (Building Automation and Control Network) is an industry-standard protocol that is specifically used in
Building Automation Systems. BACnet provides a standard framework for communication, management,
monitoring and control of various automation functions specific to buildings, such as Lighting, HVAC, Fire Safety,
Access Control and Energy Management Systems. BACnet supports multiple communication protocols, including
Ethernet, TCP/IP, and RS-485, allowing for the integration of devices from different manufacturers and
technologies.

Various Building Management Systems (BMS) rely on BACnet to communicate with each other for the exchange of
data and to control devices and automation systems within a building.

Integration of BACnet with COSEC enhances its capability to integrate with 3rd party Building Management
Systems. With this integration, COSEC Devices and their Events can be used to control various automation
functions.

The COSEC Devices and their Event data will be sent to various 3rd party Building Management Systems (BACnet
clients) through COSEC Integrate. Configuration of BACnet Server in COSEC Integrate will enable it to
communicate with COSEC Server and fetch the desired data which in-turn will be shared with BACnet clients.

To integrate BACnet with COSEC, you need to:

• Configure the BACnet Server in COSEC Integrate. Once configured, these parameters will appear in
Admin Module > System Configuration > BACnet Configuration > BACnet Server Configuration in COSEC
Web.

• Configure the BACnet Server parameters in COSEC Web. For details, refer to Admin Module > System
Configuration > BACnet Configuration > BACnet Server Configuration in the User Guide.

• Configure the BACnet Client List parameters. For details, refer to Admin Module > System Configuration >
BACnet Configuration > BACnet Client List Configuration in the User Guide.

BACnet Server Configuration

Click Server Configuration. The following page appears.

140 Matrix COSEC INTEGRATE User Guide

Click Edit and configure the following parameters:

• Integration Mode: Select the BACnet Server option.

COSEC Web Server

• Web URL: Configure the Web URL of the API service of the COSEC Web Server application.

• User Name: Configure the User Name, for example sa (It can be any user who has API Access Rights
of the COSEC Web Server application.)

• Password: Configure the Password as set for the COSEC Web Server application.

BACnet Server Configuration

• Enable: Select the check box to enable BACnet Server configuration.

• IP Address: Select the IP Address to be assigned to the BACnet Server from the drop-down list. Click

Refresh to refresh the IP List. This is the IP Address list of multiple networks incase the PC on
which Integrate is installed has multiple network connections (excluding loopback IP, that is 127.0.0.1)

Matrix COSEC INTEGRATE User Guide 141

 • Port Number: Configure the Port Number of the BACnet Server (this is the free port of the PC on which
Integrate is running. This port will be used for communication between the Integrate via BACnet Server
and the BMS Client).

• Instance ID: Configure the Instance ID which will be used by BACnet Server to communicate with the
Client. This will be provided by the Client.

• Instance Name: Configure the Instance Name which will be used by BACnet Server to communicate
with the Client. This will be provided by the Client.

• Network Number: Configure the Network Number which will be used by BACnet Server to
communicate with the Client. This will be provided by the Client.

• Polling Interval: Specify the Polling Interval in seconds between two consecutive data fetch requests
from COSEC Integrate to COSEC Web Server.

• Import Events: Select the Events to be imported from COSEC Web Server options — Live Events or
All Events.

• Event Retrieval In: If Import Events is selected as Live Events, configure the Event Retrieval time in
seconds between two consecutive retrieval requests from COSEC Integrate to COSEC Web Server for
Live events.

Click Test Connection to test the connection with COSEC Web Server.

Click Save to save the configuration. Now, click Start Service. Once the service starts, the BACnet Server
Configuration will be fetched by the COSEC Web Server.

Configure the BACnet Server and BACnet Client List parameters in COSEC Web to enable the data transfer from
COSEC Web to the BACnet clients via COSEC Integrate. For details, refer to Admin Module > System
Configuration > BACnet Configuration in User Guide.

For details regarding the Event interactions handled in BACnet, refer to “List of Event Interactions handled in
BACnet Integration”.

142 Matrix COSEC INTEGRATE User Guide

List of Event Interactions handled in BACnet Integration
Door_Alarm_State values based on the event Interaction.

Enum
Event ID Description Enum Value Remark
Description

Dead Man timer expiry

301  Dead-man
alarm generated by <user dead-man-timer-
timer expired Alarm– 256
id : Username : expired
User IN
Username>
Duress detection alarm
302  Duress
detected for <user id : duress-alarm 257
detection
Username>

Panic Alarm generated by

303  Panic Alarm panic-alarm 258
<user id : Username>

FP Memory Full Alarm

304  FP Memory Full
generated for Internal
– Alarm
memory
fp-full-alarm 259
FP Memory Full Alarm
generated for external
memory

305  Door Held open Alarm for door held open

door-open-too-long 2
too long for too long generated.

Door Abnormal alarm

306  Door Abnormal door-abnormal-alarm 260
generated.

Door Force Open alarm

307  Door force open forced-open 3
generated.

308  Door Controller Door Controller Offline

door-offline 261
Offline Alarm generated.

309  Door Controller Alarm for fault in Door

door-fault 5
-Fault Controller generated

310  Tamper Alarm Tamper Alarm generated. tamper 4

Power ON/OFF Detected

314  RTC <for external reader>
(time not in sync)

low battery detected <for

rtc-fault 262
external reader>

RTC Not Detected <for

external reader>

Matrix COSEC INTEGRATE User Guide 143

 315  Event Buffer
Event Buffer Full event-buffer-full 263
Full

Zone Empty alarm

316 Zone Empty generated.(This event is zone-empty-alarm 264
discarded)

Intercom Panic Alarm by

317 Intercom - panic extension number {extn intercom-panic 265
number}generated.

318 Occupancy Occupancy Rule Violated by

occupancy-violation 266
Violated Alarm <user id : Username>

319 Tail- Gating Tail - Gating by <user id :

tail-gating 267
Alarm Username>

320Man Trap Timer Man Trap Timer Violated by man-trap-timer-

268
Violated Alarm <user id : Username> violation

321Access Denied Access Denied Alarm

access-denied-alarm 269
Aalrm generated.

Multiple Unauthorized
322Multiple multiple-
Attempts count reached by 270
Unauthorized Access Alarm unauthorized-access
by <user id : Username>

323-> Custom Alarm 1 Custom Alarm 1 generated. custom-alarm1 271

324->Custom Alarm 2 Custom Alarm 2 generated. custom-alarm2 272

325-> Custom Alarm 3 Custom Alarm 3 generated. custom-alarm3 273

326 User User Unidentified Alarm user-unidentified-

274
Unidentified Generated alarm

Anti-Pass Back Rule

327->Anti-Pass Back
Violated by <user id : passback-violation 275
Violated Alarm
Username>

328->Access Route Access Route Violated by

access-route-violation 276
Violated Alarm <user id : Username>

IO Link Activated by <user

329-> Raise Alarm raise-alarm 277
ID : UserName>

330  Lock Open Too Alarm for lock open for too
lock-open-to-long 278
Long long generated.

Lock abnormal alarm

331  Lock Abnormal lock-abnormal 279
generated.

144 Matrix COSEC INTEGRATE User Guide

 332  Manual Lock Manual lock override alarm
manual-lock-override 280
Override generated.

Alarm acknowledged
through System Interlock/
351  Alarm
Web Jeeves/ACMS/special alarm-acked 281
acknowledged
Functions.
Alarm auto acknowledged

When Multiple
Alarms are active and
Alarm cleared through any of them is cleared
System Interlock/Web (while door is still in
352  Alarm cleared Jeeves/ACMS/Special normal 0 alarm mode) then
Functions. Door Alarm State will
Alarm auto cleared be set to 'alarm (1)'
value. Enum value
will be 1.
This will get the
353  Alarm Re-
Alarm Re-issued previously issued
issued
Alarm state.

Anti-Loiter Zone Rule

354  Anti-Loiter Zone
Violated by <user id : anti-loiter-violation 282
Violated Alarm
Username>
Access Route Timer
333  Access Route Violated by <User ID : access-route-timer-
283
Timer Violated Alarm Username> at <Door violation
Name>
Threshold Temperature
334  Threshold thresold-temperature-
Exceeded by <User ID : 284
Temperature Exceeded exceeded
Username>

335 Face Mask Not Face Mask Not Detected for face-mask-not-
285
Detected <User ID : Username> detected

When Multiple
Alarms are active and
any of them is cleared
(while door is still in
Alarm 1 alarm mode) then
Door Alarm State will
be set to 'alarm (1)'
value. Enum value
will be 1.

Door_Status values based on the event Interaction.

Enum Enum
Event ID Description Remark
Description Value

201  Door Status changed Door status normal. Normal 1024

Matrix COSEC INTEGRATE User Guide 145

 (generate this event
whenever the door state is
Door status locked. LOCKED 1025
changed through special
function or Door M&C page)

Door status unlocked. UNLOCKED 1026

(Also generate this event for

API commands and when
Door Status armed ARMED 1027
external IO link command is
received)

Door status disarmed. DISARMED 1028

202  Dead-man timer Dead man timer

No Change in State.
changed activated.

(generate this event

whenever Dead-man timer
Dead man timer
activated or expired/ No Change in State.
deactivated.
deactivated)

203  DND status changed DND Status activated DND-ACTIVE 1029

DND Status deactivated DND-INACTIVE 1030

204  Aux input status

Aux input activated. AUX-IN-ACTIVE 1031
changed

Generate this event whenever

Aux input normal. AUX-IN-NORMAL 1032
Aux input status is changed.

Aux input fault(open) AUX-IN-FAULT-OPEN 1033

(This event with Disabled in

special field-1 is generated Aux input fault(short) AUX-IN-FAULT-SHORT 1034
against Door M&C user input.)

Aux input disabled. AUX-IN-DISABLE 1035

205  Aux output status

Aux output activated. AUX-OUT-ACTIVE 1036
changed

146 Matrix COSEC INTEGRATE User Guide

 (This event with Disabled in
special field-1 is generated
against Door M&C user
input.)(Also generate this Aux output normal. AUX-OUT-NORMAL 1037
event for API commands and
when external IO link
command is received)

Aux output disabled. AUX-OUT-DISABLED 1038

206  Door sense input DOOR-SENSE-

Door sense input normal 1039
status NORMAL

(This event with Disabled in

Door sense input DOOR-SENSE-FAULT-
special field-1 is generated 1040
fault(open) OPEN
against Door M&C user input.)

Door sense input DOOR-SENSE-FAULT-

1041
fault(short) SHORT

Door sense input

UNUSED 4
disabled.

207  Door Controller

Door controller ON Line DOOR-ONLINE 1043
Communication status

(generate this event

whenever the DC status
Door controller OFF Line DOOR-OFFLINE 1044
changed to ON line or change
to OFF Line)

Door controller is
DOOR-UPRGRADING 1045
upgrading

<User id : Username>
208  Door Open/ Close OPENED 1
has opened the door

OPENED 1
Door Open/Close - Open

Door state changed to

CLOSED 0
Close

<User id : Username>
CLOSED 0
has not operated the door

Door Open/Close - CLOSED 0

NotOperated

Matrix COSEC INTEGRATE User Guide 147

 <User id : Username>
209  Lock Open/ Close OPENED 1
has opened the lock.

<User id : Username>
CLOSED 0
has closed the lock.

<User id : Username>
CLOSED 0
has not operated the lock

Manual lock override. LOCK-OVERRIDE 1046

Present Value in Panel Custom Object based on Event Interaction

Event ID Description Property Value

Duress detection alarm detected for

302  Duress detection duress-alarm
<user id : Username>

Door Controller Offline Alarm

308  Door Controller Offline door-offline
generated.

Alarm for Mains fail of Master

311  Master Controller Mains fail Alarm master-controller-mains-fail-alarm
Controller generated.

Alarm for Battery fail of Master

312  Master Controller Battery fail master-controller-battery-fail-alarm
Controller generated.

313  Master Alarm – MC Alarm input MC input Master alarm generated master-alarm-input

315  Event Buffer Full Event Buffer Full event-buffer-full

Zone Empty alarm generated.(This

316 Zone Empty zone-empty-alarm
event is discarded)

Occupancy Rule Violated by <user id

318 Occupancy Violated Alarm occupancy-violation
: Username>

353  Alarm Re-issued Alarm Re-issued alarm-reissued

Alarm cleared through System

Interlock/Web Jeeves/ACMS/Special
352  Alarm cleared normal
Functions.
Alarm auto cleared

204  Aux input status changed Aux input activated. AUX-IN-ACTIVE

Generate this event whenever Aux input

Aux input normal. AUX-IN-NORMAL
status is changed.

148 Matrix COSEC INTEGRATE User Guide

 Aux input fault(open) AUX-IN-FAULT-OPEN

(This event with Disabled in special field-1 is

Aux input fault(short) AUX-IN-FAULT-SHORT
generated against Door M&C user input.)

Aux input disabled. AUX-IN-DISABLE

205  Aux output status changed Aux output activated. AUX-OUT-ACTIVE

(This event with Disabled in special field-

1 is generated against Door M&C user
input.)(Also generate this event for API Aux output normal. AUX-OUT-NORMAL
commands and when external IO link
command is received)

Door controller ON Line PANEL_ONLINE

As no event is received for PANEL connected and
disconnected.
Door controller OFF Line PANEL_OFFLINE

Generic Notes

Applicable
Sheet Name Naming Convention Description Instance Number
Device
{COSEC_Door
Binary Output-IO IO 800 Name}_AUXOUT_{PortNo}_{1}{
MID}{0}/{2}{MID}{DID}

Device Name mentioned in

{COSEC_Door_Name} COSEC Software. Example: IO
Controller 1
Naming Convention :
{COSEC_Door Direct Door :
Direct Door
Name}_AUXOUT_{PortNo}_{1}{ {PortNo}{1}{MID}{0}
MID}{0}
Naming Convention :
{COSEC_Door Panel Door :
Panel Door
Name}_AUXOUT_{PortNo}_{2}{ {PortNo}{2}{MID}{DID}
MID}{DID}

PortNo 1 to 8

{COSEC_Door
Multistate Input IO 800 Name}_AUXIN_{PortNo}_{1}{M
ID}{0}/{2}{MID}{DID}

Device Name mentioned in

{COSEC_Door_Name} COSEC Software. Example: IO
Controller 1
Naming Convention :
{COSEC_Door Direct Door :
Direct Door
Name}_AUXIN_{PortNo}_{1}{M {PortNo}{1}{MID}{0}
ID}{0}

Matrix COSEC INTEGRATE User Guide 149

 Naming Convention :
{COSEC_Door Panel Door :
Panel Door
Name}_AUXIN_{PortNo}_{2}{M {PortNo}{2}{MID}{DID}
ID}{DID}

PortNo 1 to 8

COSEC PANEL
COSEC Panel PANEL {COSEC_Panel Name}_{MID} {MID}
(All variants)

Device Name mentioned in

{COSEC_PANEL_Name} COSEC Software. Example:
PANEL 1
Direct Doors
{COSEC_Door
and Panel Doors
Access Door Name}_{1}{MID}{0}/
All the Door
{2}{MID}{DID}
Controllers
Device Name mentioned in
{COSEC_Door_Name} COSEC Software. Example:
Door 1

Naming Convention :
Direct Door {COSEC_Door Direct Door : {1}{MID}{0}
Name}_{1}{MID}{0}

Naming Convention :
Panel Door {COSEC_Door Panel Door : {2}{MID}{DID}
Name}_{2}{MID}{DID}

Binary Output-IO

Event ID Description Present Value Value Remarks

By default, when there are no AUX

205  Aux output
Aux output activated. ACTIVE 1 Out event then Present Value will be
status changed
INACTIVE.
Whenever an Event is generated for
AUX Out Activated then Event_State
205  Aux output
Aux output normal. INACTIVE 2 will be set to OFF_NORMAL. Present
status changed
value reference shall be updated to
ACTIVE.

Multistate Input-IO

Present Value will

Event ID Description be fetched from State Text
Array

{{"AUX-IN-ACTIVE"},{"AUX-IN-
Aux input NORMAL"},{"AUX-IN-FAULT-
204 -> Aux input status changed 0
activated. OPEN"},{"AUX-IN-FAULT-SHORT"},{AUX-
IN-DISABLED}}

150 Matrix COSEC INTEGRATE User Guide

 {{"AUX-IN-ACTIVE"},{"AUX-IN-
NORMAL"},{"AUX-IN-FAULT-
204 -> Aux input status changed Aux input normal. 1
OPEN"},{"AUX-IN-FAULT-SHORT"},{AUX-
IN-DISABLED}}

{{"AUX-IN-ACTIVE"},{"AUX-IN-
Aux input NORMAL"},{"AUX-IN-FAULT-
204 -> Aux input status changed 2
fault(open) OPEN"},{"AUX-IN-FAULT-SHORT"},{AUX-
IN-DISABLED}}

{{"AUX-IN-ACTIVE"},{"AUX-IN-
Aux input NORMAL"},{"AUX-IN-FAULT-
204 -> Aux input status changed 3
fault(short) OPEN"},{"AUX-IN-FAULT-SHORT"},{AUX-
IN-DISABLED}}

{{"AUX-IN-ACTIVE"},{"AUX-IN-
Aux input NORMAL"},{"AUX-IN-FAULT-
204 -> Aux input status changed 4
disabled. OPEN"},{"AUX-IN-FAULT-SHORT"},{AUX-
IN-DISABLED}}

COSEC Panel

Present Value State text

Event ID Description Event Type
reference

Duress detection alarm detected for <user

302 -> Duress detection duress-alarm ALARM
id : Username>

308 -> Door Controller Offline Door Controller Offline Alarm generated. door-offline ALARM

311 -> Master Controller Mains fail Alarm for Mains fail of Master Controller master-controller-mains-
ALARM
Alarm generated. fail-alarm

Alarm for Battery fail of Master Controller master-controller-battery-

312 -> Master Controller Battery fail ALARM
generated. fail-alarm

313 -> Master Alarm – MC Alarm input MC input Master alarm generated master-alarm-input ALARM

315 -> Event Buffer Full Event Buffer Full event-buffer-full ALARM

Occupancy Rule Violated by <user id :

318 -> Occupancy Violated Alarm occupancy-violation ALARM
Username>

353 -> Alarm Re-issued Alarm Re-issued alarm-reissued ALARM

Alarm cleared through System Interlock/

352 -> Alarm cleared Web Jeeves/ACMS/Special Functions. normal ALARM
Alarm auto cleared

204 -> Aux input status changed Aux input activated. AUX-IN-ACTIVE Transaction

Matrix COSEC INTEGRATE User Guide 151

 Generate this event whenever Aux
Aux input normal. AUX-IN-NORMAL Transaction
input status is changed.

Aux input fault(open) AUX-IN-FAULT-OPEN Transaction

(This event with Disabled in special

field-1 is generated against Door M&C Aux input fault(short) AUX-IN-FAULT-SHORT Transaction
user input.)

Aux input disabled. AUX-IN-DISABLE Transaction

205 -> Aux output status changed Aux output activated. AUX-OUT-ACTIVE Transaction

(This event with Disabled in special

field-1 is generated against Door M&C
user input.)(Also generate this event Aux output normal. AUX-OUT-NORMAL Transaction
for API commands and when external
IO link command is received)

152 Matrix COSEC INTEGRATE User Guide

SECURITY SOLUTIONS

MATRIX COMSEC

Head Office:
394-GIDC, Makarpura, Vadodara - 390010, India.
Ph:(+91) 18002587747
E-mail: [email protected]

www.MatrixComSec.com