Find it...

    Extracting Call Data from Pexip Infinity

    Follow

    It is possible to extract the data off the management node so you can analyse that data in another format such as Microsoft Excel or PowerBI.

    More detail:
     
    The database that holds this information stores data for up to 10.000 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

    To do this, you simply run a PowerShell script to get this data.  Example below, just cut and paste into PowerShell.  You need to fill in the variables of the follow

    • mgr_host = your.mgrmtnode.com or IP
    • username = admin/whatever user
    • password = whatever this is
    # 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-us/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.csv

     

    Click Close & Load:

     

    Repeat with the pexHistoryPart.csv in a new sheet:

    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:

    Choose the view you would like to display:

    Example Charts:

    Conference Participant Count

    Protocol Mix

    Call Type

    Service Tag Mix

    Sample Location Mix

     

    Was this article helpful?
    0 out of 0 found this helpful

    Comments