ActivTrak for MS Teams Setup Guide
ActivTrak Product Team
ActivTrak for MS Teams
Setup Guide
Draft v. 1.0
Step by step guide on how to set-up an ActivTrak Dashboard for MS Teams through Power Bl
Table of Contents
|
Step No. |
Topic |
Page |
|
1 |
Requirements |
2 |
|
2 |
Configuration for new ActivConnect Customers |
2 |
|
3 |
Configuration for existing ActivConnect Customers |
7 |
|
4 |
Adding template to Microsoft Teams |
9 |
|
5 |
Data Dictionary |
15 |
Requirements
Before proceeding with the steps below, please ensure that any user who publishes and those who intend to interact with any of the dashboards in MS Teams have a valid license for Power BI and MS Teams. Specifically, you need to meet the following requirements:
- Users have an ActivTrak Advanced license and the ActivTrak ActivConnect add-on.
- All users in your MS Teams group or chat need to have access to the workspace or the published report (pbix file).
- Users have a Power BI Pro license, or the report is contained in a Power BI Premium capacity (EM or P SKU) with a Power BI license.
- Users have signed in to the Power BI service to activate their Power BI license.
- Users meet the requirements to use the Power BI tab in Microsoft Teams.
Microsoft offers different plans when it comes to MS teams and Power BI tools.
- The first option is the subscription to Microsoft 365 E5 plan, which includes licenses for both applications. https://www.microsoft.com/en-us/microsoft-365/compare-microsoft-365-enterprise-plans
- The second option is that if the user has Microsoft 365 E3, Microsoft 365 F1, or any of Microsoft’s 365 business plan which has MS teams but not Power BI, it will then require to purchase an individual license of Power BI pro per user. https://powerbi.microsoft.com/en-us/pricing/
Configuration for new ActivConnect customers
Prerequisite: You will need the Google Cloud account you provided to ActivTrak support to access your ActivTrak ActivConnect instance.
Follow the steps below to configure ActivTrak’s ActivConnect Power BI Analysis Template using the Power BI desktop app. The template should work with most of the PowerBI versions but if it doesn't then update to the latest version and try again.
Connect to Google big query:
- Go to the URL provided by ActivTrak ActivConnect set up team
- Enter your [accountnumber]@activtrak.us email address
- Enter your temporary password [you will be prompted to change it].
Default View
Importing your data in Power BI desktop application:
- Once you have completed the above steps on your google cloud console, open the ActivTrak Power BI template PBIT file you have downloaded from the ActivConnect page.
- As soon as you open the PBIT file on Power BI desktop, you will be prompted with the following screen to enter parameters data relevant to your account. These can even be modified later if you go with the default values now The values of top 4 parameters are relevant to the project that you just received from the ActivConnect set
For other parameters:
- Timezone: Enter your relevant UTC timezone. (Number only. Eg: -6 is ours)
- Daylight Savings: This is ‘False’ or ‘True’ depending on if your location observes daylight saving time.
- Focused Time Apps: This is open to how you want to define it. Default value ‘2’
- Focused Time Screens: Open to your judgment. Default value ‘4’
- Inactive Agent Last Activity Threshold (Hours): Default value ‘72’
Final Step: After you enter parameters and click load depending on if you are connected to a Google Cloud account in Power Bl desktop you would be required to sign in. Once you sign in your idea would load in the template that we built and from that, you can slice and dice the report as per your requirement or publish to Power Bl service and share it with your team.
Note: You can visit https://support.activtrak.com/hc/en-us/articles/360042146172-ActivTrak-for-Power-BI-Setup-Guide For additional information on how to change the default values, schedule a refresh of data, etc.
Configuration for existing ActivConnect customers
If you are an existing ActivConnect customer you may already have a Google Cloud account that you created or you may have one provisioned by ActivTrak. If it is provisioned by ActivTrak, your id may look like accountnumber@activtrak.us
The ActivTrak for MS Teams template uses your existing AT groups configured in the app. To use these groups, you may have to configure the view for usergroups table if it's not already configured for you. To create the view in BigQuery, follow the steps below:
- Login to https://console.cloud.google.com/bigquery and select the existing project you created there. (Reach out to Customer Success if you need to reset your password).
- Create a new view for usergroups table. Run the script below (with your account number) and save the view under the same project and dataset you created before.
Select * from `activtrak-com-214616.accountnumber.usergroups` (replace account number in the query with your activtrak app instance account number)
1. Once the view is created. Open your Power BI Template for MS Teams you downloaded from the ActivConnect page (https://app.activtrak.com/#/app/reports/ActivConnect)
2. After opening the template, enter the parameters here like your ProjectName, DatasetName, ViewName, and GroupViewName ( you just created this in BQ console running querying usergroups table for your account)
You can find the values of the top 4 parameters from BigQuery where the views were created under your project.
For other parameters:
- Timezone: Enter your relevant UTC timezone. (Number only. Eg: -6 is ours)
- Daylight Savings: This is ‘False’ or ‘True’ depending on if your location observes daylight saving time.
- Focused Time Apps: This is open to how you want to define it. Default value ‘2’
- Focused Time Screens: Open to your judgment. Default value ‘4‘
- Inactive Agent Last Activity Threshold (Hours): Default value ‘72’
Final Step: Once you enter the parameters, load the template and use this to slice and dice the data as per your settings and publish the template to a workspace in Power BI service which your team has access to.
Note: You can visit https://support.activtrak.com/hc/en-us/articles/360042146172-ActivTrak-for-Power-BI-Setup-Guide For additional information on how to change the default values, schedule a refresh of data, etc.
Add ActivTrak Power BI reports to MS Teams
If you already have a team created in MS Teams you can skip all the way to step 5.
- Install Microsoft Teams & sign in to your account, you will see the below screen first.
- You will need to create a new team in order to start conversations with other peers, to do that, simply hover over the “Create a team” option and click the “Create team” button or join an already created team through the team code to join it.
You would have two options, “Build a team from scratch” for a brand-new team to be created or “Create from a 365 group” where we can select a team we already own or an Office 365 group to be added into the team.
- Selecting “Build a team from scratch” will give you the option to create a “Public” or “Private” type of team, on the first one anyone can join by having the code whereas the second only through invitation a member can be added, select the best choice for your need.
- Next, provide a name to the Team and also provide a brief description of the team and click “Create”
- Once the team is created, you will be prompted to add members into the team, we can skip it for now.
- In the main screen of the Team, on the upper header within the “General” section, you will have some options like “Posts” “Files” and “Wiki”, next to Wiki you can click the “+” sign.
- From the pop up you can either search for Power BI or directly click on the icon displayed.
- Once you click on the Power BI icon in the above MS Teams tab section, a pop-up should appear showing you all the reports available under different workspaces you are part of.
- Select the report you just published. The default name is ActivTrak for MS Teams unless you changed it while saving and publishing before.
- Teams will take you back to the main screen and now a new tab with ActivTrak Power BI dashboard will be added to the group chat. Here you can collaborate, share and discuss productivity metrics with your team.
Data Dictionary
|
Type/Folder |
Field Name |
Type |
Description |
Source |
Formula |
|
Standard Metrics |
% of Users |
Measure |
User count as a % of total users |
Table Calculation |
[Users]/CALCULATE([Users], ALL('ActivTrak Data'[User Name])) |
|
Standard Metrics |
Active Time |
Measure |
Active time expressed as hours, minutes and seconds |
Calculation |
// We start with a duration in number of seconds VAR Duration = CALCULATE([Duration (sec)],'ActivTrak Data'[Activity Type]<>"Passive") // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) ) |
|
Standard Metrics |
Active Time (hr) |
Measure |
Time identified as active in hours |
Calculation |
CALCULATE([Duration (hr)], 'ActivTrak Data'[Activity Type]<>"Passive") |
|
Standard Metrics |
Active Time (min) |
Measure |
Time identified as active in minutes |
Calculation |
CALCULATE([Duration (min)], 'ActivTrak Data'[Activity Type]<>"Passive") |
|
Standard Metrics |
Active Time % |
Measure |
Active time as % of total time |
Calculation |
[Active Time (hr)]/[All Time (hr)] |
|
Standard Dimensions |
Activity Date |
Dimension |
Activity date in local time |
Calculation |
DateTime.Date([#"Activity Time"]) |
|
Standard Dimensions |
Activity Minute |
Dimension |
Activity minute in local time |
Calculation |
[Activity Date] & [#"Activity Minute (Time Only)"] |
|
Standard Dimensions |
Activity Time |
Dimension |
Activity datetime in local time zone (configurable) |
Calculation |
if #"Daylight Savings" = true then [#"Activity Time (UTC)"]+#duration(0,#"Time Zone",0,0)+1 else [#"Activity Time (UTC)"]+#duration(0,#"Time Zone",0,0) |
|
Standard Dimensions |
Activity Time (UTC) |
Dimension |
System activity datetime |
Data Source Field |
time |
|
Standard Dimensions |
Activity Type |
Dimension |
Type of activity captured |
Calculation |
if [#"Is Idle Event?"] = false then if [Log URL] = null then "Application" else if Text.Contains(Text.Lower([Log URL]),#"Search URL Term") then "Search" else "Site" else "Passive" |
|
Global Filters |
Activity Type Filter |
Dimension |
Type of activity captured. Field used for global filtering. |
Calculation |
[Activity Type] |
|
Advanced Metrics |
After Hours Time |
Measure |
Weekday after hours time |
Calculation |
CALCULATE([Duration (hr)],FILTER('ActivTrak Data','ActivTrak Data'[Time of Day]="Night"||'ActivTrak Data'[Time of Day]="Overnight"),'ActivTrak Data'[Day Type]="Weekday") END |
|
Standard Metrics |
Alarms Triggered |
Measure |
Count of alarms fired |
Calculation |
if [Email] = 1 or [Popup] = 1 or [Screenshot] = 1 or [Terminate] = 1 then 1 else 0 |
|
Standard Metrics |
All Time (hr) |
Measure |
Total time in hours |
Calculation |
[Duration (hr)] |
|
Standard Dimensions |
App/Site Status |
Dimension |
Logic to identify approved vs unapproved apps/sites. This logic should be modified to determine app/site status for your particular company. |
Calculation |
//This logic should be modified to determine app/site status for your particular company if (([Category] = null) or (Text.Upper([Category]) = "UNCATEGORIZED")) and (([Productivity] = null) or (Text.Upper([Productivity]) = "UNDEFINED")) then "Unapproved" else "Approved" |
|
Standard Dimensions |
Application |
Dimension |
Application name |
Calculation |
if [Log Description] = null then [Executable] else if [Log Description] = """""" then [Executable] else [Log Description])) |
|
Standard Dimensions |
Application/Site |
Dimension |
Display application name for applications or browser and site for sites |
Calculation |
if [Site] = null then [Application] else ([Application]&""&(" ("&""&[Site]&""&")")) |
|
Standard Metrics |
Applications |
Measure |
Count of applications |
Calculation |
COUNT('ActivTrak Data'[Application]) |
|
Advanced Metrics |
Avg Active Hours / Day |
Measure |
Avg active hours per user per day |
Calculation |
[Hours / User / Day] |
|
Advanced Metrics |
Avg Focused Hours / Day |
Measure |
Avg focused hours per user per day |
Calculation |
[Focused Time (hr)]/[Users]/[Days With Activity] |
|
Advanced Metrics |
Avg Productive Hours / Day |
Measure |
Avg productive hours per user per day |
Calculation |
[Productive Hours/User/Day] |
|
Advanced Metrics |
Avg Productive Hours / Day (Team) |
Measure |
Avg productive hours per team per day |
Calculation |
CALCULATE([Avg Productive Hours / Day], ALLSELECTED('ActivTrak Data'[User Name])) |
|
Advanced Metrics |
Days With After Hours Activity |
Measure |
Days where users are working after 6 pm local time. |
Calculation |
CALCULATE(DISTINCTCOUNT('ActivTrak Data'[Activity Date]), FILTER('ActivTrak Data',[After Hours Time]>0)) |
|
Advanced Metrics |
Days with After Hours Activity/ Week |
Measure |
No. of Days per week where users are working after 6 pm local time. |
Calculation |
[Days With After Hours Activity]/[Weeks with Activity] |
|
Advanced Metrics |
Burnout Risk Color |
Measure |
Formula to decide the color of the values in scatter plot. The color changes depending on the default X & Y axis values on the page. |
Calculation |
IF(AND([Avg Active Hours / Day]<='Y Axis - Avg Active Hours/Day'[Y Axis - Avg Active Hours/Day Value],[Days with After Hours Activity/ Week]<='X Axis - Days with After Hours Activity/ week'[X Axis - Days with After Hours Activity/ week Value]),"#10CFBD", IF(AND([Avg Active Hours / Day]<='Y Axis - Avg Active Hours/Day'[Y Axis - Avg Active Hours/Day Value],[Days with After Hours Activity/ Week]>'X Axis - Days with After Hours Activity/ week'[X Axis - Days with After Hours Activity/ week Value]),"#F8D053", IF(AND([Avg Active Hours / Day]>'Y Axis - Avg Active Hours/Day'[Y Axis - Avg Active Hours/Day Value],[Days with After Hours Activity/ Week]<='X Axis - Days with After Hours Activity/ week'[X Axis - Days with After Hours Activity/ week Value]),"#F8D053","#F55753"))) |
|
Advanced Metrics |
Productivity Color |
Measure |
Formula to give individual users green or yellow bar depending on the if their value is above or below the Group average |
Calculation |
IF([Avg Productive Hours / Day]<[Avg Productive Hours / Day (Group)],"#F8D053", "#10CFBD") |
|
Advanced Metrics |
Days with Weekend Activity |
Measure |
Counts the numbers if days where there was weekend activity |
Calculation |
CALCULATE(DISTINCTCOUNT('ActivTrak Data'[Activity Date]), FILTER('ActivTrak Data',[Weekend Time]>0)) |
|
Advanced Metrics |
Days Working Off Hours |
Measure |
Counts number of days where working time was beyond afternoon limit of 6 pm local time if using local timezone |
Calculation |
[Days with Weekend Activity] + [Days With After Hours Activity] |
|
Advanced Metrics |
Avg Productive Hours/Day (Group) |
Measure |
Avg of average productive hours per user per day in a group |
Calculation |
CALCULATE(AVERAGEX(VALUES('ActivTrak Data'[User Name]),[Avg Productive Hours / Day]),ALLNOBLANKROW('ActivTrak Data'[User Name])) |
|
Advanced Metrics |
Control Measure |
Measure |
To filter all users that are part of a selected user group |
Calculation |
IF([Avg Productive Hours / Day (Team V1)]=0, 0,1)
|
|
Advanced Metrics |
Days since last activity - application |
Measure |
Column measure for days since last activity by application and by user |
Calculation |
CALCULATE(DATEDIFF([Last Activity],TODAY(),DAY),ALLEXCEPT('ActivTrak Data', 'ActivTrak Data'[Application],'ActivTrak Data'[User Name]))
|
|
Advanced Metrics |
Days since last activity - Site |
Measure |
Column measure for days since last activity by site and by user |
Calculation |
CALCULATE(DATEDIFF([Last Activity],TODAY(),DAY),ALLEXCEPT('ActivTrak Data', 'ActivTrak Data'[Site],'ActivTrak Data'[User Name])) |
|
Advanced Metrics |
Application Time (Mins) |
Measure |
Total application time by all users in minutes |
Calculation |
CALCULATE(SUM('ActivTrak Data'[Duration (sec)])/60, ALLEXCEPT('ActivTrak Data','ActivTrak Data'[Application]))
|
|
Advanced Metrics |
Site Time (Mins) |
Measure |
Total site time by all users in minutes |
Calculation |
CALCULATE(SUM('ActivTrak Data'[Duration (sec)])/60, ALLEXCEPT('ActivTrak Data','ActivTrak Data'[Site]))
|
|
Standard Dimensions |
Category |
Dimension |
Activity category specified in the ActivTrak application. |
Data Source Field |
category |
|
Advanced Dimensions |
Clean Url |
Dimension |
Url without the protocol (http:// or https://) |
Calculation |
if [Log URL] = null then null else if Text.Contains([Log URL],"http://") then Text.Middle([Log URL],7) else if Text.Contains([Log URL],"https://") then Text.Middle([Log URL],8) else [Log URL] |
|
Standard Dimensions |
Computer |
Dimension |
Computer name. Comes from the operating system. |
Data Source Field |
computer |
|
Advanced Metrics |
Context Switches |
Measure |
Identifies a context switches when a user views too many different applications or screens within one minute. The number of apps or screens is configurable. |
Calculation |
CALCULATE(SUMX(SUMMARIZE('ActivTrak Data','ActivTrak Data'[User Name],'ActivTrak Data'[Activity Minute]),IF(([Distinct Applications]>SELECTEDVALUE(Parameters[Focused Time - Max Apps per Minute])) || ([Distinct Screens]>SELECTEDVALUE(Parameters[Focused Time - Max Screens per Minute])),1))) |
|
Advanced Metrics |
Context Switches Per Hour |
Measure |
Number of context switches per hour monitored |
Calculation |
[Context Switches]/[Duration (hr)] |
|
Standard Dimensions |
Day of Week |
Dimension |
Day of the week from the Activity Date |
Calculation |
Date.DayOfWeekName([Activity Date]) |
|
Standard Dimensions |
Day Type |
Dimension |
Weekday vs weekend |
Calculation |
if Date.DayOfWeek([Activity Date],Day.Monday) >= 5 then "Weekend" else "Weekday" |
|
Global Filters |
Day Type Filter |
Dimension |
Weekday vs weekend. Field used for global filtering. |
Calculation |
[Day Type] |
|
Standard Metrics |
Days Monitored |
Measure |
Distinct number of days monitored |
Calculation |
DISTINCTCOUNT('ActivTrak Data'[Activity Date]) |
|
Advanced Metrics |
Days Per User |
Measure |
Distinct number of days monitored per user |
Calculation |
CALCULATE(DISTINCTCOUNT('ActivTrak Data'[Activity Date]),ALL('ActivTrak Data'),VALUES('ActivTrak Data'[User Name]),VALUES('ActivTrak Data'[Productivity])) |
|
Advanced Metrics |
Days Since Last Activity |
Measure |
Days since last activity compared to today |
Calculation |
DATEDIFF([Last Activity],TODAY(),DAY) |
|
Advanced Metrics |
Days with Activity |
Measure |
Distinct days with activity reported |
Calculation |
[Days Monitored] |
|
Standard Metrics |
Distinct Applications |
Measure |
Distinct number of applications |
Calculation |
DISTINCTCOUNTNOBLANK('ActivTrak Data'[Application]) |
|
Standard Metrics |
Distinct Screens |
Measure |
Distinct number of screens |
Calculation |
DISTINCTCOUNTNOBLANK('ActivTrak Data'[Title Bar]) |
|
Standard Metrics |
Distinct Sites |
Measure |
Distinct number of websites |
Calculation |
DISTINCTCOUNTNOBLANK('ActivTrak Data'[Site]) |
|
Advanced Metrics |
Distracted Time (min) |
Measure |
Number of minutes marked as distracted time |
Calculation |
|
|
Standard Metrics |
Duration (hh:mm:ss) |
Measure |
Activity duration in standard time format. |
Calculation |
// We start with a duration in number of seconds VAR Duration = [Duration (sec)] // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) ) |
|
Standard Metrics |
Duration (hr) |
Measure |
Activity duration in hours. |
Calculation |
[Duration (min)]/60 |
|
Standard Metrics |
Duration (min) |
Measure |
Activity duration in minutes. |
Calculation |
[Duration (sec)]/60 |
|
Standard Metrics |
Duration (sec) |
Measure |
Activity duration in seconds. |
Data Source Field |
duration |
|
Global Filters |
Duration (sec) Filter |
Measure |
Activity duration in seconds. Field used for global filtering. |
Calculation |
[Duration (sec)] |
|
Standard Metrics |
Duration as % of Total Week |
Measure |
Duration as a % of total time in the week |
Calculation |
[Duration (hr)] / [Total Week Duration] |
|
Standard Metrics |
Duration Per User |
Measure |
Duration per user |
Calculation |
CALCULATE([Duration (sec)],ALL('ActivTrak Data'),VALUES('ActivTrak Data'[User Name])) |
|
Standard Metrics |
|
Measure |
Flag to denote if the log entry is associated with an alarm that triggered an email (0 = no email notification was sent, 1 = email notification was sent) |
Data Source Field |
|
|
Standard Dimensions |
Executable |
Dimension |
Name of the app running, usually in it's raw format. E.g. Slack.exe |
Data Source Field |
executable |
|
Advanced Metrics |
First Activity |
Measure |
First activity (at the minute level of granularity) |
Calculation |
MIN('ActivTrak Data'[Activity Minute]) |
|
Advanced Metrics |
First Activity Date Range |
Measure |
First activity (at the minute level of granularity) for filtering |
Calculation |
MIN('ActivTrak Data'[Activity Minute]) |
|
Advanced Metrics |
Focus Score |
Measure |
Focused time % was expressed as a score (1-100) |
Calculation |
([Focused Time % ]*100) |
|
Advanced Metrics |
Focused Time (hr) |
Measure |
Focused time expressed as hours |
Calculation |
[Focused Time (min)]/60 |
|
Advanced Metrics |
Focused Time (min) |
Measure |
Time where minimal distractions or context switches are observed (Active Time - Distracted Time). We identify a minute as “distracted” when the user switches between three or more apps and five or more different screens. These thresholds are configurable. |
Calculation |
CALCULATE(IF([Context Switches]>0,IF([Active Time (min)]-[Context Switches]>=0,[Active Time (min)]-[Context Switches],0))) |
|
Advanced Metrics |
Focused Time % |
Measure |
Focused time as % of all Active Time |
Calculation |
[Focused Time (min)]/[Active Time (min)] |
|
Advanced Metrics |
Hours Since Last Activity |
Measure |
Hours since last activity compared to now |
Calculation |
DATEDIFF([Last Activity],TODAY(),HOUR) |
|
Advanced Metrics |
Hours/User |
Measure |
Number of hours per user |
Calculation |
[Duration (hr)]/[Users] |
|
Advanced Metrics |
Hours/User/Day |
Measure |
Number of hours per user per day |
Calculation |
[Duration (hr)]/[Users]/[Days With Activity] |
|
Standard Dimensions |
IP Address |
Dimension |
Private IP address |
Data Source Field |
ip |
|
Standard Dimensions |
Is Idle Event? |
Dimension |
Flag to denote if an event is idle or not |
Calculation |
if [Log Description] = null then true else false |
|
Advanced Metrics |
Last Activity |
Measure |
Last activity (at the minute level of granularity) |
Calculation |
MAX('ActivTrak Data'[Activity Minute]) |
|
Advanced Metrics |
Last Reported Activity |
Measure |
Last reported activity expressed in bins (e.g. < 24 hours) |
Calculation |
CALCULATE(IF([Hours Since Last Activity]<24,"< 24 hours",IF(24<=[Hours Since Last Activity]<48,"24-48 hours",IF(48<=[Hours Since Last Activity]<72,"48-72 hours","> 72 hours")))) |
|
Standard Dimensions |
Log Description |
Dimension |
Friendly name of the app or site running. Almost always the same as "executable" but in Proper Case. |
Data Source Field |
log_description |
|
Standard Dimensions |
Log Url |
Dimension |
Url for website related activity, otherwise it will be null. |
Data Source Field |
log_url |
|
Standard Dimensions |
Logon Domain |
Dimension |
Active Directory domain attached computers will show the domain name here. If not attached to a domain, the computername will be shown. Macs almost always show computername and Google Chrome agents will not show anything. Comes from operating system. |
Data Source Field |
logondomain |
|
Advanced Metrics |
Off Hours Time (hr) |
Measure |
Aggregate of off hours activity = Weekday after hours time + Weekend time |
Calculation |
[After Hours Time]+[Weekend Time] |
|
Advanced Metrics |
Passive Time (hr) |
Measure |
Time marked as passive |
Calculation |
CALCULATE([Duration (hr)],'ActivTrak Data'[Activity Type]="Passive") |
|
Advanced Metrics |
Passive Time % |
Measure |
Passive time as a % of all time |
Calculation |
[Passive Time (hr) ]/[All Time (hr)] |
|
Standard Metrics |
Popup |
Measure |
Flag to denote if the log entry is associated with an alarm that triggered a popup in the user's screen (0 = popup notification didn't fire, 1 = popup notification fired) |
Data Source Field |
popup |
|
Advanced Dimensions |
Potential File Transfer |
Dimension |
Combines all the file tranfer keyword logic into one calculation |
Calculation |
if [Potential File Transfer 1] or [Potential File Transfer 2] or [Potential File Transfer 3] or [Potential File Transfer 4] or [Potential File Transfer 5] then true else false |
|
Advanced Dimensions |
Potential File Transfer 1 |
Dimension |
Returns true if an activity entry matches the File Transfer Keyword 1 |
if [Log Description] = null then false else if Text.Contains(Text.Upper([Log Description]),(Text.Upper("onedrive"))) then true else false |
|
|
Advanced Dimensions |
Potential File Transfer 2 |
Dimension |
Returns true if an activity entry matches the File Transfer Keyword 2 |
if [Log Description] = null then false else if Text.Contains(Text.Upper([Log Description]),(Text.Upper("dropbox"))) then true else false |
|
|
Advanced Dimensions |
Potential File Transfer 3 |
Dimension |
Returns true if an activity entry matches the File Transfer Keyword 3 |
if [Log Description] = null then false else if Text.Contains(Text.Upper([Log Description]),(Text.Upper("ftp"))) then true else false |
|
|
Advanced Dimensions |
Potential File Transfer 4 |
Dimension |
Returns true if an activity entry matches the File Transfer Keyword 4 |
if [Log Description] = null then false else if Text.Contains(Text.Upper([Log Description]),(Text.Upper("usb"))) then true else false |
|
|
Advanced Dimensions |
Potential File Transfer 5 |
Dimension |
Returns true if an activity entry matches the File Transfer Keyword 5 |
if [Log Description] = null then false else if Text.Contains(Text.Upper([Log Description]),(Text.Upper("disk"))) then true else false |
|
|
Standard Dimensions |
Primary Domain |
Dimension |
Often the same as the local system name, though not always. if the computer is attached to an Active Directory domain, that domain name will show up here. If not, it's typical for Windows machines to use "WORKGROUPS", Chrome agents to use "Google". Macs typically don't show anything related to primary domain. Comes from operating system. |
Data Source Field |
primarydomain |
|
Advanced Metrics |
Productive Applications |
Measure |
Count of distinct applications classified as productive |
Calculation |
CALCULATE(DISTINCTCOUNT('ActivTrak Data'[Application]),'ActivTrak Data'[Productivity]="PRODUCTIVE") |
|
Advanced Metrics |
Productive Hours/User/Day |
Measure |
Avg productive hours per user per day |
Calculation |
[Productive Time (hr)]/[Users]/[Days With Activity] |
|
Advanced Metrics |
Productive Screens |
Measure |
Count of screens (title bars) classified as productive |
Calculation |
CALCULATE(COUNT('ActivTrak Data'[Title Bar]),'ActivTrak Data'[Productivity]="PRODUCTIVE") |
|
Advanced Metrics |
Productive Screens % |
Measure |
Productive screens as a % of total |
Calculation |
[Productive Screens]/[Screens] |
|
Advanced Metrics |
Productive Time |
Measure |
Productive time expressed in hours, minutes, seconds format |
Calculation |
VAR Duration = CALCULATE([Duration (sec)],'ActivTrak Data'[Productivity]="PRODUCTIVE") // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number // These intermediate variables ensure that we have leading zero's concatenated onto single digits // Hours with leading zeros VAR H = IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) ) // Minutes with leading zeros VAR M = IF ( LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ) ) // Seconds with leading zeros VAR S = IF ( LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ) ) // Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" RETURN CONCATENATE ( H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) ) |
|
Advanced Metrics |
Productive Time (hr) |
Measure |
Time classified as productive |
Calculation |
CALCULATE([Duration (hr)],'ActivTrak Data'[Productivity]="PRODUCTIVE") |
|
Advanced Metrics |
Productive Time % |
Measure |
Productive time as a % of total |
Calculation |
[Productive Time (hr)]/[All Time] |
|
Standard Dimensions |
Productivity |
Dimension |
Productivity. Available values are Undefined, Productive, Unproductive. Idle time is defined in Title bar field. |
Data Source Field |
productivity |
|
Standard Dimensions |
Public IP Address |
Dimension |
Public IP address is the IP address that can be accessed over the Internet |
Data Source Field |
publicip |
|
Users & Groups |
Reporting Group |
Dimension |
Adhoc organization of users into groups (e.g. Sales, Marketing, etc.) |
Calculation |
Manual classification |
|
Global Filters |
Reporting Group Filter |
Dimension |
Reporting group. Field used for global filtering. |
Calculation |
[Reporting Group] |
|
Advanced Metrics |
Reporting Group Hours/User/Day |
Measure |
Avg hours per user per day by group |
Calculation |
CALCULATE([Duration (hr)],ALL('ActivTrak Data'[User Name])) |
|
Advanced Metrics |
Reporting Status |
Measure |
ActivTrak agent reporting status based on the threshold of inactivity |
Calculation |
IF([Hours Since Last Activity]<MAX(Parameters[Inactive Agent Last Activity Threshold (Hours)]),"Recent","Dated") |
|
Standard Metrics |
Screens |
Measure |
Count of screens (title bars) |
Calculation |
COUNT('ActivTrak Data'[Title Bar]) |
|
Standard Metrics |
Screenshot |
Measure |
Flag to denote if the log entry is associated with an alarm that triggered a screenshot capture (0 = screenshot was not captured, 1 = screenshot was captured) |
Data Source Field |
screenshot |
|
Advanced Dimensions |
Search Term |
Dimension |
Extracts the search term from the raw query string in the url |
Calculation |
if [Activity Type] = "Search" then Text.Middle([Search Term Raw],0,[Search Term Length]) else null |
|
Advanced Dimensions |
Search Term Clean |
Dimension |
Cleans up the search term by removing delimters |
Calculation |
if [Activity Type] = "Search" then Text.Replace([Search Term],"+"," ") else null |
|
Advanced Dimensions |
Search Term Length |
Dimension |
Obtains the length of the search term |
Calculation |
if [Search Term Raw] = null then null else if Text.Contains([Search Term Raw],"&") then Text.PositionOf([Search Term Raw],"&") else if Text.Contains([Search Term Raw],"-") then Text.PositionOf([Search Term Raw],"-") else Text.Length([Search Term Raw]) |
|
Advanced Dimensions |
Search Term Prefix |
Dimension |
Identifies the search term prefix used in a particular URL |
Calculation |
if [Activity Type] = "Search" then if Text.Contains(Text.Lower([Log URL]),"q=") then "q=" else if Text.Contains(Text.Lower([Log URL]),"text=") then "text=" else if Text.Contains(Text.Lower([Log URL]),"term=") then "term=" else if Text.Contains(Text.Lower([Log URL]),"desc=") then "desc=" else if Text.Contains(Text.Lower([Log URL]),"query=") then "query=" else null else null |
|
Advanced Dimensions |
Search Term Prefix Position |
Dimension |
Get the position of the search term prefix within the url |
Calculation |
if [Activity Type] = "Search" then if [Search Term Prefix] <> null then Text.PositionOf(Text.Lower([Log URL]),Text.Lower([Search Term Prefix])) else null else null |
|
Advanced Dimensions |
Search Term Raw |
Dimension |
Extracts the search term raw from the url or title bar |
Calculation |
if [Activity Type] = "Search" then if [Search Term Prefix] <> null then Text.Middle([Log URL],[Search Term Prefix Position]+Text.Length([Search Term Prefix])) else if Text.Contains([Title Bar],"Search") then [Title Bar] else "Undefined" else null |
|
Advanced Metrics |
Searches |
Measure |
Count of search activities |
Calculation |
CALCULATE(COUNT('ActivTrak Data'[Title Bar]),'ActivTrak Data'[Activity Type]="Search") |
|
Advanced Metrics |
Searches Per Day |
Measure |
Count of searches per monitored day |
Calculation |
[Searches]/[Days With Activity] |
|
Standard Dimensions |
Site |
Dimension |
Website without protocol (e.g. http://) nor paths/parameters |
Calculation |
if [Clean Url] = null then null else if Text.PositionOf([Clean Url],"/")>0 then Text.BeforeDelimiter([Clean Url],"/") else [Clean Url] |
|
Standard Metrics |
Terminate |
Measure |
Flag to denote if the log entry is associated with an alarm that triggered an application termination (0 = no application termination occurred, 1 = originating application was terminated) |
Data Source Field |
terminate |
|
Standard Dimensions |
Time of Day |
Dimension |
Part of the day for activity time. - Morning: 6am-12pm - Afternoon: 12pm-6pm - Night: 6pm-12am - Overnight: 12am-6am |
Calculation |
if Time.Hour([Activity Time])>=6 and Time.Hour([Activity Time])<12 then "Morning" else if Time.Hour([Activity Time])>=12 and Time.Hour([Activity Time])<18 then "Afternoon" else if Time.Hour([Activity Time])>=18 and Time.Hour([Activity Time])<24 then "Night" else "Overnight" |
|
Standard Dimensions |
Title Bar |
Dimension |
Name of title bar of the active window. |
Data Source Field |
titlebar |
|
Standard Metrics |
Total Time |
Measure |
Total time expressed in hours, minutes, seconds format |
Calculation |
[Duration (hh:mm:ss)] |
|
Advanced Metrics |
Total Week Duration |
Measure |
Total time reported in a week |
Calculation |
CALCULATE ([Duration (hr)],ALL('ActivTrak Data'),VALUES('ActivTrak Data'[Activity Date (Week)]),VALUES('ActivTrak Data'[Day Type])) |
|
Advanced Metrics |
Unproductive Applications |
Measure |
Count of distinct applications classified as unproductive |
Calculation |
CALCULATE (DISTINCTCOUNT('ActivTrak Data'[Title Bar]),'ActivTrak Data'[Productivity]="UNPRODUCTIVE") |
|
Advanced Metrics |
Unproductive Screens |
Measure |
Count of distinct screens (title bars) classified as unproductive |
Calculation |
CALCULATE (COUNT('ActivTrak Data'[Title Bar]),'ActivTrak Data'[Productivity]="UNPRODUCTIVE") |
|
Advanced Metrics |
Unproductive Screens % |
Measure |
Unproductive screens as a % of total |
Calculation |
[Unproductive Screens]/[Screens] |
|
Advanced Metrics |
Unproductive Screens Per Hour |
Measure |
Number of screens classified as unproductive per hour |
Calculation |
[Unproductive Screens]/[Duration (hr)] |
|
Advanced Metrics |
Unproductive Time (hr) |
Measure |
Time classified as productive |
Calculation |
CALCULATE ([Duration (hr)],'ActivTrak Data'[Productivity]="UNPRODUCTIVE") |
|
Advanced Metrics |
Unproductive Time % |
Measure |
Unproductive time as a % of total |
Calculation |
[Unproductive Time (hr)]/[All Time] |
|
Users & Groups |
User |
Dimension |
Friendly user name. E.g. First Last. Comes from operating system. |
Data Source Field |
user |
|
Users & Groups |
User (Raw) |
Dimension |
Simple login name. Often first initial, last name. Comes from operating system. |
Data Source Field |
userraw |
|
Users & Groups |
User Alias |
Dimension |
Comes from ActivTrak, and not the operating system. User Aliases are used to combine multiple user logins in our graphs and reports as well as provide a "friendly name" in case the "userraw" and "user" fields are ambiguous. |
Data Source Field |
useralias |
|
Users & Groups |
User Name |
Dimension |
Displays user alias if specified, otherwise, it displays user |
Calculation |
if [User Alias] = null then [User] else [User Alias] |
|
Standard Metrics |
Users |
Measure |
Distinct count of users (using user name as the field for user). |
Calculation |
DISTINCTCOUNTNOBLANK ('ActivTrak Data'[User Name]) |
|
Standard Metrics |
Webhook |
Measure |
Flag to denote if the log entry is associated with an alarm that triggered a webhook sent (0 = no webook sent, 1 = webhook was sent) |
Data Source Field |
webhook |
|
Advanced Metrics |
Weekend Time |
Measure |
Time recorded during weekend |
Calculation |
CALCULATE([Duration (hr)], 'ActivTrak Data'[Day Type]="Weekend") |
|
Parameters |
Daylights Savings |
Parameter |
Flag to denote daylight savings conversion from UTC time. |
Boolean |
False |
|
Parameters |
ViewName |
Parameter |
View name of the logs table in Big Query |
Text |
default: logs |
|
Parameters |
GroupViewName |
Parameter |
View name of the usergroups table in Big Query |
Text |
default: usergroups |
|
Parameters |
File Transfer Keyword 1 |
Parameter |
Keyword to match activities to potential file transfers. Can be changed. |
String |
onedrive |
|
Parameters |
File Transfer Keyword 2 |
Parameter |
Keyword to match activities to potential file transfers. Can be changed. |
String |
dropbox |
|
Parameters |
File Transfer Keyword 3 |
Parameter |
Keyword to match activities to potential file transfers. Can be changed. |
String |
ftp |
|
Parameters |
File Transfer Keyword 4 |
Parameter |
Keyword to match activities to potential file transfers. Can be changed. |
String |
usb |
|
Parameters |
File Transfer Keyword 5 |
Parameter |
Keyword to match activities to potential file transfers. Can be changed. |
String |
disk |
|
Parameters |
Focused Time - Max Distinct Apps within minute |
Parameter |
Max number of different applications you can switch within one minute before such minute will be marked as distracted. |
Integer |
2 |
|
Parameters |
Focused Time - Max Distinct Screens within minute |
Parameter |
Max number of different screens you can switch within one minute before such minute will be marked as distracted. |
Integer |
4 |
|
Parameters |
Inactive Agent Last Activity Threshold (Hours) |
Parameter |
Number of hours since last agent activity before it is marked as non-reporting |
Integer |
72 |
|
Parameters |
Search Prefix 1 |
Parameter |
Keyword used to identify a search prefix in a url query |
String |
q= |
|
Parameters |
Search Prefix 2 |
Parameter |
Keyword used to identify a search prefix in a url query |
String |
text= |
|
Parameters |
Search Prefix 3 |
Parameter |
Keyword used to identify a search prefix in a url query |
String |
term= |
|
Parameters |
Search Prefix 4 |
Parameter |
Keyword used to identify a search prefix in a url query |
String |
desc= |
|
Parameters |
Search Prefix 5 |
Parameter |
Keyword used to identify a search prefix in a url query |
String |
query= |
|
Parameters |
Search Titlebar Suffix |
Parameter |
Search Titlebar Suffix |
String |
- |
|
Parameters |
Search Titlebar Term |
Parameter |
Search Titlebar Term |
String |
Search |
|
Parameters |
Search URL Suffix |
Parameter |
Search URL Suffix |
String |
& |
|
Parameters |
Search URL Term |
Parameter |
Search URL Term |
String |
search? |
|
Parameters |
Time Zone |
Parameter |
Time to convert from UTC time. |
Integer |
-6 (Central Time) |
|
Parameters |
Days with After Hours Activity /week threshold |
Parameter |
Parameters to control the axis values |
Decimal |
2 |
|
Parameters |
Avg Active Hours / Day threshold |
Parameter |
Parameters to control the axis values |
Decimal |
7.5 |
Was this article helpful?
0 out of 0 found this helpful
Comments
No comments