ActivTrak for MS Teams Setup Guide

ActivTrak for MS Teams

Setup Guide

Draft v. 1.0                     

Step by step guide on how to setup an Activtrak Dashboard for MS Teams through Power Bl

Table of Contents

 

Step No.

Topic 

Page 

1

Requirements

2

Configuration for new Data Connect Customers

2

3

Configuration for existing Data Connect 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 licence for Power BI and MS Teams. Specifically, you need to meet the following requirements:

  • Users have an ActivTrak Advanced license and the ActivTrak Data Connect 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. 

 

Configuration for new Data Connect customers

Prerequisite: You will need the Google Cloud account you provided to ActivTrak support to access your ActivTrak Data Connect instance.

 

Follow the steps below to configure ActivTrak’s Data Connect 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 Data Connect set up team
  •       Enter your [accountnumber]@activtrak.us email address
  •       Enter your temporary password [you will be prompted to change it]. 2020-07-24_08-40-52.png

unnamed.png

Default View

                         Importing your data in Power BI desktop application:

    1. 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 Data Connect page.
    2. 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 your project that you just received from the Data Connect setunnamed__1_.png2020-08-18_15-12-55__1_.pngFor other parameters:
    1. Timezone: Enter your relevant UTC timezone. (Number only. Eg: -6 is ours)
    2. Daylight Savings: This is ‘False’ or ‘True’ depending on if your location observes daylight saving time.
    3. Focused Time Apps: This is open to how you want to define it. Default value ‘2’
    4. Focused Time Screens: Open to your judgement. Default value ‘4’ 
    5. 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. One you sign in your idea would load in the template that we built and from the 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 Data Connect customers

If you are an existing Data Connect customer you may already have a Google Cloud account which you created or you may have one provisioned by activtrak. If its 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 its not already configured for you. To create the view in BigQuery, follow the steps below:



  1. Login to https://console.cloud.google.com/bigquery and select your existing project you created there. (Reach out to Customer Success if you need to reset your password). 
  2. 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)

unnamed__2_.png

unnamed__3_.png

1. Once the view is created. Open your Power BI Template for MS Teams you downloaded from the Data Connect page (https://app.activtrak.com/#/app/reports/dataconnect)

Screen_Shot_2020-08-24_at_4.04.53_PM.png

 

 

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)2020-08-18_15-12-55.pngYou can find the values of top 4 parameters from BigQuery where the views were created under your project.

 For other parameters: 

  1. Timezone: Enter your relevant UTC timezone. (Number only. Eg: -6 is ours)
  2. Daylight Savings: This is ‘False’ or ‘True’ depending on if your location observes daylight saving time.
  3. Focused Time Apps: This is open to how you want to define it. Default value ‘2’
  4. Focused Time Screens: Open to your judgement. Default value ‘4‘
  5. 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.

  1. Install Microsoft Teams & sign in to your account, you will see the below screen first.

image8.png

  1. 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.


unnamed__5_.png



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.

      1. 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.
      2. Next, provide a name to the Team and also provide a brief description of the team and click “Create”

unnamed__11_.png

  1. Once the team is created, you will be prompted to add members into the team, we can skip it for now.image3.png
  1. 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.

unnamed__6_.png

  1. From the pop up you can either search for Power BI or directly click on the icon displayed.

image6.png

  1. 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. 
  2. Select the report you just published. Default name is ActivTrak for MS Teams unless you changed it while saving and publishing before.

unnamed__8_.png




  1. 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.


unnamed__9_.png


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

Email

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