Google Workspace Analytics Installation

Minimum Version

This DEX Pack requires SysTrack version 10.1 or higher.

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.

Import Kit

To use this DEX Pack, the corresponding Kit must be imported to SysTrack.

If you have already imported the DEX Pack directly from the Kits page, the Import Kit step is complete.

If you are viewing this DEX Pack in the Customer Gateway, follow these steps to import this DEX Pack Kit:

  1. On the DEX Pack page, download the DEX Pack ZIP file

  2. In SysTrack, open Kits

  3. Under Local, click Select Kit File

  4. Select the DEX Pack ZIP File

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:

  1. Navigate to Configure > Views

  2. Click the padlock icon in the upper-right to enable editing

  3. Click the plus icon to add a new View

  4. 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 this SQL query, and paste it into the box under SQL Selection > Generic.

    8. Click Test SQL. A Test Success message should appear. If the test is not successful, the query may have been copied incorrectly

  5. Click Create View at the top-right

To assign the new View:

  1. Navigate to Configure > Roles

  2. Use the drop-down at the top to select the SF_Google Workspace Analytics Role

  3. Click Views, then check the box next to SF_GOOGLEWORKSPACE

  4. Click Save Changes at the top-right

Copy SF_GOOGLEWORKSPACE_VU_V4

Copy 
SELECT
    T0.WGUID,
    GETUTCDATE() AS VWTIME,
    DATEADD(D,DATEDIFF(D,0,T0.FIRST_USE),0) AS DOTY,
    S0.STRVALUE AS UNAME,
    T1.WNAME 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
INNER JOIN SAUSERDEFINV T1 ON S1.STRVALUE LIKE T1.GENSTRING + '%'
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%'
        OR S1.STRVALUE IN (SELECT DISTINCT GENSTRING + '%' FROM SAUSERDEFINV) 

    )
    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,
    T1.WNAME

Google Workspace Role

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

  1. Navigate to Configure > Configurations

  2. Click the padlock icon in the upper-right to enable editing

  3. Use the drop-down at the top to select a relevant Configuration, or create a new Configuration

  4. Assign the SF_Google Workspace Analytics Role to the Configuration by dragging it from Available Roles to Assigned Roles

  5. Click Save Changes at the top-right

  6. Repeat this process for any other relevant Configurations.