Call Statistics in Grafana

• What is sipgate.io?
• What are Call Statistics useful for?
• In this Tutorial
• Architectural Overview
• Setup
• Run the Application
• Custom Teams
• How is it implemented?
  • Events
  • Multiple Containers using Docker Compose
  • Database Scheme
  • Event Handler
  • Special Cases
  • Queries in Grafana
• First overview of the Grafana dashboard
  • Available Panels
  • Filter Options

What is sipgate.io?

sipgate.io is a collection of APIs, which enables sipgate’s customers to build flexible integrations matching their individual needs.
Using our node-library you can receive events and process them live.
In this tutorial we show you how you can create a Grafana dashboard showing your most important key performance indicators (KPIs) with sipgate.io.

What are Call Statistics useful for?

When providing services for your customers you really want to keep track of calls that come in and out to your team.
Check the quality of your support by analyzing the time people currently wait in your queue.
How busy your teams are can be derived from the amount of calls that are currently active so you can reinforce them when necessary.
Looking at data from a large time period shows you whether introduced changes bring the expected result. Additionally these informations indicate call load peaks.

To visualize these KPIs we use an open source tool called Grafana.
The necessary live data can be easily collected in a small node-project by using our sipgate.io node-library.

In this Tutorial

You will learn how to set up a small server which handles received call-events and writes them to a database.
Grafana makes use of the data saved to this database and creates dashboards answering the following questions:

• How many callers are waiting in the queue?
• How many calls are currently active?
• What is the average duration a caller has to wait before reaching someone?
• When do phone calls spike over time?
• What is the average call duration?
• What percentage of calls go to voicemail?

The code for this tutorial can also be found on GitHub.
See the finished result on our solution page.

Architectural Overview

To get a better overview of the different components and how they interact with each other take a look at the following breakdown:

• Call Statistics Service: the call statistics service is the core of the project and receives call information from sipgate.io.
  It processes the calls before writing them to the database, enabling Grafana to make the most use of it.
  It is based on Node.js and uses the Node.js library sipgateio-node for the communication with sipgate.
  Furthermore it handles the authentication to the sipgate REST API using OAuth2.
• Database: All calls, groups and predefined teams are stored here. We will use the MariaDB fork of MySQL.
• Grafana: To visualize the collected data we make use of the interactive web application Grafana which is open source.

For a consistent and easy setup in both, the development and production environment, we chose Docker and Docker Composer to containerize and host these three services.

Setup

To get started you need a sipgate account with a sipgate.io package booked. Further information about what account suits you best and how to add sipgate.io can be found in the get started guide.

Install Docker

In order to orchestrate and set up different containers for the installation of the previously mentioned dependencies Docker and Docker Compose is necessary.
Please follow the instructions on the Get Docker page for your dedicated system and the Install Docker Compose respectively.
After that your system is ready to host multiple virtual containers as multi-container application.

For Mac Users: Please make sure that docker is running by opening your installed desktop app. Otherwise the docker commands are not executable. Moreover you need to install Homebrew to install the following dependencies with this command:

   brew install coreutils
   ln -s /usr/local/bin/greadlink /usr/local/bin/readlink

Port forwarding

When using webhooks in production you will want to run your code on a proper webserver with a proper address.
However, for the purpose of development we recommend using a service that makes your local environment accessible via the internet.
This makes it much easier to quickly test out your written code.

There are various free services that can be used to accomplish this.
Some examples are localhost.run or ngrok.
Either one supplies you with a public URL which can be used to receive webhooks from sipgate.
Just make sure that you forward the correct port (in this tutorial we will be using port 8080) and that the provider that you choose offers secure connections through HTTPS.

To make the Grafana dashboard accessible from the outside you will also need to forward the port 3009.

Webhook, OAuth2 Client and environment variables

For the integration with sipgate you need to do the following steps:

• set up an OAuth2 client to authorize the call statistics service
• configure the webhook URLs for events to both directions
• set some required environment variables

You have the following options to do so:

a) Setup dialog

This option is only available on Linux or Mac and runs a setup dialog that applies the previously mentioned steps. You can find the script under setup.sh and easily execute it with:

chmod +x ./setup.sh
./setup.sh

It will ask you for all the following information.

• Your sipgate credentials
• Your webhook URL
• The base URL for the authentication server

This information is required to create an OAuth2 client and configure the webhook URLs in your sipgate account. Your sipgate credentials are only used once and will not be stored. The base URL depends on the system that you want the project to run on. In most cases the default http://localhost:8080 should work. Running it on a headless server requires you to type in a hostname or domain, so you can authenticate the service remotely.

Note: When you change the port you also need to change the port used in the docker-compose.yml file.

The following information is required for the database setup. You normally can use the default values included with the script and choose a secure password.

• MySQL host
• MySQL database
• MySQL user
• MySQL password

Note: When you change the MySQL database you also need to change the database used in the .initdb.d/1_init_schema.sql file.

b) Manually

This alternative describes how to perform the steps manually in case you want more control or the setup dialog does not work for you.

Head over to console.sipgate.com and create a new OAuth2 client.

You will also need to add a redirect URI to http://localhost:8080/auth-code. In case your system is running headless you might consider using a hostname or domain instead to authenticate the service remotely.

Note: When you change the port you also need to change the port used in the docker-compose.yml file.

Note: When you change the endpoint /auth-code you also need to change the constant AUTHENTICATION_CODE_ENDPOINT defined in the call-statistics-service/src/AuthServer.ts file.

When authenticating the call statistics service, this endpoint will accept the authentication token.

Reanme the .env.example to .env by renaming it:

   mv .env.example .env

In the client details section, copy the Id, Secret and redirect URI and paste them in the .env file:

   SIPGATE_CLIENT_ID='...'
   SIPGATE_CLIENT_SECRET='...'
   SERVICE_BASE_URL=http://localhost:8080 # part of the redirect URI

2. Go to the „Webhooks“ options and set the incoming and outgoing URLs to the address that the call statistics service will be available at. This URL is either your hostname or your domain. More information of making your server accessible from outside can be found here. Again, set the variable in your .env file:

   SIPGATE_WEBHOOK_URL=https://your-address.com

3. Next you have to fill in some required information for the database setup in the .env file. You normally can use the default values as specified here and choose a secure password:

   MYSQL_HOST=db
   MYSQL_DATABASE=call_statistics
   MYSQL_USER=user
   MYSQL_PASSWORD=<your_secret_password>

Note: When you change the MySQL database in the .env file make sure to also rename the database model in the .initdb.d/1_init_schema.sql file.

Run the Application

Now that you have set up the Oauth2 client and environment variables, you can boot up the project:

sudo docker-compose up

Now you need to navigate to https://localhost:8080/auth to first authenticate your sipgate account in order to perform REST API calls. You are then forwarded to Grafana.
At this point you should be able to login in to the default account of Grafana user: admin, password: admin and get promted to modify them to more secure credentials.
After you have logged in successfully, you should be redirected to the Grafana dashboard.

Note: Grafana can only display statistics for events that have been collected while the call statistics service was running. When you set up a fresh instance of the service there are no events collected yet. Therefore your dashboard might be empty or show no data:

Otherwise you can find the projects dashboard by navigating in the left side menu to Dashboards (icon that shows four squares) → Manage and then click on the Call Statistics entry in the content pane.

For more detailed description of the statistics dashboard read the chapter First overview of the Grafana dashboard.

Custom Teams

You can filter the statistics in Grafana by groups and teams. Consider the following example:

You have a sales department which has one phone number for customers to call. Inside the sales department are several teams which look after product families. Now you can filter the statistics for the whole sales department and for the single teams.

Groups are automatically pulled from your sipgate account.
Teams on the other hand are just a custom collections of phone numbers. This concept is unique to this project and enables more flexible filtering options.

If you want to add custom teams, you can do so by adding the corresponding numbers to a team label in the teams.json:

[
  {
    "name": "Sales Team for Product A",
    "numbers": ["+49211975379020", "+4921197537902"]
  }
]

A team must contain at least two numbers or it will be skipped.

How is it implemented?

As we already touched upon in the Architectural Overview section, the service listens to call events provided by the sipgate.io Push API, which are then processed and written to the MySQL database. Finally, Grafana queries that data and visualizes it in the Dashboard.

Multiple Containers using Docker Compose

To combine the three services we use Docker Compose which is configured in docker-compose.yml. This file guarantees effortless administration and all containers acting as a single distributed system. All mentioned images are managed by Docker or built using the Dockerfile that can be found at call-statistics-service/Dockerfile and is based on a Node.js image which is hosted by Docker.

Events

There are three kinds of events we are looking out for:

• NewCallEvents
  When someone tries to establish a call, a NewCallEvent is submitted.
• AnswerEvents
  When the callee picks up their phone, an AnswerEvent is triggered.
• HangUpEvents
  Finally, a HangUpEvent signals that a call has finished, either by hangup or voicemail etc.

Database Scheme

When the database instance gets initialized for the first time the scheme as described below will be performed once. It is stored in .initdb.d/1_init_schema.sql.

Calls

The main table is the calls table:

CREATE TABLE calls (
    call_id VARCHAR(255) PRIMARY KEY,
    start DATETIME NOT NULL,
    end DATETIME,
    answered_at DATETIME,
    direction ENUM('in','out') NOT NULL,
    mastersip_id VARCHAR(255),
    extension VARCHAR(255),
    caller_number VARCHAR(255) NOT NULL,
    callee_number VARCHAR(255) NOT NULL,
    answering_number VARCHAR(255),
    hangup_cause ENUM('normalClearing', 'busy', 'cancel', 'noAnswer', 'congestion', 'notFound', 'forwarded'),
    group_extension VARCHAR(255) NULL,
    voicemail BOOLEAN NOT NULL DEFAULT false,
    crashed BOOLEAN NOT NULL DEFAULT false
);

When our call statistics service receives a NewCallEvent, it creates a new row of data with basic information like caller/callee number, time of creation and more. Importantly, the answered_at and end timestamps will be left NULL. They will get populated later when receiving the Answer- and HangupEvent respectively.

Our SQL queries can now distinguish between active and inactive calls, see whether a call has been answered etc. by simply checking which timestamps are NULL.

Groups

The groups table saves the groups from your sipgate account for calls initiated or addressed to them:

CREATE TABLE groups (
    extension VARCHAR(255) PRIMARY KEY,
    alias VARCHAR(255)
);

To ensure integrity to the groups table, we add a foreign key to the calls table which references the extension and prevent removing groups that are still referenced by the call table:

ALTER TABLE calls ADD FOREIGN KEY (group_extension) REFERENCES groups(extension) ON UPDATE CASCADE ON DELETE RESTRICT;

To allow users to filter calls in the Grafana dashboard that don’t belong to any group we add a custom group to our table:

INSERT INTO groups VALUES('other', 'Other');

Teams

The custom teams are declare in the teams table:

CREATE TABLE teams (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

For adding phone numbers to a team we will create the relationship table teams_numbers which references the teams table:

CREATE TABLE teams_numbers (
    team_id INTEGER NOT NULL,
    number VARCHAR(255) NOT NULL,
    FOREIGN KEY(team_id) REFERENCES teams(id)
);

This table will contain every relationship from a phone number to a team and can also have multiple relations.

Similar to the default group for non-group calls we will have the same for teams:

INSERT INTO teams VALUES(0, 'Other');

Authentication parameters

The following table contains tokens that are necessary for the OAuth2 authentication standard:

CREATE TABLE authentication_params (
    token_type ENUM('access', 'refresh') PRIMARY KEY,
    token_value TEXT NOT NULL
);

Event Handler

Now we take a look at the EventHandler class which is implemented in call-statistics-service/src/EventHandler.ts and handles incoming webhook events sent by the sipgate Push API.

There are three public methods for every type of event to handle it.

handleOnNewCall

public handleOnNewCall = async (
    newCallEvent: NewCallEvent
  ): Promise<void> => {
    console.log(`newCall from ${newCallEvent.from} to ${newCallEvent.to}`);

    if (this.isVoicemailCall(newCallEvent)) {
      await this.handleVoicemail(newCallEvent, new Date());
      return;
    }

    await this.handleRegularNewCall(newCallEvent, new Date());

    await this.insertCallIntoGroups(newCallEvent);
};

First of all we check whether the call is redirected to the voicemail, since it will be tagged explicitly voicemail in the database.

Otherwise it will be handled as a regular call and all received event information like caller number, callee number, direction are stored in the database as well as the current time.

Additionally, we request further information for the numbers (depending on the direction, the caller or callee number) to find out, whether they are assigned to a group. If so, we update the group information in the groups table and update the calls group_extension in the database.

handleOnAnswer

 public async handleOnAnswer(answerEvent: AnswerEvent) {
    console.log(`answer from ${answerEvent.from} to ${answerEvent.to}`);
    await this.updateAnswerDateAndNumber(answerEvent, new Date());
  }

When the event handler receives an AnswerEvent it will update a database entry with the current time:

private async updateAnswerDateAndNumber(
    answerEvent: AnswerEvent,
    date: Date
  ) {
    let callData = {
      answeredAt: date,
      answeringNumber: answerEvent.answeringNumber,
      crashed: false,
    };

    if (answerEvent.fullUserId) {
      const splitUserIdResult = splitFullUserId(answerEvent.fullUserId);
      callData["masterSipId"] = splitUserIdResult.masterSipId;
      callData["userExtension"] = splitUserIdResult.userExtension;
    }

    await this.database.updateCall(answerEvent.callId, callData);
  }

This method builds a callData object to update the call in the database. Prior to that we check whether the answerEvent includes a fullUserId because for a group call we do not know who answered the call until the answerEvent is received. Then the call entry will be updated in the database.

handleOnHangUp

public async handleOnHangUp(hangUpEvent: HangUpEvent) {
    console.log(`hangup from ${hangUpEvent.from} to ${hangUpEvent.to}`);
    await this.updateEndDateOnCall(hangUpEvent, new Date());
}

Upon receiving a HangUpEvent the database entry will be upated with the corresponding event information:

 private async updateEndDateOnCall(hangupEvent: HangUpEvent, date: Date) {
    await this.database.updateCall(hangupEvent.callId, {
      end: date,
      hangupCause: hangupEvent.cause,
      crashed: false,
    });
}

Here we just set the end time of the call and the hangupCause received from the hangupEvent.

Special Cases

In the previous chapter we simplified the event handler for readability. Here we describe some further details we implemented to handle more specific cases.

Voicemail

As already mentioned in the former chapter we check whether incoming NewCallEvents are voicemail calls or not:

private isVoicemailCall(newCallEvent: NewCallEvent) {
    return (
      newCallEvent.users?.length == 1 && newCallEvent.users[0] == "voicemail"
    );
}

To handle a voicemail call the following method decides either to insert a new call or to update an existing call. This is depending on how the voicemail is reached. There are two cases:

• The call is directly answered (e.g. your phone is offline), then a new call will be added
• The call is answered after a certain time, then the call will be updated

private async handleVoicemail(newCallEvent: NewCallEvent, date: Date) {
  const origCallEvent = await this.database.getCall(
    newCallEvent.originalCallId
  );
  if (origCallEvent.length == 0) {
    await this.database.addCall(newCallEvent.callId, {
      start: date,
      direction: newCallEvent.direction,
      callerNumber: newCallEvent.from,
      calleeNumber: newCallEvent.to,
      voicemail: true,
    });
  } else {
    await this.database.updateCall(newCallEvent.originalCallId, {
      callId: newCallEvent.callId,
      voicemail: true,
    });
  }
}

To enable filtering for voicemail calls there is the voicemail attribute which will be set to true.

Crashed calls

If at launch time the server registers active calls without HangUpEvent, it will initially tag them with a crash flag. This way, if during downtime the server misses HangUpEvents, we can keep them out of the active call statistics.

The following method is implemented in the Database class that can be found at call-statistics-service/src/Database.ts:

public async crashCheck() {
  let queryString: string =
    "UPDATE calls SET crashed=true WHERE end IS NULL;";
  await this.query(queryString);
}

Should an AnswerEvent or a HangupEvent arrive for such a flagged call we unset their crashed flag in the event handler mentioned above.

Queries in Grafana

To explain how the database queries work we will walk through the process of creating the Average answer time query.

The only information we are interested in is the start and answered_at date.
We calculate the difference between them using AVG(TIME_TO_SEC(TIMEDIFF(answered_at, start))) as „time in seconds“.

Grafana requires us to provide the time_sec which is used to filter and group events by time. For that, grafana provides the $__timeGroup macro.
You can read more about Grafana’s macros on their documentation.

Our query now looks like this:

SELECT
  $__timeGroup(start, '$time_unit', 0) as time_sec,
  AVG(TIME_TO_SEC(TIMEDIFF(answered_at, start))) as "time in seconds"
FROM calls
GROUP BY time_sec

At this point, the query should be functional and you should be able to see the results in the chart.
However, we also want to enable filtering by teams and groups.

We configured Grafana to load the teams and groups saved in the database into the variables groups and teams respectively. The user can then freely select any group and/or team constellation in the filter bar. Suppose that the user checks the boxes Sales and Other for the groups.
We can concatenate these values to Sales|Other using ${groups:pipe}. That way we can check whether a group should be included using ${groups:pipe} REGEXP fieldName OR (${groups:pipe} REGEX „Other“).

First we need to join our groups, teams_numbers and teams like this:

LEFT JOIN groups on calls.group_extension=groups.extension
LEFT JOIN teams_numbers on calls.callee_number=teams_numbers.number or calls.caller_number=teams_numbers.number
LEFT JOIN teams on teams_numbers.team_id=teams.id

The final query then checks whether the group alias matches the the users group or team slecetion:

WHERE (('${groups:pipe}' REGEXP groups.alias) OR ('${groups:pipe}' REGEXP "Other" AND calls.group_extension IS NULL))
AND   (('${teams:pipe}'  REGEXP teams.name) OR ('${teams:pipe}' REGEXP "Other"))

The full query looks like this:

SELECT
  $__timeGroup(start, '$time_unit', 0) as time_sec,
  AVG(TIME_TO_SEC(TIMEDIFF(answered_at, start))) as "time in seconds"
FROM calls

LEFT JOIN groups on calls.group_extension=groups.extension
LEFT JOIN teams_numbers on calls.callee_number=teams_numbers.number or calls.caller_number=teams_numbers.number
LEFT JOIN teams on teams_numbers.team_id=teams.id

WHERE (('${groups:pipe}' REGEXP groups.alias) OR ('${groups:pipe}' REGEXP "Other" AND calls.group_extension IS NULL))
AND   (('${teams:pipe}'  REGEXP teams.name) OR ('${teams:pipe}' REGEXP "Other"))

GROUP BY time_sec

The other queries all look roughly like this one, feel free to take a look at them.

First overview of the Grafana dashboard

The entire visualization of the statistics is done with Grafana that you can access at http://localhost:3009 in your browser. Login in with the credentials which you have set in the chapter Run the Application. If you have accessed the Call Statistics dashboard once, you should be able to see it linked in the Recently viewed dashboards section on the landing page.

Otherwise you can access the dashboard by navigating in the left side menu to Dashboards (icon shows four squares) → Manage and there you will find the link in the General folder.

When you access your dashboard it might look similiar to the following screenshot:

Available Panels

In this chapter a brief description is provided for each panel in the Call Statistics dashboard.
The data shown in each panel is defined by the date range which is specified in the upper right corner.

Waiting in queue: This panel shows all the calls that have neither been answered yet nor been hung up.

Active calls: Active calls are shown in two seperate panels with given title. The left one displays the amount of calls that got answered and not hung up yet. The right one displays more details for every active call in a table. It includes the start time, direction and the owner number.

In- and outgoing pick-up ratio: In these pie charts the relation between answered and unanswered phone calls is displayed.
Respectively, the total count and the relative amount of (un-)answered calls is shown in the legend.

In- and outcoming calls graph: These graphs show the unanswered and answered in- and outbound phone calls.
The areas overlap each other and are not stacked.

Mean call answer time: The mean time which is needed to answer a call is depicted in this graph.

Longest waiting times: This panel will show those incoming calls which accumulate the longest waiting times until being picked up by an employee.

Average call duration: The average duration of all calls are visible here.
This includes outgoing and incoming calls.

Longest calls: This panel lists the starting time and duration of the twenty longest calls in descending order.

Total calls: The amount of total calls is visible in this panel.

Total voicemail calls: All voicemail calls are shown in this graph depending on the time.

Most calling numbers: This table shows the top twenty numbers calling your hotline with their respective call count in descending order.

Filter Options

The Grafana dashboard which is included in this project provides several filter options to manage the time ranges of the call data being visualized, both at the dashboard level and the panel level. You can freely combine filters from each of the following options:

Groups

The groups are directly imported from sipgate. You can filter for calls with participants from a specific group or a collection of groups.

Teams

Teams are predefined by yourself in the teams.json. Like with groups you can filter for calls with participants from a specific team or a collection of teams.

Time Unit

Depending on the time unit, panel data is summarized for minutes, hours, days or any other given time unit.

Timespan

With this option you can increase or decrease the date range to see more or less data. For example you can change the timespan from one day to seven days or even just an last hour.