Extracting and analyzing call data

All call activity on the Pexip platform is recorded in a database on the Pexip Management Node. You can view this information via the Administrator interface (Status > Conference History or Participant History) and you can also extract this call data and analyze it in other third-party tools.

This topic shows how to use the Management Node REST API to extract conference and participant history into CSV files on a daily basis, and then import and analyze those files using Excel Power Query and PowerBI (a Microsoft business analytics tool).

The following background information is available:

Note that the database that holds this information stores historic data for up to 10,000 conferences (including the participant history for all these conferences). If data is required past this number of records then the information needs to be taken from this database at regular intervals.

The following sections show to use a PowerShell script to extract and accumulate historic data, how to examine that extracted data in Excel, and how to set up PowerBI with the Pexip Management Node as the data source.

Using a PowerShell script to extract and accumulate historic data

This example PowerShell script extracts and accumulates data on a daily basis, but you can adapt it to look over the past week, month etc. The script works as follows:

  1. Logs in to the Management Node.
  2. Downloads the conference and participant history for calls that have occurred within exactly the previous 24 hours prior to running the script.
  3. Writes that day's history to 2 CSV files in the same directory from where the script is run. The files are named in the format: <date_time>_pexHistoryConf.csv and <date_time>_pexHistoryPart.csv
  4. Adds this same data to 2 other CSV files that accumulate each previous day's history to make 2 larger files containing a running history. These files are named pexHistoryConf.csv and pexHistoryPart.csv.

Copying and using the script

  1. Cut and paste the script below into PowerShell.
  2. Edit the script, changing the following variables as appropriate for your environment:

    • $mgr_host = the FQDN or IP address of your Management Node (excluding the "https://" prefix)
    • $user = the administrator username (typically 'admin')
    • $password = the administrator password

Note that you need to manually run the script at the same time each day, or schedule it to automatically run at the appropriate intervals.

# A simple Powershell script that downloads conference and participant history from the Pexip Management Node.
# It creates 2 new csv files in the directory that you run the script from each time it is run. The files contain the
# history of all conferences and their participants between when the script is run and the same time the day before.
# These files are called yyyy-mm-dd_hh-mm-ss_pexHistoryConf.csv and yyyy-mm-dd_hh-mm-ss_pexHistoryPart.csv to allow
# for sorting. It then takes the same data and appends it to data that already exists in the pexHistoryConf.csv and
# pexHistoryPart.csv files. Note that there are no checks in place to determine if there are duplicates.
 
# Get the current time and date:
$now = get-date
 
# Convert the current time to a sortable format (suits the Management Node):
$pexNow = get-date $now -Format s
 
# Number of days ago to start the report from:
$start = $now.AddDays(-1)
 
# Convert the start time to a sortable format (suits the Management Node):
$start = get-date $start -Format s
 
# Management Node IP or FQDN:
$mgr_host = "your.mgmtnode.com"
 
# Management Node credentials:
$user = 'admin'
$password = '<change_me>'
 
$mgr_part = "https://" + $mgr_host + "/api/admin/history/v1/participant/" + "?limit=5000" + "&end_time__gte=" + $start + "&end_time__lt=" + $pexNow
$mgr_conf = "https://" + $mgr_host + "/api/admin/history/v1/conference/" + "?limit=5000" + "&end_time__gte=" + $start + "&end_time__lt=" + $pexNow
 
$pwd = ConvertTo-SecureString $password -AsPlainText -Force
$cred = New-Object Management.Automation.PSCredential ($user, $pwd)
 
 
#### Ignore self-signed cert issues
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
 
$response_part = Invoke-RestMethod -Uri $mgr_part -Method Get -Credential $cred -ContentType 'application/json'
$response_conf = Invoke-RestMethod -Uri $mgr_conf -Method Get -Credential $cred -ContentType 'application/json'
 
$participants = $response_part.objects
$conferences = $response_conf.objects
 
#$Write-output $participants # optional to show output in the PS CLI
 
Write-Output $participants | Export-Csv -Path "$((Get-Date).ToString('yyyy-MM-dd_hh-mm-ss'))_pexHistoryPart.csv" -Delimiter "," -NoTypeInformation
Write-Output $participants | Export-csv -Append pexHistoryPart.csv -Delimiter "," -NoTypeInformation #write the csv file to the same dir as where this script is run from
 
Write-Output $conferences | Export-Csv -Path "$((Get-Date).ToString('yyyy-MM-dd_hh-mm-ss'))_pexHistoryConf.csv" -Delimiter "," -NoTypeInformation
Write-Output $conferences | Export-csv -Append pexHistoryConf.csv -Delimiter "," -NoTypeInformation #write the csv file to the same dir as where this script is run from

Data analysis in Excel

As the data in the pexHistoryConf.csv and pexHistoryPart.csv files is appended to each day, you can use the data analysis capabilities in Excel to examine this accumulated data. You can add the CSV files as a data source and have the pivot tables, reports and charts update when required.

To enable the analytics in Excel and link to the source CSV files:

  1. Download Power Query for Excel, if necessary (https://www.microsoft.com/en-us/download/details.aspx?id=39379). This is not necessary if you are using Excel 2016.
  2. Create a new blank document.
  3. Select Power Query > From File > From Text/CSV.

  4. Select the pexHistoryConf.csv file.
  5. Select Load.

  6. Repeat steps 3-5 with the pexHistoryPart.csv file in a new sheet.

To update the workbook data (workbook queries) with the latest CSV file contents:

  1. Open your workbook.
  2. Select Data > Refresh All.

To add reports and charts:

  1. Open your workbook.
  2. Add a new sheet then go to the Data menu, select Existing Connections and select the Conference History (pexHistoryConf) table.

  3. In the Import Data dialog, choose the view you would like to display, for example PivotChart, and select OK.

  4. You can then build your chart by selecting fields, filters, values and so on.
  5. Repeat steps 2-4 for the Participant History (pexHistoryPart) table.

Example conference and participant history charts

Conference participant count

Call types

Call distribution across participant locations

Call distribution across participant protocols

Using PowerBI with the Pexip Management Node as the data source

You can also analyze the data using PowerBI dashboards. This example shows how to set up a real time integration with PowerBI so that it can directly pull data from the Pexip Management Node.

  1. Install (if necessary) and launch Power BI.
  2. Select Get Data > Web.
  3. Enter the query URI, for example https://your.mgmtnode.com/api/admin/history/v1/conference/?limit=100 to retrieve details about the 100 most recent conferences, and select OK.

    Remember to change this example to specify the path to your Management Node. You can change the query to retrieve different resource details, and you can also adjust the limit parameter to change the number of records that are retrieved (see Retrieving, paginating, filtering and ordering resource details).

  4. Select Basic authentication and enter the administrator credentials for the Management Node, and select Connect.

  5. In the Query Editor window, select Into Table.

  6. Right-click on "List" and select Drill Down.

  7. Select Convert To Table.

  8. Click OK on the To Table dialogue box.

  9. Click on the column-expander button to reveal the list of columns and then select OK.

  10. Now close the query editor and apply the changes (select Yes).

  11. You can now build a dashboard within Power BI.

PowerBI dashboard example

Here is an example of the sort of dashboard you can create: