Introduction
The latest version of Synergy SKY 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 & 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 2.1 | 30.01.1706 at a minimum |
Step by Step Guide
Config DB for external access
- SSH into your SUITE using a tool like those mentioned in the Prerequisites section
- Select 4 Advanced setup menu & hit Return
- Select 1 Change database bootstrap & hit Return
- 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
Note: For this How-to, nothing else should be changed except the password [10] - Select [10] and press Enter to change the DB password
- Change the Password to fit your requirements
- Select [v] and press Enter to verify the settings
- 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
- Add a PostgreSQL data source
- Name the connection
- 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) - Save the data source
- 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.
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 month by month number of meetings happening. Using a generated series to also include month 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