Implementing Desktop Flow Exception Logging and Telemetry in Power Automate Desktop: Capturing Error Details, Writing Structured Logs to SQL or SharePoint, and Triggering Cloud Flow Alerts from Unattended Bot Failures

Introduction

Picture this: it's 2 AM, and your unattended bot fleet is quietly processing thousands of invoice records. One bot encounters a session timeout in your legacy ERP system, fails silently, and Power Automate's run history tells you nothing beyond "Failed." By morning, three hundred records are unprocessed, nobody was notified, and you're spending your Tuesday reverse-engineering what went wrong from a cryptic error code in a log table that half-captures the exception message. If you've lived this, you know exactly what's missing: structured, intentional telemetry baked into the automation itself.

This is an architecture problem disguised as an operations problem. Most Power Automate Desktop flows are built with the happy path in mind. Error handling gets bolted on at the end, usually as a single On Block Error wrapper that writes a generic failure message somewhere vaguely useful. That approach collapses at scale. When you have dozens of flows running concurrently across machine groups, you need to know which flow failed, at which step, with what data context, on which machine, and at what time — automatically, consistently, and in a format you can query.

By the end of this lesson, you will be able to design and implement a production-grade exception logging and telemetry architecture for Power Automate Desktop. Specifically:

What you'll learn:

• How to capture rich, structured error context in desktop flows including step location, error code, machine identity, and business transaction data
• How to write structured log records to both SQL Server and SharePoint Lists using desktop flow native actions and HTTP calls
• How to design a reusable logging subflow that standardizes telemetry across an entire bot fleet
• How to trigger cloud flow alerts from unattended bot failures using HTTP webhooks and the Power Automate cloud connector
• How to correlate desktop flow telemetry with Power Platform's built-in run history for end-to-end observability

---

Prerequisites

You should be comfortable with Power Automate Desktop at an advanced level before working through this material. Specifically, you should already understand:

• How On Block Error and retry policies work in desktop flows — if you need a refresher, the article on error handling in desktop flows is essential reading
• How to create and call subflows — covered in depth at subflows and reusable logic in Power Automate Desktop
• How desktop flows connect to cloud flows and pass data bidirectionally — see triggering desktop flows from cloud flows
• Basic SQL Server connectivity concepts and SharePoint List structure
• How unattended runs work, including machine configuration — covered in attended vs unattended RPA

---

Understanding What You Actually Need to Log

Before writing a single action, you need to be deliberate about what constitutes useful telemetry. The single biggest mistake in desktop flow logging is capturing too little context and the second biggest is capturing it inconsistently across flows.

A well-designed log record for a desktop flow failure should answer these questions:

Identity questions — What process failed, and where is that process running?

• Flow name
• Run ID (the GUID Power Automate assigns to each execution)
• Machine name and machine group name
• Environment name

Temporal questions — When did things happen?

• Flow start timestamp
• Error occurrence timestamp
• Duration elapsed at time of failure

Location questions — Where in the flow did execution break down?

• Subflow name (if the error occurred in a subflow)
• A user-defined "current step" marker that you update as the flow progresses
• The action type or block that triggered the error

Error questions — What actually went wrong?

• Error message text (the full string Power Automate surfaces)
• Error type or category (you'll define these)
• Stack or action context if available

Business context questions — What was the bot working on?

• The business key being processed — invoice number, customer ID, order reference
• The source system and target system
• Any relevant data values that explain the failure

Outcome questions — What happened after the error?

• Did the flow retry? How many times?
• Did recovery succeed?
• Was an alert sent?

Power Automate Desktop exposes some of these natively through the %LastError% variable and environment variables. Others you have to instrument yourself by updating tracking variables as the flow progresses. The architecture we'll build handles both categories.

---

Designing the Telemetry Variable Schema

The foundation of this architecture is a consistent variable schema you populate as the flow executes. Think of this as an in-memory log record that gets flushed to your destination (SQL, SharePoint, or an HTTP endpoint) whenever something noteworthy happens.

Create a Custom Object variable at the start of every instrumented flow. In Power Automate Desktop, you do this using the Set Variable action with a JSON-structured string that you'll parse, or more practically, you build it as a series of named variables grouped by prefix convention.

Here's the variable set I recommend initializing in a dedicated Initialize_Telemetry subflow:

# Execution identity
SET TelFlow_Name TO 'Invoice_Processing_Main'
SET TelFlow_Version TO '2.4'
SET TelFlow_RunId TO ''          # Populated from cloud flow input if triggered externally
SET TelMachine_Name TO ''        # Will be populated via PowerShell
SET TelEnvironment TO 'Production'

# Business context
SET TelBusiness_Key TO ''        # Populated per iteration: Invoice number, Order ID, etc.
SET TelSource_System TO 'SAP ECC'
SET TelTarget_System TO 'SharePoint'
SET TelBatch_Id TO ''            # A GUID generated at flow start for correlation

# Execution tracking
SET TelStart_Time TO ''          # Populated via Get Current Date/Time
SET TelCurrent_Step TO 'Initialization'
SET TelStep_Counter TO 0

# Error capture
SET TelError_Message TO ''
SET TelError_Type TO ''
SET TelError_Severity TO 'INFO'  # INFO, WARNING, ERROR, CRITICAL
SET TelError_Subflow TO ''

# Outcome
SET TelStatus TO 'RUNNING'
SET TelRetry_Count TO 0
SET TelItems_Processed TO 0
SET TelItems_Failed TO 0

The TelCurrent_Step variable is the secret weapon here. You update it with a descriptive string at the beginning of each major block — before you open the application, before each data transformation, before each write operation. When an error fires, TelCurrent_Step tells you exactly where in the business process the failure occurred, even if the underlying PAD error message is cryptic.

For machine name, use a Run PowerShell action immediately after initialization:

Write-Output $env:COMPUTERNAME

Capture the output into TelMachine_Name. If you're running on a machine group, this gives you the specific bot machine that executed the run, which is invaluable for diagnosing environment-specific failures.

Similarly, generate a batch ID using PowerShell:

Write-Output ([System.Guid]::NewGuid().ToString())

Store this in TelBatch_Id. When you're processing a queue of 500 invoices in a single run, this ID ties all log records from that execution together in your reporting queries. You can read more about scripting inside desktop flows with PowerShell if this pattern is new to you.

---

Building the Reusable Logging Subflow

Now that your variable schema is defined, encapsulate all logging behavior into a subflow called Log_Telemetry_Event. This subflow will be called from everywhere in your automation — both in error handlers and at normal checkpoints.

The subflow accepts the following inputs via the calling context (the shared variables we defined above are already in scope, plus you'll set severity and override fields before calling):

# Caller sets these before invoking the subflow:
SET TelError_Severity TO 'ERROR'           # or INFO, WARNING, CRITICAL
SET TelError_Message TO %LastError%        # captured immediately
SET TelError_Type TO 'SAP_Timeout'         # business-defined category
SET TelError_Subflow TO 'Open_SAP_Session' # which subflow the error occurred in

Inside Log_Telemetry_Event, the first action is always:

# 1. Capture precise timestamp
GET CURRENT DATE AND TIME → TelEvent_Timestamp

# 2. Build the log record as a structured JSON string
SET LogRecord TO $'{{
  "BatchId": "{TelBatch_Id}",
  "RunId": "{TelFlow_RunId}",
  "FlowName": "{TelFlow_Name}",
  "FlowVersion": "{TelFlow_Version}",
  "MachineName": "{TelMachine_Name}",
  "Environment": "{TelEnvironment}",
  "BusinessKey": "{TelBusiness_Key}",
  "SourceSystem": "{TelSource_System}",
  "TargetSystem": "{TelTarget_System}",
  "EventTimestamp": "{TelEvent_Timestamp}",
  "CurrentStep": "{TelCurrent_Step}",
  "StepCounter": {TelStep_Counter},
  "Severity": "{TelError_Severity}",
  "ErrorMessage": "{TelError_Message}",
  "ErrorType": "{TelError_Type}",
  "ErrorSubflow": "{TelError_Subflow}",
  "Status": "{TelStatus}",
  "RetryCount": "{TelRetry_Count}",
  "ItemsProcessed": {TelItems_Processed},
  "ItemsFailed": {TelItems_Failed}
}}'

The $'...' syntax is Power Automate Desktop's string interpolation format. This builds your entire log payload as a single JSON string that you can pass to SQL, SharePoint, or an HTTP endpoint without further transformation.

After building the payload, the subflow routes to two destinations depending on configuration: a SQL write path and/or an HTTP notification path. We'll build both.

---

Writing Structured Logs to SQL Server

Direct SQL connectivity from desktop flows is one of the most reliable logging destinations because it offers transactional guarantees, rich query capabilities, and integrates easily with existing monitoring dashboards like Power BI or SSMS.

Setting Up the Log Table

First, create the log table in your SQL Server instance. Here's the DDL:

CREATE TABLE dbo.RPA_FlowTelemetry (
    LogId           BIGINT IDENTITY(1,1) PRIMARY KEY,
    BatchId         UNIQUEIDENTIFIER NOT NULL,
    RunId           NVARCHAR(100) NULL,
    FlowName        NVARCHAR(200) NOT NULL,
    FlowVersion     NVARCHAR(20) NULL,
    MachineName     NVARCHAR(100) NULL,
    Environment     NVARCHAR(50) NULL,
    BusinessKey     NVARCHAR(500) NULL,
    SourceSystem    NVARCHAR(100) NULL,
    TargetSystem    NVARCHAR(100) NULL,
    EventTimestamp  DATETIME2 NOT NULL,
    CurrentStep     NVARCHAR(200) NULL,
    StepCounter     INT NULL,
    Severity        NVARCHAR(20) NOT NULL,
    ErrorMessage    NVARCHAR(MAX) NULL,
    ErrorType       NVARCHAR(100) NULL,
    ErrorSubflow    NVARCHAR(200) NULL,
    Status          NVARCHAR(50) NULL,
    RetryCount      INT NULL,
    ItemsProcessed  INT NULL,
    ItemsFailed     INT NULL,
    CreatedAt       DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

CREATE INDEX IX_RPA_FlowTelemetry_BatchId ON dbo.RPA_FlowTelemetry (BatchId);
CREATE INDEX IX_RPA_FlowTelemetry_FlowName_Severity ON dbo.RPA_FlowTelemetry (FlowName, Severity, EventTimestamp DESC);
CREATE INDEX IX_RPA_FlowTelemetry_BusinessKey ON dbo.RPA_FlowTelemetry (BusinessKey) WHERE BusinessKey IS NOT NULL;

The indexes matter. When you're querying "show me all CRITICAL errors from the Invoice flow in the last 24 hours" against a table with 2 million rows, you'll be grateful for IX_RPA_FlowTelemetry_FlowName_Severity.

Connecting and Writing from Desktop Flow

In your Log_Telemetry_Event subflow, add a SQL connection block. Use the Open SQL Connection action:

• Connection string: Use a DSN or a full connection string. For SQL Server with Windows authentication: Server=SQLPROD01;Database=RPA_Monitoring;Trusted_Connection=True;
• If you're using SQL authentication, store credentials in Azure Key Vault and retrieve them dynamically — see handling credentials securely in desktop flows for the pattern.

After opening the connection (stored in SQLConnection), execute an insert:

# Build the INSERT statement
SET SQLInsert TO $"INSERT INTO dbo.RPA_FlowTelemetry 
(BatchId, RunId, FlowName, FlowVersion, MachineName, Environment, 
 BusinessKey, SourceSystem, TargetSystem, EventTimestamp, CurrentStep, 
 StepCounter, Severity, ErrorMessage, ErrorType, ErrorSubflow, Status, 
 RetryCount, ItemsProcessed, ItemsFailed)
VALUES 
('{TelBatch_Id}', '{TelFlow_RunId}', '{TelFlow_Name}', '{TelFlow_Version}',
 '{TelMachine_Name}', '{TelEnvironment}', '{TelBusiness_Key}', 
 '{TelSource_System}', '{TelTarget_System}', '{TelEvent_Timestamp}',
 '{TelCurrent_Step}', {TelStep_Counter}, '{TelError_Severity}',
 '{TelError_Message_Escaped}', '{TelError_Type}', '{TelError_Subflow}',
 '{TelStatus}', {TelRetry_Count}, {TelItems_Processed}, {TelItems_Failed})"

# Execute the statement
EXECUTE SQL STATEMENT SQLInsert ON CONNECTION SQLConnection

# Close connection
CLOSE SQL CONNECTION SQLConnection

The TelError_Message_Escaped variable should be a version of TelError_Message where single quotes are replaced with two single quotes (the SQL escaping convention). Do this with a Replace Text action before building the insert: replace ' with ''.

For the database query and record updates pattern, you may prefer parameterized queries via stored procedures, which sidestep injection risk entirely:

-- Create this stored procedure in SQL Server
CREATE PROCEDURE dbo.usp_InsertRPATelemetry
    @BatchId UNIQUEIDENTIFIER, @RunId NVARCHAR(100), @FlowName NVARCHAR(200),
    -- ... all parameters
    @ErrorMessage NVARCHAR(MAX)
AS
BEGIN
    INSERT INTO dbo.RPA_FlowTelemetry (...) VALUES (@BatchId, @RunId, ...);
END

Then call it from the desktop flow using EXECUTE PROCEDURE syntax. Stored procedures eliminate the single-quote escaping problem entirely and give your DBA team a clean interface to the log table.

---

Writing Structured Logs to SharePoint Lists

Not every organization has a readily accessible SQL Server. SharePoint Lists are a viable alternative for teams already in the Microsoft 365 ecosystem — they offer no-code reporting through Power BI or built-in list views, and access control is simpler to manage.

SharePoint List Schema

Create a SharePoint List called RPA_FlowTelemetry with these columns:

Column Name	Type	Notes
Title	Single line text	Use for FlowName
BatchId	Single line text
RunId	Single line text
MachineName	Single line text
Environment	Choice	Production, Staging, Dev
BusinessKey	Single line text	Indexed
EventTimestamp	Date and Time	Include time
CurrentStep	Single line text
Severity	Choice	INFO, WARNING, ERROR, CRITICAL
ErrorMessage	Multiple lines text
ErrorType	Single line text
Status	Choice	RUNNING, SUCCESS, FAILED, RETRYING
ItemsProcessed	Number
ItemsFailed	Number

Using HTTP Calls to Write to SharePoint

Desktop flows can write to SharePoint via the SharePoint REST API using the Invoke Web Service action. This is more reliable than the SharePoint-specific desktop actions for logging scenarios because it works in unattended mode without browser dependency.

In your Log_Telemetry_Event subflow, add an Invoke Web Service block:

URL: https://yourtenant.sharepoint.com/sites/RPAMonitoring/_api/web/lists/getbytitle('RPA_FlowTelemetry')/items

Method: POST

Headers:
  Accept: application/json;odata=verbose
  Content-Type: application/json;odata=verbose
  X-RequestDigest: {FormDigestValue}   # See note below

Body (use the LogRecord JSON variable, structured for SP REST):
{
  "__metadata": { "type": "SP.Data.RPA_FlowTelemetryListItem" },
  "Title": "{TelFlow_Name}",
  "BatchId": "{TelBatch_Id}",
  "MachineName": "{TelMachine_Name}",
  "Severity": "{TelError_Severity}",
  "ErrorMessage": "{TelError_Message}",
  "CurrentStep": "{TelCurrent_Step}",
  "EventTimestamp": "{TelEvent_Timestamp}",
  "BusinessKey": "{TelBusiness_Key}",
  "Status": "{TelStatus}"
}

The SharePoint REST API requires a form digest value for write operations. Obtain it by making a preliminary POST call to /_api/contextinfo and extracting FormDigestValue from the response JSON. Do this once at the start of your session (not on every log write), and store it in SharePointDigest. You'll need to refresh it after 30 minutes if your flow runs that long.

For simpler organizations, the most pragmatic SharePoint logging pattern is to not write directly from the desktop flow to SharePoint at all. Instead, write to a local CSV or JSON file as a buffer, then at the end of the run (or in a scheduled sweep), trigger a cloud flow that reads the buffer file and writes all records to SharePoint in batch. This eliminates network latency from your automation's critical path and makes the logging more resilient to SharePoint connectivity blips. See reading and writing to CSV and text files in Power Automate Desktop for the file buffering pattern.

---

Triggering Cloud Flow Alerts from Unattended Bot Failures

Getting log records into a database is necessary but not sufficient for operational awareness. You also need active alerting — someone needs to be notified when a CRITICAL failure occurs, not when they remember to check the monitoring dashboard at 9 AM.

The architecture for this has three components:

1. Desktop flow detects a critical failure and captures full error context
2. Desktop flow calls an HTTP endpoint (a cloud flow HTTP trigger) passing the structured payload
3. Cloud flow processes the alert — sends Teams/email notifications, creates incidents in your ITSM, or triggers remediation flows

Building the Alert Cloud Flow

In Power Automate cloud, create a new Instant flow with an HTTP Request trigger. Set the request body JSON schema to match your telemetry payload:

{
  "type": "object",
  "properties": {
    "BatchId": { "type": "string" },
    "FlowName": { "type": "string" },
    "MachineName": { "type": "string" },
    "BusinessKey": { "type": "string" },
    "ErrorMessage": { "type": "string" },
    "ErrorType": { "type": "string" },
    "CurrentStep": { "type": "string" },
    "Severity": { "type": "string" },
    "EventTimestamp": { "type": "string" },
    "Environment": { "type": "string" }
  }
}

After the trigger, add a Condition action: if Severity is CRITICAL or ERROR, proceed with alerting. This lets you use the same endpoint for all telemetry events but filter alerting behavior in the cloud flow.

For the alert itself, add a Post message in a chat or channel (Teams) action targeting your RPA Operations channel:

🚨 **RPA Bot Failure Alert**
**Flow:** @{triggerBody()?['FlowName']} v@{triggerBody()?['FlowVersion']}
**Machine:** @{triggerBody()?['MachineName']}
**Environment:** @{triggerBody()?['Environment']}
**Failed At Step:** @{triggerBody()?['CurrentStep']}
**Business Key:** @{triggerBody()?['BusinessKey']}
**Error Type:** @{triggerBody()?['ErrorType']}
**Error Detail:** @{triggerBody()?['ErrorMessage']}
**Batch ID:** @{triggerBody()?['BatchId']}
**Timestamp:** @{triggerBody()?['EventTimestamp']}

Add a secondary branch to log the alert to your SharePoint list or SQL table (even if the desktop flow already did this — the cloud flow write serves as confirmation that the alert was processed, and it adds a AlertSent flag you can track).

Optionally, add a Create a work item (Azure DevOps) or Create incident (ServiceNow connector) action for CRITICAL failures so your ops team gets a trackable ticket automatically.

Calling the HTTP Trigger from Desktop Flow

Back in your Log_Telemetry_Event subflow, after the SQL write, add a conditional branch: if TelError_Severity equals ERROR or CRITICAL, invoke the HTTP endpoint:

URL: https://prod-123.westus.logic.azure.com:443/workflows/abc123.../triggers/manual/paths/invoke?...
  (The full URL from your HTTP trigger's "When a HTTP request is received" action)

Method: POST

Headers:
  Content-Type: application/json

Body: {LogRecord}   # The JSON string you already built

Use the Invoke Web Service action. Set Connection Timeout to 30 seconds and enable Follow Redirects. Wrap this call in its own On Block Error handler that swallows failures — if your alerting infrastructure is down, you don't want the error handler itself to crash.

This pattern — desktop flow calling cloud flow via HTTP — gives you access to the entire Power Platform connector ecosystem from your unattended bot. You can trigger email sends, Teams notifications, ServiceNow incidents, PagerDuty alerts, and more without any of that logic living in the desktop flow itself. The desktop flow stays focused on automation; the cloud flow handles the notification orchestration.

---

Instrumenting the Main Flow: Putting It All Together

Here's how the instrumented flow skeleton looks in practice. This is the main flow for an unattended invoice processing bot:

CALL Initialize_Telemetry
  → Sets all Tel* variables, gets machine name, generates batch ID, logs INFO start event

SET TelCurrent_Step TO 'SAP_Application_Launch'
SET TelStep_Counter TO TelStep_Counter + 1

ON BLOCK ERROR
  GOTO Error_Handler
END ON BLOCK ERROR

  # Open SAP, log in, navigate to transaction
  CALL Open_SAP_Session
  
  SET TelCurrent_Step TO 'Queue_Read'
  SET TelStep_Counter TO TelStep_Counter + 1
  
  CALL Read_Invoice_Queue  # Returns InvoiceList datatable
  
  LOOP FOR EACH CurrentInvoice IN InvoiceList
  
    SET TelBusiness_Key TO CurrentInvoice['InvoiceNumber']
    SET TelCurrent_Step TO 'Invoice_Header_Read'
    SET TelStep_Counter TO TelStep_Counter + 1
    
    ON BLOCK ERROR (Label: 'InvoiceError')
      SET TelError_Message TO %LastError%   # MUST be first
      SET TelError_Type TO 'Invoice_Processing_Error'
      SET TelError_Severity TO 'ERROR'
      SET TelError_Subflow TO TelCurrent_Step
      SET TelItems_Failed TO TelItems_Failed + 1
      CALL Log_Telemetry_Event
      GOTO NEXT ITERATION  # Skip this invoice, continue the loop
    END ON BLOCK ERROR
    
    CALL Process_Invoice (CurrentInvoice)
    
    SET TelItems_Processed TO TelItems_Processed + 1
    SET TelError_Severity TO 'INFO'
    SET TelError_Message TO 'Invoice processed successfully'
    CALL Log_Telemetry_Event   # Log success events too — they populate your throughput metrics
    
  END LOOP
  
  SET TelStatus TO 'SUCCESS'
  SET TelError_Severity TO 'INFO'
  CALL Log_Telemetry_Event   # Final completion record

LABEL Error_Handler:
  SET TelError_Message TO %LastError%
  SET TelError_Type TO 'Critical_Flow_Failure'
  SET TelError_Severity TO 'CRITICAL'
  SET TelStatus TO 'FAILED'
  CALL Log_Telemetry_Event
  
  STOP FLOW WITH ERROR

Notice the layered error handling strategy. The inner ON BLOCK ERROR with GOTO NEXT ITERATION handles per-invoice failures gracefully — one bad invoice doesn't stop the entire batch. The outer ON BLOCK ERROR at the flow level catches failures that should terminate processing (SAP session crash, database unavailable, etc.) and marks them as CRITICAL.

This pattern aligns with what's discussed in error handling in desktop flows — the key addition here is that every error path funnels through your Log_Telemetry_Event subflow with the right severity and context, making every failure searchable and alertable rather than just terminal.

---

Correlating Desktop Flow Telemetry with Power Platform Run History

Your custom telemetry is valuable, but Power Platform also captures run history natively. The goal is to correlate the two so that when you pull up a run in the Power Automate portal, you can immediately find the corresponding detailed log records.

The RunId is the key. When a desktop flow is triggered by a cloud flow, the cloud flow can pass its own run ID as an input to the desktop flow. Capture this in TelFlow_RunId during initialization. Now every telemetry record in your SQL table can be joined back to the exact cloud flow run in the portal.

If your desktop flow runs directly (scheduled or triggered from the machine), the run ID isn't natively available inside the flow. In this case, use the TelBatch_Id GUID you generate at startup as your correlation key. When investigating a failure, search for the batch ID in your telemetry table and cross-reference the timestamp against the Power Automate run history.

For teams with multiple bot machines and dozens of concurrent flows, the monitoring and troubleshooting desktop flow runs at scale article covers the built-in portal tools — combine those with your custom telemetry for full observability.

---

Advanced Pattern: Telemetry Buffering for High-Throughput Flows

When a desktop flow processes thousands of records in a single run, synchronous SQL writes on every log event can add meaningful overhead. At 50ms per write and 5000 records with success/failure logging, you're adding over 4 minutes to your run time — and creating significant load on your SQL Server.

The solution is to buffer telemetry in-memory during the run and flush in batches. Use a Power Automate Desktop Data Table as your in-memory buffer:

# Initialize buffer at flow start
CREATE DATA TABLE TelemetryBuffer
  WITH COLUMNS: BatchId, EventTimestamp, BusinessKey, Severity, 
                CurrentStep, ErrorMessage, ErrorType, Status

# In your log subflow, instead of writing to SQL:
ADD ROW TO TelemetryBuffer WITH VALUES: 
  (TelBatch_Id, TelEvent_Timestamp, TelBusiness_Key, TelError_Severity, 
   TelCurrent_Step, TelError_Message, TelError_Type, TelStatus)

# Every N records (e.g., every 100), flush:
IF TelItems_Processed MOD 100 = 0 THEN
  CALL Flush_Telemetry_Buffer
END IF

# Always flush at end of run and in error handler
CALL Flush_Telemetry_Buffer

The Flush_Telemetry_Buffer subflow iterates through TelemetryBuffer, builds a multi-row INSERT statement, executes it as a single SQL call, then clears the buffer. This batches 100 database writes into 1, reducing overhead by roughly 99%.

The critical design consideration: CRITICAL and ERROR severity events should bypass the buffer and write immediately. You don't want an alert-worthy failure sitting in a buffer if the flow crashes before the next flush cycle. Use a conditional in your log subflow:

IF TelError_Severity IS IN ['CRITICAL', 'ERROR']
  WRITE TO SQL IMMEDIATELY
  CALL HTTP_Alert
ELSE
  ADD TO TelemetryBuffer
END IF

Variables, data tables, and the patterns for working with them efficiently are covered in detail in variables, lists, and data tables in Power Automate Desktop.

---

Security Considerations for Telemetry Infrastructure

Your telemetry system handles potentially sensitive data — error messages from invoice processing may contain amounts, customer IDs, or personally identifiable information. Design with these constraints:

SQL connection security: Use a dedicated SQL login with INSERT-only permissions on dbo.RPA_FlowTelemetry and nothing else. The bot account should never have SELECT rights to this table from desktop flows — that's for your monitoring dashboards, not your automation runtime.

Sanitize before logging: Before writing TelBusiness_Key or TelError_Message to any log store, consider whether those values contain PII. For GDPR/CCPA compliance, you may need to hash customer identifiers rather than store them in plaintext: use a PowerShell [System.Security.Cryptography.SHA256] call to hash the value, log the hash, and keep a separate (secured) mapping table.

Alert payload scrubbing: The HTTP payload sent to your cloud flow alert goes over the internet. Remove or mask any sensitive field values from the alert notification — the Teams message doesn't need the full customer name or invoice amount, just enough context for the ops team to identify and route the issue.

Log retention: Telemetry tables grow fast. Implement a SQL Agent job or scheduled Power Automate flow that archives records older than 90 days to cheaper storage (Azure Blob as Parquet, for instance) and deletes from the hot table. Without this, your telemetry query performance degrades over months.

---

Hands-On Exercise

In this exercise, you'll build a complete logging infrastructure for a simulated invoice validation flow and test it end-to-end.

Step 1: Create the SQL infrastructure Deploy the dbo.RPA_FlowTelemetry table and all three indexes from the DDL provided earlier. Create a dedicated SQL login rpa_logger with INSERT-only permissions on that table.

Step 2: Build the Initialize_Telemetry subflow Create a new desktop flow called EXERCISE_InvoiceValidator. Add a subflow called Initialize_Telemetry that sets all 15+ Tel* variables, retrieves the machine name via PowerShell, and generates a GUID batch ID via PowerShell.

Step 3: Build the Log_Telemetry_Event subflow Create the logging subflow that builds the JSON string, escapes special characters in the error message, opens a SQL connection using your rpa_logger credentials, inserts the record, and closes the connection. Wrap the entire SQL block in an On Block Error that logs to a local text file as fallback (so logging never crashes the main flow).

Step 4: Instrument the main flow Build a simple main flow that reads a hardcoded list of 5 invoice numbers, pretends to process each one, and deliberately fails on invoice number 3 using a Raise Error action with message "Duplicate invoice number detected: INV-10003". Before the error, set TelBusiness_Key to "INV-10003" and TelCurrent_Step to "Validation_DuplicateCheck".

Step 5: Build the alert cloud flow Create an HTTP-triggered cloud flow. Set the JSON schema. Add a Teams message action. Copy the HTTP trigger URL (store it in a text file, don't hardcode it yet). In your desktop flow's error handler, add an Invoke Web Service call to this URL.

Step 6: Run and verify Execute the flow in attended mode. Check your SQL table — you should see 5 records (2 INFO successes, 1 ERROR for INV-10003, 1 INFO skip, 1 CRITICAL flow-level error if you structured it that way). Check your Teams channel for the alert. Query the table to verify all fields populated correctly.

Bonus challenge: Add the telemetry buffer pattern. Change the success events to write to a DataTable buffer and flush at the end of the loop. Verify the final SQL record count is the same but the timing is faster.

---

Common Mistakes & Troubleshooting

Mistake: Capturing %LastError% too late If you add any action between the error trigger and your SET TelError_Message TO %LastError%, you risk overwriting the original error. The %LastError% capture must be the unconditional first action inside every error handler. Even a GET CURRENT DATE AND TIME action can theoretically generate its own error and replace the value.

Mistake: Not escaping special characters in the JSON payload Single quotes crash SQL inserts. Double quotes break JSON strings. Ampersands and angle brackets can corrupt SharePoint REST calls. Build a dedicated Sanitize_For_Log subflow that runs all text fields through Replace Text before they touch your log record builder. Characters to handle: ' → '' (SQL), " → \" (JSON), & → %26 (URL contexts).

Mistake: Logging only on error, never on success Your telemetry is useless for capacity planning, throughput analysis, or SLA reporting if you only capture failures. Log INFO events at the start and end of each major business transaction. Even a simple "Invoice INV-10001 processed in 4.2 seconds" record gives you the data to build throughput dashboards and detect performance degradation before it becomes failure.

Mistake: Using a single outer error handler for everything One On Block Error at the flow level means every failure — whether it's a per-record validation issue or a catastrophic session crash — gets the same treatment. Tier your error handlers: per-item handlers that skip and continue, per-phase handlers that retry the phase, and a global handler that terminates and alerts. The severity mapping (INFO/WARNING/ERROR/CRITICAL) should align with these tiers.

Mistake: The alert cloud flow itself fails silently HTTP calls from desktop flows succeed or fail based on HTTP response code. A 200 response means the cloud flow accepted the trigger — it doesn't mean the Teams message was sent successfully. Add a response action to your cloud flow that returns a meaningful status, and check for it in your desktop flow. If you get a non-200 response, write the alert failure to your SQL log (use a separate connection and a simple flag column).

Troubleshooting: SQL inserts succeed but data looks wrong Check your datetime format. Power Automate Desktop's Get Current Date and Time produces a locale-formatted string that SQL Server may misinterpret depending on server locale settings. Use PowerShell to generate ISO 8601 formatted timestamps: Get-Date -Format 'yyyy-MM-ddTHH:mm:ss.fffZ'. Store the result in TelEvent_Timestamp for guaranteed SQL compatibility.

Troubleshooting: SharePoint REST API returns 403 Forbidden The form digest has expired (after 30 minutes) or was generated for a different site collection. Implement a Refresh_SharePoint_Digest subflow that re-requests /_api/contextinfo and updates SharePointDigest. Call it every 20 minutes in long-running flows.

Troubleshooting: HTTP alert calls timing out during high-load runs If your bot is under load and the cloud flow HTTP trigger is slow, your logging subflow may block the main flow for 30+ seconds per timeout. Set a short timeout (10 seconds) on the Invoke Web Service call for alerts, catch the timeout error, and fall back to writing the alert details to a local "pending alerts" file. A separate cleanup flow can process these files and retry the notifications.

---

Summary & Next Steps

You've now built a production-grade telemetry system for Power Automate Desktop. The key architectural elements are:

• A consistent variable schema that captures identity, location, business context, and error details for every noteworthy event
• A reusable Log_Telemetry_Event subflow that all flows in your fleet call rather than each implementing their own logging logic
• SQL Server as the primary log store with a well-indexed schema that supports both real-time alerting queries and long-term trend analysis
• SharePoint as an alternative for organizations without accessible SQL Server, using REST API writes or file-buffered batch uploads
• Cloud flow HTTP triggers as the alert backbone, decoupling notification logic from automation logic and giving you access to the full Power Platform connector ecosystem
• Tiered error handling that distinguishes per-item failures from flow-level failures and routes each to the appropriate severity and response
• Telemetry buffering for high-throughput flows where synchronous logging would impair performance

This telemetry infrastructure is most valuable when it's standardized across your entire bot fleet. The next step is to package Initialize_Telemetry and Log_Telemetry_Event as a shared solution that all teams in your organization deploy from a single managed source. When a new flow is built, it imports these subflows and immediately inherits your full logging and alerting capability.

From here, consider building a Power BI report on top of dbo.RPA_FlowTelemetry that shows daily throughput, error rates by flow and machine, MTTR (mean time to recovery) for each error type, and SLA attainment by process. With structured telemetry in place, this goes from impossible to straightforward.

For the orchestration layer above this — queue management, machine load balancing, and automated recovery — the building a resilient unattended RPA orchestration framework article picks up exactly where this one leaves off.