Technical Documentation
Search for in-depth articles on Synergy SKY tools and technologies.

  • There are no suggestions because the search field is empty.
 

Support > Guides > Advanced Configuration >
Access API Database for 3rd Party Data Visualization Tools

Access API Database for 3rd Party Data Visualization Tools

 

Introduction

The latest version of Synergy SKY Management Suite allows for pulling of tables from the meeting database into 3rd party tools. This way you can create meeting reports on your company's terms, even if you're not familiar with Grafana. Compatible with a number of external analytics packages and report writing platforms (ie. Tableau/MS Power BI).

Prerequisites

Deployment Completed The Synergy SKY platform & Management Suite has been deployed and running in a virtualized environment. If this requirement is not already fulfilled, please start here.
SSH client Download a suitable SSH client like PuTTY, OpenSSH, SolarPuTTY etc.
Synergy SKY Suite 30.01 30.01.1706 at a minimum

 

Step-by-Step Guide

Config DB for external access
  1. SSH into your Management Suite using a tool like those mentioned in the Prerequisites section

  2. Select 4 Advanced setup menu & hit Return

    mceclip0.png

  3. Select 1 Change database bootstrap & hit Return

    mceclip1.png


  4. The following page will display. For using the Database with 3rd party tools, you'll want to take note of the following lines:

    1. DatabaseHostname - This is the IP address of your Synergy SKY SUITE server

    2. ApiDatabaseName - This is the name of the DB you'll be adding to your 3rd party tool

    3. ApiDatabaseUsername - This is the username you'll use to connect to the DB

    4. ApiDatabasePassword - This is the password you'll use with the above username

    mceclip0.png

    Note: For this How-to, nothing else should be changed except the password [10]

  5. Select [10] and press Enter to change the DB password

  6. Change the Password to fit your requirements

  7. Select [v] and press Enter to verify the settings
    mceclip1.png

  8. Select [s] and press Enter to save and exit.
    Note: The password will encrypt after save & exit


Add DB source to your Data Visualization Tool

This step may vary depending on which tools you choose to use, but in general, the following steps/info applies. 
Note: The following example shown is for Grafana

  1. Add a PostgreSQL data source

  2. Name the connection

  3. Add the following information to connect:
    Host: <IP address of SUITE> (this may be different if you host your db externally)
    Database: synergyskydb_api

    User: externalapiuser
    Password: <as configured in the appliance in steps above>
    PostgreSQL Version: 11 (Not always necessary, but 11 is what we run)

    mceclip2.png


  4. Save the data source

  5. Done!

 

Relation of tables

Below are diagrams showing how some of the tables in the DB relate to and rely on each other. This should help in deciding what tables to show while building reports.

DatabaseRelation_Limited-Minimized_Diagrams_crop.png

 

 

 

Available columns

All highlighted columns are relational columns that can be used in a SQL Join.

 

mv_meetingparticipants

This table contains a single meeting's invitees and participants. Each being populated as a single row in this view. A meeting with 3 invitees (either persons in form of email, or room resources), will consist of 4 invitees total (one per invitee, and one for the organizer).

This will then result in 4 rows in the mv_meetingparticipants, all relating to same meeting ID.

Column Name Type Description
id text Table unique ID.
skynetlink text Globally unique ID for the object/row - often used in joining of rows.
coreresourceid text URI/Email for a meeting room.  This is only set when the meeting has a relation to a meeting room/resource. Otherwise it will be NULL
name text

Display name of the resource/invitee.

If there is no display name extracted from the invitation, this will be equal to address field.

isbooked bool  
address text Address of the invitee/resource as found in the booking.
callstartat timestamptz UTC time when first call was detected. (SIP/Teams/etc.) - This requires call data that matches to the invitee to exist in the system *MCU?*.
callendat timestamptz UTC time when the call was ended (In the case of multiple connect/disconnects for the same invitee, the latest occurrence of call stop will be displayed).
firstseenat timestamptz UTC time when the earliest occurrence of call and/or presence data was recorded. 
lastseenat timestamptz UTC time when the latest occurrence of call and/or presence data was recorded.
isrecurring bool Is this meeting part of an recurring series?
iscallmatched bool Is this call matched with call data (CDR)?
meetingstartat timestamptz UTC time the recorded booked meeting start time.
meetingendat timestamptz UTC time the recorded booked meeting end time.
presencestartat timestamptz UTC time when presence was first detected, requires supported sensor configuration.
presenceendat timestamptz UTC time when presence last was detected, requires supported sensor configuration.
issensormatched bool Is sensordata matched.
meetingduration float8 Meeting time recorded in seconds. meetingendat - meetingstartat
meetingquality int4 Not to be used.
meetingraphlink text Relation ID to unique meeting ID, can be used as a grouping key.
sensordataresourcelink text Relation ID to sensor data row.
participantgraphlinks _text (text array) Relation to call data row.
foundtypes _text (text array) Array of type of data found during compile of meeting (SENSOR,RESOURCE,EMAIL,CALL)
isnoshow bool

Was this participant recorded as a noshow? This requires supported sensors.

noshowat timestamptz UTC time when no-show was flagged (otherwise NULL).
isrejected bool Was the meeting's unbooking negated?
rejectedat timestamptz UTC time when the the meeting was marked as "not to be unbooked" (isrejected).
isunbooked bool Was this meeting unbooked due to noshow? This requires supported sensors and an unbooking policy.
unbookedat timestamptz UTC time when the meeting was unbooked.
isorganizernotified bool Was a email notification sent to the organizer?
organizernotifiedat timestamptz UTC time when the above email was sent.
isabortedbypresence bool UTC time when the the meeting was marked as "not to be unbooked" by sensor data.  
abortedbypresenceat timestamptz UTC time when the unbooking process was stopped by sensor data. This requires supported sensors and an unbooking policy.
iscandidateforrelease bool  
candidateforreleaseat timestamptz UTC time

 

mv_meetinggraphs
Column Name Type Description

id

text  

skynetlink

text Globally unique ID for the object/row - often used in joining of rows.
meetinggraphid text  
corecalendarmeetingid text  
recurringmeetinggroupid text  
basicmeetingtype text  
basicmeetingtypematchingcategory text  
organizername text  
organizeremail text  
bookedattendees int4  
matchedattendees int4  
subject text  
minstartat timestamptz UTC time
maxendat timestamptz UTC time
mincallstartat timestamptz UTC time
maxcallendat timestamptz UTC time
minfirstseenat timestamptz UTC time
maxlastseenat timestamptz UTC time
minpresencestartat timestamptz UTC time
maxpresenceendat timestamptz UTC time
minunbookstartat timestamptz UTC time
maxunbookendat timestamptz UTC time
effectivestartat timestamptz UTC time
effectiveendat timestamptz UTC time
founduris _text (text array)  
foundmeetingaddresses _text (text array)  
callgraphlinks _text (text array)  
meetingduration float8  

 

mv_participantcalldata

Call related data, one participant in call for each row. 

 Column Name  Type  Description
connectedat timestamptz UTC time
disconnectedat timestamptz UTC time
modifiedat timestamptz UTC time
quarter text not in use
week int4 not in use
weekdayisoindex int4 not in use
weekdayindex int4 not in use
weekday text not in use
id text Globally unique ID for the object/row - often used in joining of rows.
uri text  
originaluri text  
durationseconds int4  
effectivedurationseconds int4  
ishost text  
callgraphlink text Relation ID to unique conference ID, can be used as a grouping key.
primarytargeturi text  
callrecordlink text  
videorxpkgloss float8  
videotxpkgloss float8  
audiorxpkgloss
float8  
audiotxpkgloss
float8  
videorxjitter
numeric  
videotxjitter
numeric  
audiorxjitter
numeric  
audiotxjitter
numeric  
hasaudio bool Did this participant have any audio during the call?
hasvideo bool Did this participant have any video during the call?
haspresentation bool Did this participant present anything during the call?
primaryprotocol text  
vendors text  
conferenceprotocols _text (text array)  
conferencetypes _text (text array)  
networkpeers _text (text array)  
alltargeturisarray _text (text array)  
alltargeturis text  



Examples

The query behind "Meeting history" report.

Lists meetings with some aggregated data per meeting.

SELECT  mg.meetinggraphid,
mg.subject,
mg.basicmeetingtype,
mg.organizername,
mg.organizeremail,
array_to_string(foundmeetingaddresses, ',') as meetinguris,
mg.minstartat,
mg.meetingduration,
SUM(CASE WHEN 'EMAIL' = ANY(mp.foundtypes) THEN 1 ELSE 0 END) AS invitees,
SUM(CASE WHEN 'RESOURCE' = ANY(mp.foundtypes) THEN 1 ELSE 0 END) AS meetingroomparticipants,
SUM(CASE WHEN 'CALL' = ANY(mp.foundtypes) THEN 1 ELSE 0 END) AS videoparticipants,
CASE WHEN mg.mincallstartat IS NOT NULL THEN 1 ELSE 0 END AS videoused
FROM mv_meetinggraphs mg
JOIN mv_meetingparticipants mp ON mp.meetinggraphlink = mg.meetinggraphid
JOIN(
SELECT DISTINCT meetinggraphlink
FROM mv_meetingparticipants
WHERE meetingstartat BETWEEN '2021-05-17T12:27:22.156Z' AND '2021-06-16T12:27:22.156Z'
AND (LOWER(address) LIKE LOWER('%%') OR LOWER(name) LIKE LOWER('%%'))

) a ON a.meetinggraphlink = mg.meetinggraphid
WHERE mg.minstartat BETWEEN '2021-05-17T12:27:22.156Z' AND '2021-06-16T12:27:22.156Z'
AND LOWER(mg.subject) LIKE LOWER('%%')
AND LOWER(mg.organizername) LIKE LOWER('%%')
AND LOWER(array_to_string(foundmeetingaddresses, ',')) LIKE LOWER('%%')
GROUP BY
mg.meetinggraphid,
mg.subject,
mg.basicmeetingtype,
mg.organizeremail,
mg.organizername,
array_to_string(foundmeetingaddresses, ','),
mg.minstartat,
mg.meetingduration,
CASE WHEN mg.mincallstartat IS NOT NULL THEN 1 ELSE 0 END
ORDER BY
mg.minstartat desc



Number of meetings per month

Shows the month-by-month number of meetings happening.  Using a generated series to also include months with zero data.

SELECT  to_char(timex, 'Month'),
NOW() as time,
COALESCE(m.count, 0) as count
FROM generate_series(date_trunc('month', CAST('2000-01-01T00:00:00.000Z' AS TIMESTAMP)), Now(), '1 month') timex
LEFT JOIN
(
SELECT date_trunc('month', mg.minstartat) AS month,
COUNT(*) as count
FROM mv_meetinggraphs mg
WHERE mg.minstartat BETWEEN '2020-06-16T12:30:52.286Z' AND '2021-06-16T12:30:52.286Z'
GROUP BY
date_trunc('month', mg.minstartat)
) m on m.month = timex
ORDER BY timex

 

Still in need of help?

Create a ticket