Runbook: Import-VeriatoProductivity.ps1

Script: scripts/powershell/Import-VeriatoProductivity.ps1

Owner: Max Simon (build), Juan Munoz (graduation execution)

Schedule: Monthly, 1st of month, 06:00 UTC

Azure Automation Account: pdr-sharepoint-automation (West US 2, rg-it-automation)

Compliance: Veriato ETL pipeline feeding Power BI Negotiations dashboard

Purpose

Pulls Veriato productivity data (summary + daily endpoints), joins groupName against the users dimension table, and exports CSV files for Power BI consumption. This is the primary data feed for Jessica's Negotiations department dashboard.

Data Flow

Veriato API (/v1/productivity/summary + /v1/productivity)
    |
    v
Import-VeriatoProductivity.ps1 (client-side date filter + groupName join)
    |
    v
veriato-productivity-summary.csv + veriato-productivity-daily.csv
    |
    v
SharePoint pipeline-data library (PDR-SecOps site)
    |
    v
Power BI M queries (FactProductivitySummary, FactProductivityDaily)
    |
    v
Negotiations Department Dashboard

Dependencies

Import-VeriatoUsers.ps1 must run BEFORE this script (provides veriato-users.csv for groupName join)
SharePoint PDR-SecOps site must exist with pipeline-data document library
Veriato API credentials in Azure Automation Credentials vault

SharePoint Prerequisite (MUST COMPLETE BEFORE GRADUATION)

Step 1: Juan Creates PDR-SecOps Site

Juan creates a new dedicated SharePoint site:

Site name: PDR-SecOps
URL: https://anthonypacificdebt.sharepoint.com/sites/PDR-SecOps
Site type: Team site (no Microsoft 365 group needed)

Step 2: Juan Sets Permissions

Principal	Permission Level
Azure Automation service account	Contribute
Max Simon	Full Control
Juan Munoz	Full Control
Melissa Pena	Read (add when she starts)

Step 3: Juan Creates pipeline-data Document Library

Within the PDR-SecOps site, create a document library called pipeline-data. This is where all Azure Automation runbook CSV outputs will land.

Step 4: Juan Confirms to Max

Juan provides Max with confirmation that the site, permissions, and library are ready.

Step 5: Max Updates Configuration

Max updates the following locations with the SharePoint values:

.env (local development):

SHAREPOINT_SITE_URL=https://anthonypacificdebt.sharepoint.com/sites/PDR-SecOps
SHAREPOINT_PIPELINE_DATA_LIBRARY=pipeline-data

Bitwarden (IT Shared folder):

Store the same values for reference and disaster recovery

Azure Automation Variables:

Add SHAREPOINT_SITE_URL and SHAREPOINT_PIPELINE_DATA_LIBRARY as automation variables

Step 6: Max Tests SharePoint Write

Max runs a local test to confirm the script can write CSVs to the pipeline-data library:

# Test with a small date range and -Verbose
.\Import-VeriatoProductivity.ps1 -Scope All -ConfirmProduction -StartDate "2026-03-01" -EndDate "2026-03-07" -Verbose

Verify:

CSVs appear in pipeline-data library
File names match expected pattern
File contents match local output

Step 7: Max Confirms CSVs Land in pipeline-data

Max opens the SharePoint library and confirms:

veriato-productivity-summary.csv is present and valid
veriato-productivity-daily.csv is present and valid
Power BI can connect to the SharePoint library and refresh data

Step 8: Juan Executes Azure Automation Graduation

Only after Steps 1-7 are complete, Juan proceeds with the 8-stage Azure Automation graduation path:

Azure Automation Graduation Checklist (8 Stages)

Stage	Description	Status
1	Local Build & Test	COMPLETE
2	Production Validation (Manual)
3	Service Account Creation
4	Credential Storage
5	Runbook Creation & Testing
6	Schedule & Monitor
7	Parallel Run (7 days)
8	Decommission Local

Stage 1 - Local Build & Test (COMPLETE)

Script validated against test account and org-wide. GroupName join against users dimension table proven. Client-side date filtering active (server-side filtering unreliable on Veriato API).

Stage 2 - Production Validation (Manual)

Run manually against production for a full month date range:

.\Import-VeriatoProductivity.ps1 -Scope All -ConfirmProduction -StartDate "2026-03-01" -EndDate "2026-03-31" -Verbose

Verify:

CSV row counts match expected user count (~80 users in summary, ~300+ daily records)
Department distribution matches users dimension table
No blank or "Default Group" entries in groupName column
Date range in output matches requested range

Stage 3 - Service Account Creation

Create svc-veriato-etl@pacificdebt.com (or reuse existing service account if scoped appropriately)
Grant Veriato API access
Document credentials in Bitwarden (IT Shared > Service Accounts)

Stage 4 - Credential Storage

Store in Azure Automation Credentials vault (pdr-sharepoint-automation):

Veriato API key
Veriato tenant ID
Veriato account ID
SharePoint site URL
Service account credentials

Update script to pull from Credentials vault when $env:AUTOMATION_RUNBOOK is set.

Stage 5 - Runbook Creation & Testing

Import Import-VeriatoProductivity.ps1 to pdr-sharepoint-automation as a runbook
Also import Import-VeriatoUsers.ps1 (dependency — must run first)
Publish both runbooks
Test with manual run from Azure portal
Verify CSV output lands in SharePoint pipeline-data library

Stage 6 - Schedule & Monitor

Create schedule: Monthly, 1st of month, 06:00 UTC
Create dependency: Import-VeriatoUsers.ps1 runs at 05:30 UTC (30 min before)
Configure Azure Monitor alerts:

- [ ] Job failure alert → Slack (C0ALSNATGET) + email to runbook owner

- [ ] Heartbeat alert: no run in 35 days = alert

Export job logs to Log Analytics workspace (minimum 90 days retention)

Stage 7 - Parallel Run

Run both local and Azure Automation versions for 1 full monthly cycle. Compare:

CSV row counts match
Department distribution matches
Date ranges match
Power BI dashboard refreshes successfully from Azure Automation output

Stage 8 - Decommission Local

Remove local scheduled execution
Azure Automation is sole production runtime
Local script remains in repo for reference and future modifications
Update Power BI data source to point exclusively to SharePoint library

Rollback Procedure

If the Azure Automation runbook produces incorrect output or fails:

Stop the runbook — disable the schedule in Azure portal
Run locally — execute the script manually from the local repo as a stopgap
Upload CSVs manually — copy local CSV output to the SharePoint pipeline-data library
Notify Jessica — if the Power BI dashboard was affected, notify the Data Team
Investigate — check Azure Automation job logs and Log Analytics for root cause
Fix and re-test — fix the issue, re-publish the runbook, and run a manual test before re-enabling the schedule

Monitoring After Go-Live

Monthly spot-check on CSV row counts and department distribution
Verify Power BI dashboard refresh succeeds after each monthly run
Check Log Analytics for any warnings or errors in job output
Zendesk ticket: #17349

Related Tickets

#17349 — [Azure Automation] Graduate Import-VeriatoProductivity.ps1 to Production
SharePoint prerequisite tracked in ticket #17349 (internal note)

Source: secops-pipeline/runbooks/Import-VeriatoProductivity.md | Last synced: 2026-03-23T08:24:34Z | Do not edit in Zendesk -- changes will be overwritten on next sync.