Pexip Reporting

All call activity on the Pexip platform in a database that exists on the Pexip Management Node. This activity is recorded in two tables that detail information pertinent to conferences and the participants connecting to the conferences. It is possible to list this infomantion by browsing to the Management Node and selecting Status > Conference History or Participant History  
More detail:
The database that holds this information stores data for up to 5000 conferences (including the Participant history for all these conferences) in the past. If data is required past this number of records then the information needs to be taken from this database at regular intervals. The Pexip management node offers an API to allow this to done.
The API allows for REST calls to be made for specific conferences or participants or all or subset of this information to be called. More detail:
In the following sections, there are two examples on how this can be achieved. These are examples using standard Microsoft products. The same, or arguably more, could be achieved by a competent developer (e.g.. python, nodejs, javascript, etc).
PowerShell script
This method uses a simple PoweShell script to perform the following functions on a daily basis:
     This could be tweaked to occur daily, weekly or monthly
  1. Login to the Management node
  2. Download the Participant history for calls that have occurred within exactly one day prior to the running of this script
  3. Write the history to a file in the path that the script is run that contains the day’s history and name it: <current_date/time>_pexHistory.csv
  4. Add this same data to a CSV file that has the previous day’s history already added to make one big file containing a running history called: pexHistory.csv
 Powershell script:
# Powershell script that downloads the history from the Pexip Management node
# It creates a new csv file in the directory that you run the script from each time it is run. The first file is the history of all conferences
# between when the script is run and the same time the day before. This file is writted as yyyy-MM-dd_hh-mm-ss_pexHistory.csv to allow for sorting.
# The script also takes this same data and appends it to data that already exists in the pexHistory.csv file. Note that there are no checks in place
# to determine if there are duplicates. This is a very simple example.

# Get the time and dat now:
$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 = ""

# Management node credentials:
$user = 'admin'
$password = ''

$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
As the data in the pexHistory.csv file is appended to each day, it is possible to use the inherent data analysis capabilities in Excel. It is possible to add the csv file as a data source and have the pivot tables, reports and charts update when required.
To enable this analytics in Excel:
Download Power Query for Excel (for the version you are running). e.g. Excel 2013:
note: not yet available for Mac
  1. Create a new blank document
  2. Click on Power Query > From File > From CSV
  3. Select pexHistoryConf.csvOpen file
Open file


Data imported

Click Close & Load:

Conference data imported

Repeat with the pexHistoryPart.csv in a new sheet:


Participant data imported

To update the data, click refresh:


Add reports and charts

Add a new sheet then go to the DATA menu and click Existing Connections and select the Participant History table:

Add participant history table

Choose the view you would like to display:

Adding a chart

Example Charts:


Conference Participant Count
Conference Participant Count
Protocol Mix


Call Type
Service Tag mix
Sample Locations Report
Sample Locations Mix



3 thoughts on “Pexip Reporting

  1. Great post, works like a charm:)

    Oh and if you use Excel2016 you dont need to download Power Query 😉


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s