Pexip Reporting


Background
 
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 = "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

$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
Analytics
 
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: https://www.microsoft.com/en-au/download/details.aspx?id=39379
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_history
Open file

 

data_imported
Data imported

Click Close & Load:

data_imported2
Conference data imported

Repeat with the pexHistoryPart.csv in a new sheet:

 

pexpart
Participant data imported

To update the data, click refresh:

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:

pexpart_report
Add participant history table

Choose the view you would like to display:

pivot
Adding a chart

Example Charts:

 

Conference Participant Count
Conference Participant Count
protocol_mix
Protocol Mix

 

service_type
Call Type
service_tag
Service Tag mix
Sample Locations Report
Sample Locations Mix

 

Advertisements

3 thoughts on “Pexip Reporting

  1. Great post, works like a charm:)
    Thanks!

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

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

w

Connecting to %s