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.csvfor groupName join) - SharePoint PDR-SecOps site must exist with
pipeline-datadocument 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_URLandSHAREPOINT_PIPELINE_DATA_LIBRARYas 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-datalibrary - 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.csvis present and valid -
veriato-productivity-daily.csvis 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.ps1topdr-sharepoint-automationas 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-datalibrary
Stage 6 - Schedule & Monitor
- Create schedule: Monthly, 1st of month, 06:00 UTC
- Create dependency:
Import-VeriatoUsers.ps1runs 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-datalibrary - 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.
Comments
0 comments
Please sign in to leave a comment.