ActivTrak SQL Access

Unlock the power of ActivConnect and access your ActivTrak data through a native SQL interface. ActivConnect (Powered by BigQuery) leverages the native Google BigQuery user interface to simplify access to your data. You’ll be able to access and analyze millions of rows of data bringing the power of SQL tools you are already familiar with. 

ActivConnect is available as an add-on to customers on a paid subscription plan. Learn more about ActivConnect here.

Requirements

• ActivTrak paid subscription plan and the ActivTrak ActivConnect Add-on. Learn more here.
• ActivConnect user name and password will be in the format of [account_number]@activtrak.us]

Note: While all tables are available to all ActivConnect subscription plans, the data available within each table will vary based on your specific ActivTrak subscription plan type. See details in the ActivConnect Data Glossary.

Accessing Your Data

1. Navigate to https://console.cloud.google.com 
2. Enter your [accountnumber]@activtrak.us email address
3. Enter your password (you may be prompted to change the password if this is the first time accessing your ActivConnect account)

Search for BigQuery in the search bar and select BigQuery to open the Google BigQuery interface:

Basic BigQuery Navigation

Below are the main objects you should be familiar with. Please refer to Google BigQuery documentation for a complete list of capabilities. 

1. Billing Project: If you are accessing ActivConnect with the provided ActivTrak account the billing project should be us-activtrak-ac-prod
2. Dataset: This will be your ActivTrak account ID. i.e. 123456
3. Views: These views contain your ActivTrak data. Please refer to the ActivConnect Data Glossary for detailed information about each view. 
4. Query Function: This will open the SQL Query editor

Navigation Tabs: You can organize your work in multiple tabs when working with multiple queries

The Query editor will open once you select the desired ActivConnect table and click the Query Function. 

Below are the main components of the query editor.

1. Query Editor: This is where you will enter your SQL query 
2. Validation: Look for any errors in your query, if everything is correct you will see the Green Check before running the query
3. Run: Click Run to execute the query
4. Query Results: The output of the query will be displayed here

Save Results: Alternatively, if you wish to export the results you can select the multiple export options from this menu:

Sample Queries

1. List Apps or Sites pending Classification

Replace xxxxxx for your ActivTrak account ID

------------------ Query Start --------------------

SELECT

COUNT(DISTINCT(user_id)) as user_count,

SUM(duration_seconds) AS time_in_app_hr,

application_or_site,

website,

application,

executable,

category,

productivity

FROM

`us-activtrak-ac-prod.xxxxxx.daily_application_summary`

WHERE

productivity ='UNDEFINED' or category is null

GROUP BY

application_or_site,

website,

application,

executable,

category,

productivity

ORDER BY

time_in_app_hr DESC

------------------ Query End --------------------

2. Working Hours Report

Replace xxxxxx for your ActivTrak account ID

------------------ Query Start --------------------

SELECT

 local_date,

 day_type,

 user_name,

 utilization_level,

 TIME(first_activity_datetime)AS first_activity,

 TIME(last_activity_datetime) AS last_activity,

 SUM(productive_active_duration_seconds+productive_passive_duration_seconds)/3600 AS productive_hours,

 SUM(unproductive_active_duration_seconds+ unproductive_passive_duration_seconds)/3600 AS unproductive_hours,

 SUM(total_duration_seconds)/3600 AS total_hours

FROM

 `us-activtrak-ac-prod.xxxxxx.daily_user_summary`

GROUP BY

 1,

 2,

 3,

 4,

 5,

 6

ORDER BY

 local_date DESC

------------------ Query End --------------------

Need additional assistance? Please reach out to ActivTrak Support.