Call Center Stats PRO is powerfull cause of their flexibility, one important feature is that you can integrate external data from other tables or MySQL / MariaDB based databases.

The process require a minimum knowledge about this engine and how the JOIN functions may be usefull to integrate external database information.

Although the options and parameters are documented in the software as well, we owed you the detailed explanation of how to access and process that possibility so let’s start!

Brief instructions on how to use Join

MySQL JOIN function lets you add one or several columns or even an entire table to a SELECT query output. You need to relate both tables to get a succesfull JOIN.

For a succesfull relationship, you need a matching field on both tables. As an Asterisk example:

TableA contains fields related with the call including the unique_id field that identifies the call itself.

TableB contains fields related with another things like notes or ratings or whatever you want but should also include the unique_id in this example.

The unique_id field in both tables is the matching field that joins those tables. When the unique_id values (on both tables) match, the result will output both tables rows in a single row ‘joined’.

Lets see an example (from qstats database):

select * from qstats.queue_stats where uniqueid="1541606059.165" returns the following:

+----------------+----------------+---------------------+-------+--------+--------+----------+----------+-------+----------+-------+
| queue_stats_id | uniqueid       | datetime            | qname | qagent | qevent | info1    | info2    | info3 | info4    | info5 |
+----------------+----------------+---------------------+-------+--------+--------+----------+----------+-------+----------+-------+
|          23673 | 1541606059.165 | 2018-11-07 12:54:31 |     4 |      1 |     14 | 43821666 |          |       |          |       |
|          23674 | 1541606059.165 | 2018-11-07 12:54:31 |     4 |      1 |     15 |          | 42468909 | 1     | 43821666 |       |
|          23675 | 1541606059.165 | 2018-11-07 12:57:56 |     4 |      1 |      1 | 1        | 1        | 205   |          |       |
+----------------+----------------+---------------------+-------+--------+--------+----------+----------+-------+----------+-------+

and the next query from an external database:

select * from base_out.test_out

+----+----------------+--------------+
| id | unique_id      | dato         |
+----+----------------+--------------+
|  1 | 1541606059.165 | john doe     |
+----+----------------+--------------+

Results on both queries shows the same unique_id that will match if you use a JOIN function, this is how you apply in mysql:

select * from queue_stats_mv LEFT JOIN base_out.test_out ON queue_stats_mv.real_uniqueid=test_out.unique_id where uniqueid="1541606059.165";

With the following result (quite difficult to read? sorry about that):

+----------------+----------------+---------------------+-------+--------+--------+----------+----------+-------+----------+-------+------+----------------+--------------+
| queue_stats_id | uniqueid       | datetime            | qname | qagent | qevent | info1    | info2    | info3 | info4    | info5 | id   | unique_id      | dato         |
+----------------+----------------+---------------------+-------+--------+--------+----------+----------+-------+----------+-------+------+----------------+--------------+
|          23673 | 1541606059.165 | 2018-11-07 12:54:31 |     4 |      1 |     14 | 43821666 |          |       |          |       |    1 | 1541606059.165 | john doe     |
|          23674 | 1541606059.165 | 2018-11-07 12:54:31 |     4 |      1 |     15 |          | 42468909 | 1     | 43821666 |       |    1 | 1541606059.165 | john doe     |
|          23675 | 1541606059.165 | 2018-11-07 12:57:56 |     4 |      1 |      1 | 1        | 1        | 205   |          |       |    1 | 1541606059.165 | john doe     |
+----------------+----------------+---------------------+-------+--------+--------+----------+----------+-------+----------+-------+------+----------------+--------------+

So, you can see the data ‘joined’ in the same result. Now you need to apply the same to Call Center Stats PRO. For this, you need to open Setup => Report Designer and add a JOIN_TABLE entry.

By using JOIN_TABLE you can perform relational queries with your own tables/data using standard SQL statements. The parameter field will contain the table field name, for example: f.rating as rating, while the value field will containt the actual SQL join statement, for example: LEFT JOIN raitings f ON q.real_uniqueid=f.uniqueid

Now, if we have a table in the same database qstats or in an external database (out of qstats), we can add it, following the prior example:

By adding the keyword:

Keyword: JOIN_TABLE
Parameter: test_out.dato as dato
Value  : LEFT JOIN base_out.test_out ON q.real_uniqueid=test_out.unique_id

The ‘dato’ field name can be used also to add it in other keywords like XXXXX_DISPLAY_ROWS that define the rows to show on different reports.

Joined data will be shown in the detailed reports for those results where uniqueid fields match in both tables. If there is no match, then you will only see first table (table queue_stats or ‘q’) data only as result.