ActivTrak for Power BI Setup Guide

ActivTrak Data Connect Overview

Data Connect provides customers direct access into user behavioral activity data to create personalized reports with business intelligence data visualization tools (e.g.Tableau, PowerBI, Google Data Studio, etc.) and data reporting tools (e.g. Google Sheets, Microsoft Excel).

ActivTrak’s Data Connect Power BI Template includes a combination of pre-built valuable metrics, reports, and dashboards to help you analyze how your organization works and jump-start the creation of your own customized reports.

 

Configuration

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

  1. Connect to Google big query:
    • Go to https://console.cloud.google.com/home/dashboard and login if you aren’t already using your google cloud account you provided for ActivTrak
    • On the left-side navigation go to BIG DATA > BigQuery as shown below. BigQuery might not be the first option in the menu, but it is available under BIG DATA or you can search directly. 

                   Quick_start_1.png

    • Clicking on Big Query will take you to the BigQuery console page. There, under Resources click "Add Data" and pin a project if you don't already have one.

                Quick_start_2.png

    • Select your project folder once created and click on CREATE DATASET on the right.

              Quick_start_3.png

    • A pop-up appears asking for your Dataset ID and Country. Under the dataset id enter id as ‘ActivTrak’ and the country and click create dataset.

             Quick_start_4.png

    • Run the following query (Select * from `activtrak-com-214616.xxxxxx.logs` in the box and replace xxxxxx with your ActivTrak account number (This can be found in the ActivTrak app - see screenshot below).

           Quick_start_5.png

    • After running the query with your account ID, click on save view and save the ActivTrak dataset within a table. You will need to reference this saved data in your Power BI app.

          Quick_start_6.png

2. 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 Data Connect 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.

            Quick_start_7_b.png  

  • The values of top 3 parameters are relevant to your project that you just created on Google BigQuery. 

             Quick_start_8.png

  • For other parameters: 
    • Timezone: Enter your relevant UTC timezone. (Number only. E.g. -6)
    • Daylight Savings: This is "False"
    • 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 BI desktop you would be required to sign in. Once you sign in your data would load in the template that we built and from there you can slice and dice the report as per your requirement or publish and share it with your team

       quick_start_9_b.png      

3.  Configure the default parameters in the Power query editor. These parameters help you tailor the analysis to your organization's needs. Once you load your data, click on transform data and on the left side you would see the same above parameters and you can make changes here.

  • Time Zone: All activities in ActivTrak are recorded in UTC time. This parameter allows you to specify the time conversion to your organization’s primary time zone.
  • Inactive Agent Last Activity Threshold (Hours): specify the number of hours since the last reported activity on an ActivTrak agent before it is marked as non-reporting
  • Focused Time - Max Distinct Apps within a minute: maximum number of distinct applications you can switch between within a minute before such a minute is marked as ‘distracted’ time. The default value is 2 (a user will be marked as distracted for that minute if switches between 3 different applications within a minute).
  • Focused Time - Max Distinct Screens within a minute: maximum number of distinct screens you can switch between within a minute before such minute is marked as ‘distracted’ time. The default value is 4 (a user will be marked as distracted for that minute if switches between 5 screens within a minute).

Quick_start_10.png

4.  Configuring Static Parameters: There are some parameters that are static. Meaning they are still changeable but are in the form of formulas that you would be able to access only through power query editor (by clicking on transform data). Some of those parameters are.

    1. Potential File Transfer 1
    2. Potential File Transfer 2
    3. Potential File Transfer 3
    4. Potential File Transfer 4
    5. Potential File Transfer 5
    6. Search Term Prefix
    7. Search Term Prefix Position
    8. Search Term Raw
    9. Search Term Length
    10. Search Term
    11. Search Term Clean

 quick_start_11.png

5. Configure the "Reporting Group" Power BI group object to reflect which users belong to which teams. This will help you properly compare individuals to their peers.

  • RightClick on “Reporting Group” dimension
  • Select Edit Group
  • Create new user groups and assign users to each user group

quick_start_12.png

6.   (Optional) Configure the global filters (filters in all pages) in the Readme tab. These filters help you tailor the analysis to your organization's needs.

  • Activity Type Filter: allows you to pick which activity types you want to analyze and which to exclude across all dashboards. The different activity types are: application, site, search and passive. By default, all activity types are selected as each dashboard can filter some types as needed.
  • Day Type Filter: allows you to include/exclude activities happening on weekdays and/or weekends. Default, both are selected.
  • Reporting Group Filter: allows you include/exclude activities for users that are part of certain groups. By default, all groups are included.RightClick on the “Reporting Group” dimension.

7.   Review the Analysis Overview and Data Dictionary sections of this document to familiarize yourself with the pre-built analysis.

8.   Use the pre-built dashboards/reports, customize existing reports and/or create your own reports.

9.  Publish your data report on your workspace in Power BI service to share it with other team members. 

10.  Refresh Data: Your reports can be scheduled to automatically refresh in Power BI for faster performance. This setting can be applied on Power BI service on your dataset. Go to the workspace where you published your report. Given that the Big Query database data set will refresh twice a day (morning and night), we recommend you schedule the Power BI dashboards to refresh only once or twice a day.

Workspace > right click on 3 dots on dataset > Click on settings > Scheduled Refresh

 quick_start_13.png

Analysis Overview

This pre-built Power BI analysis template contains a set of predefined dimensions, measures, reports and dashboards making it easy to perform an in-depth analysis of your organization's work patterns and jump-start the creation of your own customized reports. Each activity captured by ActivTrak has a rich set of associated fields and metrics that help provide content and additional information at the granular level or in aggregate. The pre-built dashboards and underlying reports are organized around four major analysis areas:

  • Activity & Application Usage
  • Productivity
  • Collaboration & Knowledge Management
  • Compliance & Risk Management

Key Definitions

  • Activity Type: captures major types of digital activity (application, site, search or passive).
  • Category: an app or site can be assigned a category (e.g. development, business application, etc. Categories denote the activity performed using an app or site.
  • Productivity: an app or site can be classified as productive, unproductive or undefined.
  • All Time or Duration: All time recorded.
  • Active Time: Time where the computer is active (Total Time - Passive Time).
  • Productive Time: time spent on activities (apps and sites) marked as productive.
  • Focused Time: productive time where minimal distractions or context switches are observed (Productive Time - Distracted Time). By default, we identify a given minute as “distracted” when the user switches between three or more apps and five or more different screens. These thresholds are configured through the "Focused Time" parameters on the right-hand pane.
  • Focus Score: Focused Time as a % of Active Time (Focused Time / Active Time).
  • Avg Hours / User / Day: normalizes the time captured by dividing the active hours analyzed by the number of users and days. This allows you to compare and trend changes without having to worry about skewed stats due to teams of different sizes, users on vacation, etc.

Using & Customizing the Dashboards

  • Each dashboard has a brief description and instructions on how to use the dashboard in the title.
  • Date Filters: This template uses relative date filters to analyze recent data and trends automatically. You can change these filters to fit your needs.
  • Trend Charts: All trend charts are configured to display week by week analysis to identify trends. However, you can customize these charts to perform analysis at other levels (e.g. daily, monthly, etc.)
  • Filtering Data: You can select one or more values on the filter controls within the right-hand pane of each dashboard to filter the analysis.

 

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]

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

email

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

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)



Was this article helpful?
0 out of 0 found this helpful

ActivTrak Product Team
  • ActivTrak Product Team
  • Follow
Comments

To view or create a ticket, please login to ActivTrak

Log in