Building a Daily Chatbot Transcript Archiver with Power Automate

How to automatically export Copilot conversation transcripts from Dataverse to SharePoint as a daily CSV.

Overview

This Power Automate cloud flow ("Daily [Assistant] Transcripts to SharePoint") runs once a day, pulls the previous day's chatbot conversation transcripts out of Dataverse, flattens each message into a CSV row, and drops a single dated .csv file into a SharePoint document library. It's a clean pattern for archiving conversational AI logs for analytics or compliance.

Flow Architecture

Recurrence (daily 6:00 AM)
   v
Initialize variable  -> csvContent (CSV header)
   v
List rows            -> Dataverse: conversationtranscripts (yesterday only)
   v
Apply to each        -> over each transcript record
   |- Compose         -> extract bot name
   \- Condition (If)   -> only "[Assistant Name]" transcripts
        |- Filter array -> keep only "message" activities
        |- Select       -> map each message to a CSV row
        \- Append to string variable -> add rows to csvContent
   v
Create file          -> SharePoint: ChatTranscripts_<yesterday>.csv

Step 1 - Trigger: Recurrence

A scheduled trigger fires the flow once per day.

Setting	Value
Interval	1
Frequency	Day
Time zone	Pacific Standard Time
At these hours	6
At these minutes	0

Preview: Runs at 6:00 every day.

{
  "type": "Recurrence",
  "recurrence": {
    "interval": 1,
    "frequency": "Day",
    "timeZone": "Pacific Standard Time",
    "schedule": { "hours": [ "6" ], "minutes": [ 0 ] }
  }
}

Step 2 - Initialize variable (CSV header)

A string variable csvContent is seeded with the CSV header row. decodeUriComponent('%0A') injects a real newline character so the next rows start on a fresh line.

• Name: csvContent
• Type: String
• Value (fx):

@concat('AgentName,Session,ConversationStartTime,Role,MessageText,Timestamp', decodeUriComponent('%0A'))

Step 3 - List rows (Dataverse)

Retrieves transcript records from the Dataverse conversationtranscripts table, scoped to yesterday using an OData $filter.

• Table name: conversationtranscripts
• Filter rows (fx):

conversationstarttime ge @{startOfDay(addDays(utcNow(),-1))} and conversationstarttime lt @{startOfDay(utcNow())}

startOfDay(addDays(utcNow(),-1)) = start of yesterday; startOfDay(utcNow()) = start of today. Together they form a clean 24-hour window for the prior day.

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "entityName": "conversationtranscripts",
      "$filter": "conversationstarttime ge @{startOfDay(addDays(utcNow(),-1))} and conversationstarttime lt @{startOfDay(utcNow())}"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps",
      "connection": "[REDACTED - connection reference]",
      "operationId": "ListRecords"
    }
  }
}

Step 4 - Apply to each (per transcript)

Loops over every record returned by List rows: @outputs('List_rows')?['body/value'].

4a - Compose (extract bot name)

Pulls the friendly (formatted) name of the bot tied to the transcript:

@items('Apply_to_each')?['_bot_conversationtranscriptid_value@OData.Community.Display.V1.FormattedValue']

4b - Condition (If)

Only process transcripts that belong to the target assistant:

@equals(outputs('Compose'), '[Assistant Name]')

If true, the three actions below run. If false, nothing happens for that record.

Filter array - keep only chat messages

The transcript content is JSON; parse it, grab the activities array, and keep only items whose type is message (dropping system/event entries).

• From (fx): @json(items('Apply_to_each')?['content'])?['activities']
• Where (fx): @equals(item()?['type'], 'message')

Select - map each message to a CSV row

Builds one quoted, comma-separated CSV line per message. Note the careful quote-escaping (" -> ""), role mapping, timestamp conversion from Unix epoch seconds, and newline stripping inside message text.

• From (fx): @body('Filter_array')
• Map / select (fx):

@concat(
  '"', replace(outputs('Compose'), '"', '""'), '","',
  replace(string(items('Apply_to_each')?['conversationtranscriptid']), '"', '""'), '","',
  formatDateTime(items('Apply_to_each')?['conversationstarttime'], 'dd MMM yyyy HH:mm'), '","',
  if(equals(string(item()?['from']?['role']), '1'), 'User', 'Bot'), '","',
  replace(replace(replace(coalesce(item()?['text'], ''), '"', '""'), decodeUriComponent('%0D'), ''), decodeUriComponent('%0A'), ' '), '","',
  formatDateTime(addSeconds('1970-01-01T00:00:00Z', int(string(item()?['timestamp']))), 'dd MMM yyyy HH:mm:ss'), '"'
)

What the formula does, field by field:

• AgentName - bot name from Compose, double-quotes escaped.
• Session - conversationtranscriptid, double-quotes escaped.
• ConversationStartTime - formatted dd MMM yyyy HH:mm.
• Role - message from/role; "1" -> User, otherwise Bot.
• MessageText - coalesce(..., '') guards nulls; quotes escaped; carriage returns (%0D) removed and line feeds (%0A) replaced with a space so each message stays on one CSV line.
• Timestamp - Unix epoch seconds converted to a date via addSeconds('1970-01-01T00:00:00Z', ...), formatted to the second.

Append to string variable - accumulate rows

Joins the row array with newlines and appends to csvContent; the if(empty(...)) guard avoids adding a stray blank line when a transcript has no messages.

• Name: csvContent
• Value (fx):

@if(empty(body('Select')), '', concat(join(body('Select'), decodeUriComponent('%0A')), decodeUriComponent('%0A')))

Step 5 - Create file (SharePoint)

Writes the fully assembled CSV to a SharePoint library with a date-stamped filename.

Parameter	Value
Site Address	[REDACTED - SharePoint site URL]
Folder Path	[REDACTED - document library path]
File Name (fx)	ChatTranscripts_@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}.csv
File Content (fx)	@{variables('csvContent')}

Transfer mode is Chunked to support larger files.

{
  "type": "OpenApiConnection",
  "inputs": {
    "parameters": {
      "dataset": "[REDACTED - SharePoint site URL]",
      "folderPath": "[REDACTED - document library path]",
      "name": "ChatTranscripts_@{formatDateTime(addDays(utcNow(),-1),'yyyy-MM-dd')}.csv",
      "body": "@{variables('csvContent')}"
    },
    "host": {
      "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline",
      "connection": "[REDACTED - connection reference]",
      "operationId": "CreateFile"
    }
  },
  "runtimeConfiguration": { "contentTransfer": { "transferMode": "Chunked" } }
}

Key Takeaways

• Time-windowing with startOfDay + addDays gives a reliable "yesterday only" query without hardcoding dates.
• Build CSV by hand with Select + concat when you need precise control over quoting and escaping; always escape " -> "" and strip %0D/%0A from free-text fields.
• coalesce + if(empty(...)) guards prevent null errors and stray blank lines.
• Unix-epoch timestamps convert cleanly via addSeconds('1970-01-01T00:00:00Z', int(...)).
• Chunked transfer mode on Create file keeps large exports reliable.

Automate Azure PIM Role Activation Using GitHub Actions and OIDC (No Secrets Needed)

Tags: Azure PIM | GitHub Actions | OIDC | Microsoft Graph | PowerShell | DevOps | M365 Level: Intermediate — Azure AD, GitHub Actions, PowerShell

---

Introduction

Azure Privileged Identity Management (PIM) lets you assign roles on a just-in-time basis — users activate roles only when needed, reducing your attack surface. But what if you want to automate PIM role assignment from a CI/CD pipeline without storing any credentials?

In this article, I walk through the complete end-to-end implementation I built:

• A GitHub Actions workflow that lints and runs a PowerShell script
• The script uses OIDC (OpenID Connect) to authenticate to Azure — zero stored secrets
• It reads eligible PIM roles for a target user and creates adminAssign requests via Microsoft Graph
• The workflow has automatic retry logic and reports final pass/fail status

Everything in this article is based on a real working implementation with real errors fixed along the way.

---

Why OIDC Instead of Client Secrets?

Traditional CI/CD pipelines store an Azure App Registration client secret (or certificate) as a GitHub secret. That secret:

• Expires and needs manual rotation
• Can be leaked if someone gets access to your GitHub secrets
• Creates a long-lived credential that exists outside Azure's control plane

OIDC (Workload Identity Federation) replaces this with a trust relationship. GitHub Actions generates a short-lived JWT token per run. Azure AD verifies that token — no secret ever needs to be stored. The token is valid only for that specific run and expires automatically.

GitHub Actions Run
        │
        ▼
   GitHub OIDC Provider  ──── JWT Token ────▶  Azure AD
                                               (validates token
                                                against federated
                                                credential config)
                                                      │
                                                      ▼
                                              Issues short-lived
                                              access token

---

Architecture Overview

┌─────────────────────────────────────────────────────────────┐
│                    GitHub Actions Workflow                  │
│                                                             │
│  Job 1: PSScriptAnalyzer Lint                               │
│  ├── Checkout repo                                          │
│  ├── Install PSScriptAnalyzer                               │
│  └── Lint Azure/AzureRoleEnable-v5.ps1                      │
│      ├── Error found? → FAIL (fix and re-run)               │
│      └── Clean? → trigger Job 2                             │
│                                                             │
│  Job 2: Activate PIM Roles (depends on lint passing)        │
│  ├── Azure OIDC Login (no client secret)                    │
│  ├── Get Microsoft Graph access token via OIDC              │
│  ├── Install Microsoft Graph PowerShell SDK                 │
│  ├── Run AzureRoleEnable-v5.ps1 (attempt 1)                 │
│  │   └── Fail? → wait 30s → attempt 2                       │
│  └── Report final status                                    │
└─────────────────────────────────────────────────────────────┘
                          │
                          ▼ Microsoft Graph API
┌─────────────────────────────────────────────────────────────┐
│  AzureRoleEnable-v5.ps1                                     │
│  ├── Connect-MgGraph (using OIDC access token)              │
│  ├── Get-MgUser (resolve target UPN → Object ID)            │
│  ├── Get-MgRoleManagementDirectoryRoleEligibilitySchedule   │
│  │   (fetch all eligible roles for user)                    │
│  ├── Check if role already active → skip                    │
│  └── New-MgRoleManagementDirectoryRoleAssignmentScheduleRequest
│      (adminAssign action for each eligible role)            │
└─────────────────────────────────────────────────────────────┘

---

Prerequisites

Before starting, you need:

• An Azure AD tenant (Entra ID)
• A GitHub repository
• Azure AD P2 or Microsoft Entra ID Governance licence (required for PIM)
• A user account with permissions to create App Registrations in Azure AD
• A Global Administrator or Privileged Role Administrator to grant admin consent (you can prepare everything and ask them to click one button)

---

Step 1 — Create the Azure App Registration

Navigate to Azure Portal → Microsoft Entra ID → App Registrations → New Registration.

Field	Value
Name	github-pim-activator
Supported account types	Accounts in this org only
Redirect URI	Leave blank

After creating, note down:

• Application (client) ID
• Directory (tenant) ID

Add API Permission

Go to API Permissions → Add a permission → Microsoft Graph → Application permissions.

Search for and add: RoleManagement.ReadWrite.Directory

⚠️ This permission requires admin consent. The "Grant admin consent" button will be greyed out if you are not a Global Administrator. Note this down to ask your admin later.

Add Federated Credential (OIDC)

Go to Certificates & secrets → Federated credentials → Add credential.

Field	Value
Federated credential scenario	GitHub Actions deploying Azure resources
Organization	your-github-org
Repository	your-repo-name
Entity type	Branch
Branch	main
Name	github-actions-main-branch

This tells Azure AD: "Trust JWT tokens from GitHub Actions running on the main branch of this repository."

---

Step 2 — Add GitHub Secrets

In your GitHub repository, go to Settings → Secrets and variables → Actions → New repository secret.

Add these two secrets:

Secret Name	Value
AZURE_CLIENT_ID	Application (client) ID from Step 1
AZURE_TENANT_ID	Directory (tenant) ID from Step 1

No client secret is needed. OIDC handles authentication entirely.

---

Step 3 — The PowerShell Script

Create Azure/AzureRoleEnable-v5.ps1 in your repository. This script is PSScriptAnalyzer compliant — it passes lint checks with zero errors.

Key design decisions:

• Uses [System.Net.NetworkCredential]::new("", $accessToken).SecurePassword instead of ConvertTo-SecureString -AsPlainText (avoids PSScriptAnalyzer error)
• Uses Write-Information instead of Write-Host (avoids PSScriptAnalyzer warnings)
• Reads target UPN and access token from environment variables
• Checks if a role is already active before attempting to assign (skip logic)
• Exits with code 1 if any role assignment fails (triggers retry in workflow)

# ============================================================
# Azure PIM Role Activator v5 - App-Only OIDC
# PSScriptAnalyzer compliant
# ============================================================
param(
    [string]$TargetUserUPN   = $env:TARGET_USER_UPN,
    [string]$Justification   = "M365 Team - GitHub Actions"
)

Set-StrictMode -Version Latest
$ErrorActionPreference  = "Stop"
$InformationPreference  = "Continue"

if ([string]::IsNullOrWhiteSpace($TargetUserUPN)) {
    Write-Error "TARGET_USER_UPN required"; exit 1
}

$accessToken = $env:AZURE_ACCESS_TOKEN
if ([string]::IsNullOrWhiteSpace($accessToken)) {
    Write-Error "AZURE_ACCESS_TOKEN not set"; exit 1
}

Write-Information "Connecting to Microsoft Graph..."

# Use [securestring] direct cast to avoid PSAvoidUsingConvertToSecureStringWithPlainText
$secureToken = [System.Net.NetworkCredential]::new("", $accessToken).SecurePassword
Connect-MgGraph -AccessToken $secureToken -NoWelcome

Write-Information "Resolving user: $TargetUserUPN"
$user = Get-MgUser -UserId $TargetUserUPN -Property "id,userPrincipalName,displayName" -ErrorAction Stop
Write-Information "User: $($user.DisplayName) [$($user.Id)]"

Write-Information "Fetching eligible PIM roles..."
$eligibleRoles = Get-MgRoleManagementDirectoryRoleEligibilitySchedule `
    -Filter "principalId eq '$($user.Id)'" `
    -ExpandProperty RoleDefinition `
    -ErrorAction SilentlyContinue

if (-not $eligibleRoles -or $eligibleRoles.Count -eq 0) {
    Write-Warning "No eligible roles found for $TargetUserUPN"
    exit 0
}

Write-Information "Found $($eligibleRoles.Count) eligible role(s)."

$ok = 0; $fail = 0; $skip = 0

foreach ($role in $eligibleRoles) {
    $rn = $role.RoleDefinition.DisplayName
    try {
        # Check if already active — skip if so
        $aa = Get-MgRoleManagementDirectoryRoleAssignmentSchedule `
            -Filter "principalId eq '$($user.Id)' and roleDefinitionId eq '$($role.RoleDefinitionId)'" `
            -ErrorAction SilentlyContinue

        if ($aa) {
            Write-Information "SKIP: $rn (already active)"
            $skip++
            continue
        }

        # Submit adminAssign request
        New-MgRoleManagementDirectoryRoleAssignmentScheduleRequest -BodyParameter @{
            Action           = "adminAssign"
            PrincipalId      = $user.Id
            RoleDefinitionId = $role.RoleDefinitionId
            DirectoryScopeId = if ($role.DirectoryScopeId) { $role.DirectoryScopeId } else { "/" }
            Justification    = $Justification
            ScheduleInfo     = @{
                StartDateTime = (Get-Date).ToUniversalTime().ToString("o")
                Expiration    = @{ Type = "AfterDuration"; Duration = "PT8H" }
            }
        } | Out-Null

        Write-Information "OK : $rn activated for 8h"
        $ok++
    }
    catch {
        Write-Error "FAIL: $rn - $($_.Exception.Message)"
        $fail++
    }
}

Write-Information "Summary: OK=$ok SKIP=$skip FAIL=$fail"
Disconnect-MgGraph -ErrorAction SilentlyContinue

if ($fail -gt 0) { exit 1 }

---

Step 4 — The GitHub Actions Workflow

Create .github/workflows/run-AzureRoleEnable.yml:

name: Azure PIM Role Activator (OIDC)

on:
  workflow_dispatch:
    inputs:
      target_user_upn:
        description: 'Target user UPN (e.g. user@domain.com)'
        required: false
        default: 'user@yourdomain.com'
        type: string
      justification:
        description: 'Activation justification'
        required: false
        default: 'M365 Team - GitHub Actions'
        type: string

permissions:
  id-token: write   # Required for OIDC token
  contents: read    # Required for checkout

jobs:
  lint:
    name: PSScriptAnalyzer Lint
    runs-on: windows-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Install PSScriptAnalyzer
        shell: pwsh
        run: Install-Module PSScriptAnalyzer -Scope CurrentUser -Force -AllowClobber

      - name: Run PSScriptAnalyzer
        shell: pwsh
        run: |
          $results = Invoke-ScriptAnalyzer `
            -Path "Azure/AzureRoleEnable-v5.ps1" `
            -Severity Error, Warning `
            -Recurse

          if ($results) {
            $results | Format-Table -AutoSize
            $errors = $results | Where-Object { $_.Severity -eq 'Error' }
            if ($errors) {
              Write-Error "PSScriptAnalyzer found $($errors.Count) error(s). Fix before proceeding."
              exit 1
            }
          } else {
            Write-Host "PSScriptAnalyzer: No issues found." -ForegroundColor Green
          }

  activate-pim-roles:
    name: Activate PIM Roles
    runs-on: windows-latest
    needs: lint   # Only runs if lint job passes
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Azure OIDC Login
        uses: azure/login@v2
        with:
          client-id: ${{ secrets.AZURE_CLIENT_ID }}
          tenant-id: ${{ secrets.AZURE_TENANT_ID }}
          allow-no-subscriptions: true

      - name: Get Microsoft Graph Access Token via OIDC
        id: get-token
        shell: pwsh
        run: |
          $token = az account get-access-token `
            --resource https://graph.microsoft.com `
            --query accessToken -o tsv
          echo "AZURE_ACCESS_TOKEN=$token" >> $env:GITHUB_ENV

      - name: Install Microsoft Graph PowerShell SDK
        shell: pwsh
        run: |
          Install-Module Microsoft.Graph -Scope CurrentUser -Force -AllowClobber
          Import-Module Microsoft.Graph -Force

      - name: Run PIM Role Activator (attempt 1)
        id: attempt1
        shell: pwsh
        continue-on-error: true
        env:
          TARGET_USER_UPN: ${{ github.event.inputs.target_user_upn }}
          JUSTIFICATION:   ${{ github.event.inputs.justification }}
        run: |
          Write-Host "=== Attempt 1 ===" -ForegroundColor Cyan
          & "./Azure/AzureRoleEnable-v5.ps1"

      - name: Wait before retry
        if: steps.attempt1.outcome == 'failure'
        shell: pwsh
        run: Start-Sleep -Seconds 30

      - name: Run PIM Role Activator (attempt 2 on failure)
        if: steps.attempt1.outcome == 'failure'
        id: attempt2
        shell: pwsh
        continue-on-error: true
        env:
          TARGET_USER_UPN: ${{ github.event.inputs.target_user_upn }}
          JUSTIFICATION:   ${{ github.event.inputs.justification }}
        run: |
          Write-Host "=== Attempt 2 ===" -ForegroundColor Cyan
          & "./Azure/AzureRoleEnable-v5.ps1"

      - name: Report final status
        shell: pwsh
        run: |
          $a1 = "${{ steps.attempt1.outcome }}"
          $a2 = "${{ steps.attempt2.outcome }}"

          if ($a1 -eq "success") {
            Write-Host "PIM role activation SUCCEEDED on attempt 1." -ForegroundColor Green
          } elseif ($a2 -eq "success") {
            Write-Host "PIM role activation SUCCEEDED on attempt 2." -ForegroundColor Yellow
          } else {
            Write-Error "PIM role activation FAILED after all attempts."
            exit 1
          }

---

Step 5 — Grant Admin Consent

This is the one step that requires a Global Administrator or Privileged Role Administrator in your tenant.

1. Navigate to: Azure Portal → App Registrations → github-pim-activator → API Permissions
2. Click "Grant admin consent for [your tenant]"
3. Confirm the dialog

Once granted, the RoleManagement.ReadWrite.Directory permission status changes from ⚠️ "Not granted" to ✅ "Granted".

Optional but recommended: Also assign the "Privileged Role Administrator" Azure AD role directly to the github-pim-activator Service Principal via: Azure AD → Roles and administrators → Privileged Role Administrator → Add assignment → search github-pim-activator

---

Errors I Hit and How I Fixed Them

This is a real implementation — here are the actual errors encountered and their fixes.

Error 1: PSScriptAnalyzer — PSAvoidUsingConvertToSecureStringWithPlainText

Cause: Using ConvertTo-SecureString -AsPlainText -Force to convert the access token.

Fix: Replace with:

# Before (triggers PSScriptAnalyzer error)
$secureToken = ConvertTo-SecureString $accessToken -AsPlainText -Force

# After (PSScriptAnalyzer compliant)
$secureToken = [System.Net.NetworkCredential]::new("", $accessToken).SecurePassword

Error 2: PSScriptAnalyzer — PSAvoidUsingWriteHost (7 warnings)

Cause: Multiple Write-Host calls throughout the script.

Fix:

# Add at top of script
$InformationPreference = "Continue"

# Replace all Write-Host with Write-Information
Write-Information "Connecting to Microsoft Graph..."

Error 3: OIDC Login Failed — "client-id and tenant-id not supplied"

Cause: GitHub secrets AZURE_CLIENT_ID and AZURE_TENANT_ID had not been added to the repository yet.

Fix: Navigate to GitHub → Settings → Secrets and variables → Actions and add both secrets.

Error 4: 403 Forbidden — "Insufficient privileges"

Cause: The RoleManagement.ReadWrite.Directory Application permission was added to the App Registration, but admin consent was never granted.

Fix: A Global Administrator must click "Grant admin consent for [tenant]" in the API permissions page.

---

How to Run the Workflow

Once everything is set up and admin consent is granted:

1. Go to your repository → Actions tab
2. Select "Azure PIM Role Activator (OIDC)"
3. Click "Run workflow"
4. Fill in:
   • Target user UPN: user@yourdomain.com
   • Justification: M365 Team - GitHub Actions
5. Click "Run workflow"

The workflow will:

1. ✅ Run PSScriptAnalyzer lint
2. ✅ Authenticate via OIDC (no stored secret)
3. ✅ Get Microsoft Graph access token
4. ✅ Resolve the user by UPN
5. ✅ Fetch all eligible PIM roles
6. ✅ Skip roles already active
7. ✅ Submit adminAssign requests for remaining roles
8. ✅ Report pass/fail with retry on failure

---

Key Design Decisions

Why adminAssign and not selfActivate?

selfActivate requires delegated authentication — a real user signing in interactively. GitHub Actions uses app-only (application) authentication, which cannot impersonate a user. adminAssign works with app-only auth and assigns roles on behalf of the target user.

Why OIDC over a client secret?

No rotation, no leakage risk, no long-lived credential. The token is scoped to the exact repo + branch and expires immediately after the run.

Why PSScriptAnalyzer lint as a separate job?

It ensures the script is always clean before any Azure resources are touched. If a code change introduces a linting error, the workflow fails fast at the lint stage without ever attempting to connect to Azure.

Why retry logic?

Microsoft Graph can occasionally return transient errors on PIM requests (throttling, replication delays). A 30-second wait and second attempt handles these without requiring a full re-run.

---

Summary

Component	Details
Script	Azure/AzureRoleEnable-v5.ps1 — PSScriptAnalyzer compliant
Workflow	.github/workflows/run-AzureRoleEnable.yml — lint + retry
Auth	OIDC Workload Identity Federation — zero stored secrets
App Registration	github-pim-activator — Application permission only
Graph Permission	RoleManagement.ReadWrite.Directory (requires admin consent)
Action	adminAssign — compatible with app-only auth
Trigger	Manual workflow_dispatch with UPN and justification inputs

---

Resources

• Azure Workload Identity Federation (OIDC)
• GitHub Actions: Configuring OIDC with Azure
• Microsoft Graph: PIM Role Assignment Schedule Requests
• PSScriptAnalyzer