ActivTrak for Power BI Setup Guide
ActivTrak Product Team
ActivTrak ActivConnect Overview
ActivConnect 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 ActivConnect 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 ActivConnect instance.
Follow the steps below to configure ActivTrak’s ActivConnect 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.
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].
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 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 3 parameters are relevant to your project that you just received from the ActivConnect setup.
- For other parameters:
- Timezone: Enter your relevant UTC timezone. (Number only. E.g. -6)
- Daylight Savings: This is "False" or "True" depending on if your location observes daylight savings 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 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
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).
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.
- Potential File Transfer 1
- Potential File Transfer 2
- Potential File Transfer 3
- Potential File Transfer 4
- Potential File Transfer 5
- Search Term Prefix
- Search Term Prefix Position
- Search Term Raw
- Search Term Length
- Search Term
- Search Term Clean
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
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
NOTE: Once the data has been connected to Power BI, either by way of our template, or your own custom built information, the report should be published to Power BI Online.
Once uploaded, go into the Dataset settings of the workspace for your new report, verify the credentials one more time to authorize Power BI Online permission into Google BigQuery(GBQ), and set up your scheduled refresh below that.
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 | 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 | 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
Comments
No comments