Unlock the power of ActivConnect and access your ActivTrak data without requiring advanced SQL or BI knowledge. ActivConnect (Powered by BigQuery) leverages the native integration between Google BigQuery and Tableau to simplify access to your data.
You’ll be able to access, analyze and visualize millions of rows of data in a simple way, allowing you to leverage the tools and techniques you are already familiar with, such as formulas, charts and pivot tables, while maintaining the data current through scheduled refresh options.
ActivConnect is available as an add-on for customers on paid plans. Learn more about ActivConnect here.
Contents
- Setup requirements
- Validate ActivConnect credentials
- Import your data into Tableau desktop application
- Learn more
Setup requirements
Before you begin, ensure you have:
- ActivTrak paid plan and the ActivTrak ActivConnect API (Add-on). Learn more here.
- ActivConnect user name (in the format of [account_number]@activtrak.us]) and password. Your account number is in the Account Information menu in ActivTrak; access it via the circle icon in the top right corner.
- An active Tableau license
Note: This template is designed to work with all ActivTrak subscription plans; however, the data available for each report will vary depending on your specific subscription plan type. Reports containing the Premium or Professional label in the report title indicate that the data required for those reports is only available to license tiers with the corresponding label.
Validate ActivConnect credentials
Note: This step is required for new customers to validate access to ActivConnect
- Navigate to Google Cloud Console
- Log in with your ActivConnect username ([accountnumber]@activtrak.us) and password
Import your data into the Tableau desktop application
- Within the ActivTrak app, navigate to APIs & Integrations > ActivConnect & BI
- Locate the Potential False Activity card and click the download icon next to Tableau
- From the zipfile, open the “ActivTrak for Potential False Activity Analysis.twbx” file
- You will be prompted to authenticate using a Google Cloud account. Use the same credentials from Step 1. Click 'Allow to Grant Access to the ActivConnect data from Tableau'.
- Close the browser window when you are done, and then switch back to Tableau Desktop
- A "Dashboard Unavailable" message will appear, indicating a problem connecting to the data source. This occurs because Tableau does not allow you to change the pre-built Data Source Connection before attempting to log in. Click Edit Connection inside the error message.
- Select the parameters relevant to your account
- See the image below for examples of where to find this information
- Click on “New Custom SQL” in the bottom left, beneath the tables.
-
Copy and paste the following code.
Important: to replace <@accountid> with your 6-digit account number and rename the custom SQL view with the name "Potential False Activities"
WITH filtered_logs AS (
SELECT
logs.*,
ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY local_datetime) AS rn
FROM
`us-activtrak-ac-prod.<@accountid>.events` AS logs
WHERE
local_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)
),
target_entries AS (
SELECT
user_name,
rn
FROM
filtered_logs
WHERE
application_or_site LIKE '%artificial input%'
),
all_entries AS (
SELECT
fl.*,
CASE
WHEN fl.application_or_site LIKE '%artificial input apps%' THEN 'Software'
ELSE 'Hardware/Pattern'
END AS jiggler_type,
IF(
INSTR(fl.application_or_site, 'Possible causes:') > 0,
SUBSTR(fl.application_or_site, 1, INSTR(fl.application_or_site, 'Possible causes:') - 1),
fl.application_or_site
) AS truncated_application
FROM
filtered_logs fl
JOIN
target_entries te
ON
fl.user_name = te.user_name
AND fl.rn = te.rn
)
SELECT
* EXCEPT(rn, application),
truncated_application AS application
FROM
all_entries
ORDER BY local_datetime
- Click and drag the premium_user_groups source from the Table list on the left and connect it to the Potential False Activities dataset.
- Select User Name from Potential False Activities and User Name from premium_user_groups when prompted to edit the relationship.
-
Click on Custom SQL Query again, and paste in the following code.
Important: Replace <@accountid> with your 6-digit account number and rename the custom SQL view with the name "Extended Events"
SELECT
user_name,
application_or_site,
application,
computer_name,
executable,
local_date,
local_datetime,
CASE WHEN EXTRACT(DAYOFWEEK FROM local_date) = 0 or EXTRACT(DAYOFWEEK FROM local_date) = 6 THEN 'Weekend'
ELSE 'Weekday'
END AS day_type,
titlebar,
duration_sec
FROM
`us-activtrak-ac-prod.<@accountid>.events`
WHERE
duration_sec > 1800
AND application_or_site NOT LIKE '%Remote Desktop%'
AND application_or_site NOT LIKE '%CDViewer%'
AND application_or_site NOT LIKE '%workspaces%'
AND application_or_site NOT LIKE 'LockApp.exe'
AND application_or_site NOT LIKE '%Visual Studio%'
AND application_or_site NOT LIKE '%MySQL%'
AND application_or_site IS NOT NULL
AND user_name NOT LIKE '%autopilot%'
- Drag Extended Events to connect to premium_user_groups. Select User Name from Extended Events and User Name from premium_user_groups when prompted to edit the relationship.
- Your data source map should look like the following:
- Select the Connection on the top right as either live or extract. We recommend using “extract” to save the data locally for improved performance.
- Sorting on a few visuals might have been lost. You can revisit those visuals and set the sort type accordingly.