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 API (Add-on) is available for purchase with all paid packages.
Contents
Requirements
- ActivTrak paid package and the ActivConnect API (Add-on)
- ActivConnect username and password will be in the format of [account_number]@activtrak.us. Your account number is in the Account Information menu in ActivTrak; access it via the circle icon in the top right corner.
Note: This template is designed to work with all ActivTrak paid packages; however, the data available in each report will vary by package type. See details in the ActivConnect Data Glossary.
Accessing Your Data
- Navigate to https://console.cloud.google.com
- Enter your [account_number]@activtrak.us email address
- 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.
-
Billing Project: If you are accessing ActivConnect with the provided ActivTrak account, the billing project should be "us-activtrak-ac-prod"
- Click the project picker at the top of BigQuery Studio to select a project
- Select the "activtrak.us" organization from the dropdown in the top left of the pop-up window
- Select the "All" filter and search for "us-activtrak-ac-prod"
- Select "us-activtrak-ac-prod" as your billing project
- Dataset: This will be your ActivTrak account number.
- Views: These views contain your ActivTrak data. Please refer to the ActivConnect Data Glossary for detailed information about each view.
- 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.
- Query Editor: This is where you will enter your SQL query
- Validation: Look for any errors in your query; if everything is correct, you will see the Green Check before running the query
- Run: Click Run to execute the query
- 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
List Apps or Sites pending Classification
Important: Replace 'xxxxxx' with your ActivTrak account number
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 GROUPBY application_or_site, website, application, executable, category, productivity ORDERBY time_in_app_hr DESC
Working Hours Report
Important: Replace 'xxxxxx' with your ActivTrak account number
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