Tracking Excel Usage Telemetry in Regulated Industries: Solutions Post-Office Telemetry Dashboard

As of Office 365 version 2008, Microsoft has officially deprecated the Office Telemetry Dashboard, a tool that helped enterprises track Office usage and performance data. This leaves many organizations, especially in regulated industries like financial institutions, insurance companies, and healthcare, with a challenge. These industries require strict oversight of software usage for compliance, auditability, and risk management. Without the Telemetry Dashboard, enterprises must now explore alternative methods to track Excel usage effectively while meeting regulatory requirements.

In this blog post, we will explore practical solutions for enterprises in regulated industries to collect and monitor telemetry from their Excel projects, ensuring compliance and data governance, without relying on the deprecated Office Telemetry Dashboard.

Why Telemetry Tracking is Critical in Regulated Industries

For industries like finance, insurance, and healthcare, collecting detailed telemetry and usage data from Excel is crucial for several reasons:

1. Compliance with Regulations: Regulatory bodies, such as HIPAA (for healthcare) and FINRA or SOX (for finance), often require companies to track how sensitive data is accessed and used within applications like Excel.

2. Data Security and Audit Trails: It's important to ensure that data is being accessed and manipulated according to company policies, with detailed logs of any changes made to key documents.

3. Performance Monitoring and Risk Management: Tracking performance issues within Excel can help organizations optimize workflows and mitigate operational risks that stem from poor software performance or improper usage.

4. User Activity Monitoring: In certain high-security contexts, organizations need to know exactly who is interacting with which documents, what changes are being made, and how frequently these actions are occurring.

Given these needs, the deprecation of the Office Telemetry Dashboard requires businesses to seek out alternatives.

Solutions for Tracking Excel Usage Post-Office Telemetry Dashboard

1. Custom VBA Macros for In-Document Monitoring

One of the most straightforward ways to track activity within Excel documents is to use VBA (Visual Basic for Applications) macros. These macros allow you to write custom scripts that track and log user activity, such as opening documents, modifying cells, or making changes to sheets.

How it Works:

• Logging Events: VBA macros can track specific actions such as when a user opens a workbook, edits a cell, or saves a file. The macro can log these actions into a separate file (such as a CSV or text file) or send the data to an internal server for storage and further analysis.

• Embedding Security and Compliance Features: In highly regulated industries, these macros can also be used to prevent unauthorized actions or enforce access controls (e.g., locking specific sections of a workbook or logging unauthorized access attempts).

Example VBA Macro for Excel Telemetry:

Private Sub Workbook_Open()
    LogEvent "Workbook opened by " & Environ("username")
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    LogEvent "User " & Environ("username") & " modified " & Target.Address & " in sheet " & Sh.Name
End Sub

Sub LogEvent(EventDescription As String)
    Dim LogFile As String
    LogFile = ThisWorkbook.Path & "\activity_log.txt"
    Open LogFile For Append As #1
    Print #1, Now & ": " & EventDescription
    Close #1
End Sub

This script logs when the workbook is opened and when any cell is modified, along with a timestamp and the username of the person making the changes.

Pros:

• Easy to implement for specific files or projects.

• Fully customizable to track specific events.

• Does not require external software or third-party tools.

Cons:

• Only works within individual workbooks.

• Lacks centralization for large-scale deployments.

2. PowerShell and Python for System-Wide Monitoring

While VBA works well for tracking activity within individual Excel workbooks, PowerShell or Python can be used for broader, system-wide monitoring of Excel usage.

PowerShell for Excel Telemetry:

PowerShell scripts can monitor Excel usage across the organization by tracking file access, changes, and user actions. This method works well for regulated industries because it can be integrated into other compliance and security workflows.

• Monitoring File Access: Use PowerShell to monitor when specific Excel files are accessed, who accesses them, and what changes are made.

• Scheduling Reports: Set up regular reports on file usage and activity, making this method ideal for compliance audits.

# PowerShell script to log file access
$excelFiles = Get-ChildItem "C:\Path\To\Your\Excel\Files" -Filter *.xlsx
foreach ($file in $excelFiles) {
    $lastAccess = Get-ItemProperty $file.FullName | Select-Object LastAccessTime, LastWriteTime
    Write-Output "File: $($file.Name) - Last Accessed: $($lastAccess.LastAccessTime) - Last Modified: $($lastAccess.LastWriteTime)"
}

This script logs the last access and modification times for all Excel files in a specified directory.

Python for Enhanced Analytics:

Python, particularly using libraries like openpyxl or xlwings, can be used to interact with Excel files programmatically. You can create scripts that log interactions with Excel files, track changes over time, and send this data to a central database or dashboard for analysis.

Example with xlwings:

import xlwings as xw
from datetime import datetime

# Open the workbook
wb = xw.Book('your_file.xlsx')

# Log actions
with open('log.txt', 'a') as log_file:
    log_file.write(f"{datetime.now()}: Opened {wb.name}\n")

wb.close()

Pros:

• Works well for large-scale monitoring of file systems.

• Centralized logging possible by sending logs to a server or database.

• Cross-platform capabilities (Python is multi-platform, and PowerShell works on both Windows and Linux).

Cons:

• Requires some coding expertise.

• Can be resource-intensive if used for live monitoring.

3. Third-Party Auditing and Telemetry Tools

For organizations looking for out-of-the-box solutions, various third-party tools can help you monitor Excel usage while ensuring compliance with regulatory requirements.

Tools for Monitoring Excel Usage:

1. Vena Solutions: This tool is commonly used in financial institutions to track the use of Excel in financial models, offering real-time data collection and reporting for compliance and audit purposes.

2. Splunk or ELK (Elasticsearch, Logstash, Kibana): These logging and monitoring platforms can be set up to capture Excel usage across an enterprise by tracking file access, edits, and user interactions. They can ingest data from PowerShell or Python scripts and provide dashboards for compliance reporting.

3. Sysmon (Windows Sysinternals): Sysmon can log events related to file usage at the operating system level. You can configure Sysmon to monitor specific directories where critical Excel files are stored, ensuring that all actions (open, modify, delete) are logged.

Pros:

• Comprehensive monitoring and reporting features.

• Some tools offer dashboards that are ideal for compliance audits.

• Scalable for large organizations.

Cons:

• Can be expensive.

• Complexity in integrating with existing systems.

4. Integration with Cloud Platforms for Monitoring and Reporting

If your organization uses cloud services such as Azure or AWS, there are cloud-native solutions for tracking telemetry. Azure Monitor or AWS CloudWatch can be used to capture usage metrics for Excel and other Office apps in cloud-based environments.

• Azure Monitor: Can log and track Excel file usage in environments using OneDrive or SharePoint, providing insights into file usage patterns and compliance.

• Power Automate: Integrate Power Automate with SharePoint or OneDrive to trigger workflows when Excel files are accessed or modified, logging details into a central database or reporting system.

Pros:

• Centralized cloud-native solution for telemetry and reporting.

• Scalable for hybrid environments (on-prem and cloud).

Cons:

• Requires cloud infrastructure, not suitable for fully on-prem deployments.

• Integration complexity with existing on-prem apps.

Conclusion: A Custom Approach for Regulated Industries

The deprecation of the Office Telemetry Dashboard creates a gap for organizations in regulated industries, but a combination of open-source tools and custom solutions can fill this need. The choice of method depends on the specific requirements of the organization:

• Small to medium-scale projects: VBA macros or Python scripts can help track usage and compliance on a project-by-project basis.

• Large-scale, enterprise-wide tracking: PowerShell combined with system-wide monitoring tools like Sysmon or third-party platforms such as Splunk can provide centralized logging and compliance reporting.

• Cloud-based environments: Leverage cloud-native services such as Azure Monitor or Power Automate for tracking and logging file usage in cloud-hosted Excel files.

Each method allows organizations to maintain control over how they monitor and track Excel usage, ensuring compliance with industry regulations without relying on the now-deprecated Office Telemetry Dashboard.