How to integrate XPLANY with XCALLY Database

ON THIS PAGE

1 How to Configure MySQL Connector Integration?
2 How to Launch a Query on Estimate Shifts?

How to Configure MySQL Connector Integration?

If you want to connect your database to XPLANY, you can configure a MySQL integration from Connector Section, creating a new connector.

Remember that in order to enable connector, port configured to connect the database has to be opened specifying IP 13.53.57.21 as source address, that corresponds to our API server IP address.

From XPLANY Architecture, the different microservices pass requests to NAT to go out with single connection and get to XCALLY customer servers.

Customer has own firewall where it is necessary to write rule to accept requests coming from port 3306 (MySQL port default) and from IP 13.53.57.21 (if this rule is not entered, NAT tries to connect to MySQL but it is blocked by firewall which rejects the request.

Remember that if you use a port different from 3306 you need to enter port number used on MySQL.

On MySQL you have several databases and also several users.

If for example on XPLANY you insert user X - password Y - database motion and XPLANY sends request to make MySQL connector, going to the database with user Z - password W - database motion: connection is refused because user does not have permissions to read that database. So it’s important to check that user has permissions in read mode.

For MySQL user it’s necessary to connect with mysql u root → insert password → enter on database to create new user to access the mySQL database

You can create new user in read mode to be able to access MySQL database from outside:

inserting XPLANY ip_address (+your username/password):

CREATE USER 'username'@'ip_XPLANY_NAT' IDENTIFIED BY 'password';

or entering this command to allow user to connect from any machine (+your username/password):

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Don't create the user with the command CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
otherwise user is only enabled from the local machine and he can only query mysql from localhost

Then you need to add permissions to be able to read XCALLY database. Remember not to grant all privileges, but grant mysql only read permission with select (+your username/password):

GRANT SELECT ON motion2.* TO 'username’@‘IP_XPLANY_NAT’ IDENTIFIED BY 'password';

With this command there is the possibility to make the select on all motion2 tables.

How to Launch a Query on Estimate Shifts?

On XPLANY it’s possible to create a prediction of shifts, understanding how many resources can be needed, based on customer's database. By Estimate Shifts section it’s necessary to enter the query to be launched for the algorithm, to tell which database data to retrieve.

To configure the query, to connect database with XPLANY, you always need 3 columns:

service to manage
date of interaction, e.g. when the call was made, when an e-mail/sms was received…
duration of interaction expressed in seconds.

In order to integrate XCALLY to XPLANY for example for voice calls, it's possible to use a string like that:

SELECT SUBSTRING_INDEX(lastdata, ',', 1) AS service, calldate AS date,
duration FROM cdr
WHERE lastapp='Queue'

With this configuration, all incoming calls in the queue are filtered on all services.

SELECT SUBSTRING_INDEX(lastdata, ',', 1) AS service, calldate AS date,
duration FROM cdr
WHERE lastapp='Queue'
and SUBSTRING_INDEX(lastdata, ',', 1) in ('Support', 'Sales')

Instead with this specific configuration, all incoming calls in the queue are filtered on certain services (on Support and Sales queues).

The query can be launched on all the various channels. For instance if you want to run a query for example on the mail service, you can create a query like that:

SELECT MailAccountId as service, createdAt as date,
TIMESTAMPDIFF (SECOND, createdAt, updatedAt)
as duration from mail_interactions

In this case you have selected all mail interaction, inserting CreatedAt as date and entering in duration the difference between date of first read message and the arrival of last interaction (obviously in the calculation of the interaction manage you need to remove the non-operating hours).