Customised pascom Analytics

Overview

With pascom Analytics you can create not only your own dashboard with existing graphs but also customize the underlying data sources.

The requirement for this is that you are familiar with the basics of pascom Analytics and SQL.

Getting Started

The easiest way of understanding the SQL queries used is simply to edit existing graphs. First copy an existing dashboard (see Create Edit Custom Dashboards). Then you can edit graphs and tables and adjust the SQl.

Click on any graph or tablet on Edit:

Now you can adapt and test the SQL:

Further information, in particular on variables, can be found in the editor under Show Help.

The database used is PostgreSQL. Information about PostgreSQL in connection with Grafana (Analytics Tool used by pascom) can be found here Using PostgreSQL in Grafana.

SQL functions

pascom provides SQL functions for querying data. Similar to tables, these can be used in SQL queries.

filter_calls ()

This function returns all completed calls. Active calls i.e. live calls that are ongoing at the time the query is made are not included in the filter.

Parameters

filter_calls (
    from_timestamp,
    to_timestamp,
    call_type,
    filter_user,
    filter_label,
    filter_from_name,
    filter_from_number,
    filter_to_name,
    filter_to_number
)

Return Values

filter_queue_calls ()

This function returns all completed incoming calls made to a team. Active calls that are still ongoing at the moment of the query (i.e.live) are not included.

Parameters

filter_queue_calls (
    from_timestamp,
    to_timestamp,
    filter_team,
    filter_user,
    filter_from_name,
    filter_from_number,
    filter_label
)

Return values

SQL tables

mdphonecallrecord

In mdphonecallrecord every single step that a call takes through the pascom phone system is saved. A call therefore creates many entries in the mdphonecallrecord. The individual entries are linked to a call via the phonecallrecord_chain field.

In the main, please only use the SQL functions offered by pascom and only access this table if needed for any details.

Example attended transfer

In the mdphonecallrecord an entry is created for each step.

As a step, we mean every object in pascom that can be reached via an extension.

In our example, the call flow is as follows:

• Caller calls user A directly (101)
• User A puts caller on hold and calls user B (102)
• User A connects caller to user B (102)

The sample call flow therefore creates three entries in the database (only excerpts):

The following can be seen from the data:

• The caller spoke to user A for 13 seconds (connected - holdduration)
• User A spoke to user B for 12 seconds and then transferred the caller to user B.
• User A called user B for 4 seconds (duration - connected) before he answered

Example Call via a IVR / Team

In the mdphonecallrecord an entry is created for each step.

As a step, we mean every object in pascom that can be reached via an extension.

In our example, the call flow is as follows:

• Caller is prompted to select a team in IVR menu and chooses 1 (501)
• The team greets the caller with an announcement and calls the members in parallel (201)
• User A is called and does not answer the call (101)
• User B is called and answers the call (102)

The example call flow therefore creates four entries in the database (only excerpts):

The following can be seen from the data:

• Steps 2 to 4 run in parallel. This is recognizable by the same parentid.
• The team (step 2) runs until the call has been processed by a user.
• The total duration of the call is therefore the duration of step 1 and step 2, i.e. 37 seconds.
• User A and user B were called in parallel (see timestamp).
• For user A, the call rang for 10 seconds but was not answered (duration - connected = 10).
• User B answered the call after 10 seconds (duration - connected = 10) and spoke for 20 seconds.
• User B put the caller on hold for 6 seconds.

Field Description mdphonecallrecord

phonecallrecord_result

phonecallrecord_resultdetails

phonecallrecord_via

phonecallrecord_viadetails