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

Step by Step Guide

Config DB for external access

1. SSH into your SUITE using a tool like those mentioned in the Prerequisites section
2. Select 4 Advanced setup menu & hit Return
   
   
   
3. Select 1 Change database bootstrap & hit Return
   
   
   
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
   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
   
   
   
   
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)
   
   
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.

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.

mv_meetinggraphs

mv_participantcalldata

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

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