Compliance audits are terrible so use this workflow

TL;DR: Access reviews are slow and error-prone. This 5-step prompt chain automates reconciliation between HRIS, IDP, and ticketing data for SOC 2 and ISO compliance.

The Breakdown

If you have ever had to manually reconcile an HR spreadsheet with an Identity Provider list while cross-referencing Jira tickets, you know the specific kind of pain this solves. This Reddit user, u/CalendarVarious3992, shared a comprehensive prompt chain designed to handle the heavy lifting of workforce access reviews. The author constructed a workflow that doesn’t just ask the AI to “check the data,” but specifically guides it through normalization, reconciliation, validation, and reporting.

I was impressed by how structured this approach is; it treats the LLM like a junior compliance analyst rather than a simple text generator.

Why It Works

• Variable Definitions: The prompt starts by clearly defining the data inputs ([HRIS_DATA], etc.). This grounds the model, ensuring it knows exactly what data represents what source before processing begins.
• Sequential Logic: Instead of asking for the final report immediately, the author breaks the task into five distinct prompts. This prevents the model from hallucinating connections and forces it to show its work at every stage (normalization -> reconciliation -> validation).
• Interactive Checkpoints: Each step ends with a question (e.g., “Proceed to reconciliation?”). This allows you to verify the intermediate tables are correct before wasting tokens on the next step.

Use Cases

• Quarterly Access Reviews: Speed up the mandatory SOC 2 review cycle by automating the comparison of active vs. terminated employees.
• Shadow IT Detection: Identify “orphaned” accounts that exist in your Identity Provider but have no matching record in your HR system.
• Audit Evidence Generation: Create the exact artifacts auditors ask for, mapped to specific control clauses like ISO 27001 A.9.2.1.

The Prompt

Note: This is a prompt chain. You will need to provide the data (CSV text) where the placeholders are, or upload files if your LLM supports code interpreter/file analysis.

VARIABLE DEFINITIONS

• [HRIS_DATA]=CSV export of active and terminated workforce records from the HRIS
• [IDP_ACCESS]=CSV export of user accounts, group memberships, and application assignments from the Identity Provider
• [TICKETING_DATA]=CSV export of provisioning/deprovisioning access tickets (requester, approver, status, close date) from the ticketing system

Prompt 1 – Consolidate & Normalize Inputs

1. Ingest HRIS_DATA, IDP_ACCESS, and TICKETING_DATA.
2. Standardize field names (Employee_ID, Email, Department, Manager_Email, Employment_Status, App_Name, Group_Name, Action_Type, Request_Date, Close_Date, Ticket_ID, Approver_Email).
3. Generate three clean tables: Normalized_HRIS, Normalized_IDP, Normalized_TICKETS.
4. Flag and list data-quality issues: duplicate Employee_IDs, missing emails, date-format inconsistencies.
5. Output the three normalized tables plus a Data_Issues list. Ask: “Tables prepared. Proceed to reconciliation? (yes/no)”

Prompt 2 – HRIS ⇄ IDP Reconciliation

System role: You are a compliance analyst.

1. Compare Normalized_HRIS vs Normalized_IDP on Employee_ID or Email.
2. Identify and list:
   a) Active accounts in IDP for terminated employees.
   b) Employees in HRIS with no IDP account.
   c) Orphaned IDP accounts (no matching HRIS record).
3. Produce Exceptions_HRIS_IDP table with columns: Employee_ID, Email, Exception_Type, Detected_Date.
4. Provide summary counts for each exception type.
5. Ask: “Reconciliation complete. Proceed to ticket validation? (yes/no)”

Prompt 3 – Ticketing Validation of Access Events

1. For each add/remove event in Normalized_IDP during the review quarter, search Normalized_TICKETS for a matching closed ticket by Email, App_Name/Group_Name, and date proximity (±7 days).
2. Mark Match_Status: Adequate_Evidence, Missing_Ticket, Pending_Approval.
3. Output Access_Evidence table with columns: Employee_ID, Email, App_Name, Action_Type, Event_Date, Ticket_ID, Match_Status.
4. Summarize counts of each Match_Status.
5. Ask: “Ticket validation finished. Generate risk report? (yes/no)”

Prompt 4 – Risk Categorization & Remediation Recommendations

1. Combine Exceptions_HRIS_IDP and Access_Evidence into Master_Exceptions.
2. Assign Severity:
   • High – Terminated user still active OR Missing_Ticket for privileged app.
   • Medium – Orphaned account OR Pending_Approval beyond 14 days.
   • Low – Active employee without IDP account.
3. Add Recommended_Action for each row.
4. Output Risk_Report table: Employee_ID, Email, Exception_Type, Severity, Recommended_Action.
5. Provide heat-map style summary counts by Severity.
6. Ask: “Risk report ready. Build auditor evidence package? (yes/no)”

Prompt 5 – Evidence Package Assembly (SOC 2 + ISO 27001)

1. Generate Management_Summary (bullets, <250 words) covering scope, methodology, key statistics, and next steps.
2. Produce Controls_Mapping table linking each exception type to SOC 2 (CC6.1, CC6.2, CC7.1) and ISO 27001 (A.9.2.1, A.9.2.3, A.12.2.2) clauses.
3. Export the following artifacts in comma-separated format embedded in the response:
   a) Normalized_HRIS
   b) Normalized_IDP
   c) Normalized_TICKETS
   d) Risk_Report
4. List file names and recommended folder hierarchy for evidence hand-off (e.g., /Quarterly_Access_Review/Q1_2024/).
5. Ask the user to confirm whether any additional customization or redaction is required before final submission.

Review / Refinement
Please review the full output set for accuracy, completeness, and alignment with internal policy requirements. Confirm “approve” to finalize or list any adjustments needed (column changes, severity thresholds, additional controls mapping).

💡 Variations to Try

• Privacy-First Mode: Before running this, replace real names and emails in your CSVs with dummy IDs (e.g., User_001). The logic remains the same, but you avoid sharing PII with the model.
• Single-Step Execution: If you are using a model with a massive context window (like Gemini 1.5 Pro or Claude 3 Opus), you can try pasting all data and the full prompt at once, though the step-by-step method usually yields higher accuracy.

Check out the full discussion on Reddit for more context on how the original creator implements this.

Streamline your access review process. Prompt included.
by u/CalendarVarious3992 in PromptEngineering