Google Workspace Analytics Installation

Prerequisites

Minimum Version: Requires SysTrack 10.1 or later.

Notes

After this DEX Pack is installed, it may take up to 24 hours for data to appear.
If you update or reinstall this DEX Pack, you must reassign any Views created below to the SF_Google Workspace Analytics Role.

Installation Steps

To install this DEX Pack, complete the following steps:

Import the Kit

There are two ways to import the DEX Pack to SysTrack:

Import the pack directly from the Kits page in SysTrack. See the Import Online Kits guide for more information.
Import a local file downloaded from the DEX and Kit Downloads page. Click the Download Kit link for the pack and follow the Import Local Kits guide to finish the import.

Create the Google Workspace View

This DEX Pack requires you to make a View for the relevant SF_Google Workspace Analytics Role.

To create the new View:

Navigate to Configure > Views.
Click the padlock icon in the upper-right to enable editing.
Click the plus icon to add a new View.
Fill in the Settings as follows:
1. View Name: SF_GOOGLEWORKSPACE
2. Existing Category: General
3. Expires in: 30 Days
4. When Expired: Append Data
5. Do NOT check When overdue by 1 day (s).
6. Set the Refresh drop-downs to Daily, Inside, 24x7, and Every Day.
7. Copy the SF_GOOGLEWORKSPACE Query, and paste it into the box under SQL Selection > Generic.
8. Click Test SQL. A Success message should appear at the top of the screen. If the test is not successful, the query may have been copied incorrectly.
Click Create View at the top-right.

SF_GOOGLEWORKSPACE Query

SELECT
	T0.WGUID,
	GETUTCDATE() AS VWTIME,
	DATEADD(D,DATEDIFF(D,0,T0.FIRST_USE),0) AS DOTY,
	S0.STRVALUE AS UNAME,
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/spreadsheets%' THEN 'spreadsheets' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/presentation%' THEN 'presentation' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/forms%' THEN 'forms' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/document%' THEN 'document' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://mail.google.com/mail%' THEN 'mail' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://mail.google.com/chat%' THEN 'chat' ELSE
	SUBSTRING(S1.STRVALUE,9,CHARINDEX('.',S1.STRVALUE)-9) END END END END END END AS WEBAPP,
	COUNT(*) AS NUM_VISITS,
	SUM(T0.TIMEONPAGE) AS TIME_ON_PAGE,
	SUM(T0.FOCUSTIMEONPAGE) AS FOCUS_TIME_ON_PAGE,
	AVG(T0.PAGELOADTIME) AS AVG_PAGE_LOAD_TIME,
	AVG(LOADEND - NAVSTART) / 1000.0 AS AVG_CLICKTORENDERTIME,
    AVG(RESPONSEEND - REQUESTSTART) / 1000.0 AS AVG_LATENCYTIME,
    AVG(LOADEND - DOMLOADING)/ 1000.0 AS AVG_PROCESSINGTIME
FROM (SAWEB AS T0
INNER JOIN SASTRUSER AS S0 ON T0.ACCOUNT_ID = S0.STRINGID)
INNER JOIN SAWEBSTR AS S1 ON T0.FULLURL = S1.STRINGID
WHERE 
	(
		S1.STRVALUE LIKE 'https://docs.google.com/spreadsheets%'
		OR S1.STRVALUE LIKE 'https://docs.google.com/presentation%'
		OR S1.STRVALUE LIKE 'https://docs.google.com/forms%'
		OR S1.STRVALUE LIKE 'https://docs.google.com/document%'
		OR S1.STRVALUE LIKE 'https://mail.google.com/mail%'
		OR S1.STRVALUE LIKE 'https://mail.google.com/chat%'
		OR S1.STRVALUE LIKE 'https://hangouts.google.com%'
		OR S1.STRVALUE LIKE 'https://jamboard.google.com%'
		OR S1.STRVALUE LIKE 'https://classroom.google.com%'
		OR S1.STRVALUE LIKE 'https://meet.google.com%'
		OR S1.STRVALUE LIKE 'https://contacts.google.com%'
		OR S1.STRVALUE LIKE 'https://drive.google.com%'
		OR S1.STRVALUE LIKE 'https://calendar.google.com%'
		OR S1.STRVALUE LIKE 'https://remotedesktop.google.com%'
		OR S1.STRVALUE LIKE 'https://cloud.google.com%'
		OR S1.STRVALUE LIKE 'https://admin.google.com%'
		OR S1.STRVALUE LIKE 'https://script.google.com%'
		OR S1.STRVALUE LIKE 'https://messages.google.com%'
		OR S1.STRVALUE LIKE 'https://keep.google.com%'
		OR S1.STRVALUE LIKE 'https://accounts.google.com%'
		OR S1.STRVALUE LIKE 'https://support.google.com%'
		OR S1.STRVALUE LIKE 'https://currents.google.com%'
		OR S1.STRVALUE LIKE 'https://sites.google.com%'
		OR S1.STRVALUE LIKE 'https://console.cloud.google.com%'
		OR S1.STRVALUE LIKE 'https://stream.meet.google.com%'
	)
	AND DATEDIFF(D, T0.FIRST_USE, <LASTREFRESHTIME>) <= 0
	AND DATEDIFF(D, T0.FIRST_USE, GETUTCDATE()) > 0
GROUP BY
	T0.WGUID,
	DATEADD(D,DATEDIFF(D,0,T0.FIRST_USE),0),
	S0.STRVALUE,
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/spreadsheets%' THEN 'spreadsheets' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/presentation%' THEN 'presentation' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/forms%' THEN 'forms' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://docs.google.com/document%' THEN 'document' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://mail.google.com/mail%' THEN 'mail' ELSE
	CASE WHEN S1.STRVALUE LIKE 'https://mail.google.com/chat%' THEN 'chat' ELSE
	SUBSTRING(S1.STRVALUE,9,CHARINDEX('.',S1.STRVALUE)-9) END END END END END END

Assign the Google Workspace View to the Google Workspace Analytics Role

To assign the new View:

Navigate to Configure > Roles.
Use the drop-down at the top to select the SF_Google Workspace Analytics Role.
Click Views, then check the box next to SF_GOOGLEWORKSPACE.
Click Save Changes at the top-right.

Assign the Google Workspace Role to Relevant Configurations

The Google Workspace DEX Pack requires you to assign the SF_Google Workspace Analytics Role to the relevant Configurations:

Navigate to Configure > Configurations.
Click the padlock icon in the upper-right to enable editing.
Use the drop-down at the top to select a relevant Configuration, or create a new Configuration.
Assign the SF_Google Workspace Analytics Role to the Configuration by dragging it from Available Roles to Assigned Roles.
Click Save Changes at the top-right.
Repeat this process for any other relevant Configurations.