Lab 02 — Automate Compliance Reporting

Modern compliance teams spend a significant amount of time collecting, validating, organizing, and reporting evidence.

A traditional compliance cycle may look like:

Compliance Team
      ↓
Emails Control Owners
      ↓
Requests Evidence
      ↓
Receives Screenshots
      ↓
Updates Spreadsheet
      ↓
Calculates Compliance Status
      ↓
Creates Report
      ↓
Repeats Next Month

This approach is difficult to scale.

It creates:

Manual Effort

Stale Evidence

Inconsistent Reporting

Missed Deadlines

Duplicate Requests

Weak Traceability

Slow Remediation

In this lab, you will design and simulate an automated compliance reporting workflow.

Your objective is to move from:

Manual Evidence Collection

to:

Connected Data Sources
      ↓
Automated Evidence
      ↓
Control Evaluation
      ↓
Exception Detection
      ↓
Compliance Reporting
      ↓
Management Action

Mission Information

Field	Details
Lab Type	Compliance Automation
Primary Role	GRC / Compliance Analyst
Supporting Roles	Control Owners, Security, IAM, Cloud, IT Operations
Difficulty	Intermediate
Estimated Time	90–120 minutes
Environment	Spreadsheet / Script / GRC Simulation
Primary Deliverable	Automated Compliance Report
Secondary Deliverables	Evidence Register, Control Status Report, Exception Register, Management Summary
Skills Practiced	Compliance Automation, Evidence Mapping, Control Testing, Reporting, Exception Management

Mission Scenario

You are working as a GRC Analyst at Meridian Digital Services.

The organization currently supports:

ISO 27001

SOC 2

PCI DSS

Internal Security Policies

Every month, the compliance team requests evidence from:

IAM Team

Cloud Security

IT Operations

Security Operations

HR

Engineering

Examples include:

MFA Reports

Encryption Status

Vulnerability Reports

Security Training Records

Logging Configuration

Access Reviews

Backup Reports

Code Review Evidence

Management wants to reduce manual reporting effort.

You have been asked to design a simple automated compliance reporting process.

Lab Objectives

By completing this lab, you will:

identify compliance data sources.
create a common control register.
map controls to multiple frameworks.
define automated evidence sources.
simulate evidence collection.
calculate control status automatically.
identify failed controls.
identify missing evidence.
identify stale evidence.
create an exception register.
create remediation actions.
generate framework-level compliance results.
create a management compliance summary.
understand where human validation remains necessary.
build a repeatable compliance-reporting workflow.

Lab Architecture

Your workflow will follow:

Source Systems
      ↓
Evidence Collection
      ↓
Evidence Register
      ↓
Control Mapping
      ↓
Automated Evaluation
      ↓
PASS / FAIL / REVIEW
      ↓
Framework Status
      ↓
Exception Register
      ↓
Remediation
      ↓
Compliance Report

Part 1 — Prepare the Workspace

Create a workbook named:

Automated-Compliance-Reporting.xlsx

Create these worksheets:

01 Management Summary

02 Control Register

03 Framework Mapping

04 Evidence Register

05 Automated Tests

06 Exceptions

07 Remediation

08 Reporting

Part 2 — Build the Control Register

Open:

02 Control Register

Create the following columns:

Field	Purpose
Control ID	Unique control identifier
Control Name	Control description
Domain	IAM, Cloud, Security, HR, etc.
Owner	Control owner
Frequency	Continuous, Daily, Quarterly, Annual
Evidence Source	Authoritative evidence source
Automation	Automated / Manual / Hybrid
Criticality	Critical / High / Medium
Status	Pass / Fail / Review

Populate:

Control ID	Control	Domain	Owner	Frequency	Automation
IAM-001	Privileged MFA	Identity	IAM	Continuous	Automated
IAM-002	Quarterly Access Review	Identity	IAM	Quarterly	Hybrid
ENC-001	Production Data Encryption	Cloud	Cloud Security	Continuous	Automated
LOG-001	Security Logging Enabled	Security	SOC	Daily	Automated
VM-001	Critical Vulnerabilities Within SLA	Security	Vulnerability Team	Daily	Automated
HR-001	Security Training Completed	HR	HR / Security	Monthly	Automated
BCP-001	Backup Completion	IT Ops	IT Operations	Daily	Automated
DEV-001	Production Changes Reviewed	Engineering	Engineering	Continuous	Hybrid

Part 3 — Build the Framework Mapping

Open:

03 Framework Mapping

Create:

Control ID	ISO 27001	SOC 2	PCI DSS	Internal Policy
IAM-001	Yes	Yes	Yes	Yes
IAM-002	Yes	Yes	Yes	Yes
ENC-001	Yes	Yes	Yes	Yes
LOG-001	Yes	Yes	Yes	Yes
VM-001	Yes	Yes	Yes	Yes
HR-001	Yes	Yes	No	Yes
BCP-001	Yes	Yes	No	Yes
DEV-001	Yes	Yes	Yes	Yes

The purpose is to create:

One Control
      ↓
Multiple Frameworks

instead of:

One Framework
      ↓
Duplicate Control

Part 4 — Identify Evidence Sources

Open:

04 Evidence Register

Create:

Evidence ID	Control ID	Source	Evidence	Collection	Frequency
E-001	IAM-001	Identity Provider	Admin MFA Status	API	Daily
E-002	IAM-002	IAM Platform	Access Review Records	Export	Quarterly
E-003	ENC-001	Cloud Platform	Encryption Status	API	Daily
E-004	LOG-001	SIEM / Cloud	Logging Status	API	Daily
E-005	VM-001	Vulnerability Scanner	Vulnerability Age	API	Daily
E-006	HR-001	Training Platform	Completion Report	API / CSV	Monthly
E-007	BCP-001	Backup Platform	Backup Job Status	API	Daily
E-008	DEV-001	Source Control	PR Approval Records	API	Continuous

Part 5 — Add Evidence Metadata

Add the following columns:

Collected Date

Period Start

Period End

Record Count

Status

Freshness

Validated By

Example:

Evidence ID	Collected	Record Count	Freshness
E-001	Today	250	Current
E-002	3 months ago	1	Review
E-003	Today	500	Current
E-004	Today	40	Current

Part 6 — Define Evidence Freshness

Create rules such as:

Daily Control
Evidence > 2 Days Old
=
Stale

Monthly Control
Evidence > 35 Days Old
=
Stale

Quarterly Control
Evidence > 100 Days Old
=
Stale

The exact thresholds should reflect organizational policy.

Part 7 — Build Automated Tests

Open:

05 Automated Tests

Create:

Test ID	Control	Metric	Target	Current	Result
T-001	IAM-001	MFA Coverage	100%	98%	Fail
T-002	IAM-002	Access Review Completion	100%	100%	Pass
T-003	ENC-001	Encryption Coverage	100%	100%	Pass
T-004	LOG-001	Logging Coverage	100%	99%	Fail
T-005	VM-001	Vulnerabilities Within SLA	100%	92%	Fail
T-006	HR-001	Training Completion	100%	98.5%	Review
T-007	BCP-001	Backup Success	100%	99.8%	Review
T-008	DEV-001	Reviewed Production Changes	100%	97%	Fail

Part 8 — Define Test Logic

For controls requiring 100%:

IF Current = Target
THEN PASS
ELSE FAIL

For controls with approved tolerance:

IF Current >= Approved Threshold
THEN PASS
ELSE REVIEW

Use organizational policy rather than arbitrary thresholds.

Part 9 — Simulate IAM Evidence

Control:

IAM-001
Privileged MFA

Population:

250 Privileged Accounts

Results:

245 MFA Enabled

5 MFA Disabled

Calculate:

245 ÷ 250 × 100
=
98%

Control requirement:

100%

Result:

FAIL

Part 10 — Validate the Population

Before finalizing the failure, ask:

Are All 250
Accounts In Scope?

Suppose investigation identifies:

2 Break-Glass Accounts

1 Decommissioned Account

2 Active Admin Accounts
Without MFA

Now determine whether the break-glass accounts have approved compensating controls.

The final GRC result may be:

2 Genuine Exceptions

rather than simply:

5 Failures

Part 11 — Simulate Encryption Evidence

Control:

ENC-001
Production Data Encryption

Population:

500 Production Storage Resources

Results:

500 Encrypted

Coverage:

100%

Result:

PASS

Record:

Source

Timestamp

Population

Result

Part 12 — Simulate Logging Evidence

Control:

LOG-001
Security Logging Enabled

Population:

40 Production Accounts

Result:

39 Logging Enabled

1 Logging Disabled

Coverage:

97.5%

If requirement is:

100%

result:

FAIL

Part 13 — Simulate Vulnerability Compliance

Control:

VM-001
Critical Vulnerabilities
Remediated Within SLA

Population:

100 Critical Vulnerabilities

Results:

92 Within SLA

8 Past SLA

Compliance:

92%

Result:

FAIL

Part 14 — Add Risk Context

The eight overdue vulnerabilities consist of:

5 Development Systems

2 Internal Production Systems

1 Internet-Facing Production Server

Your compliance report should not simply state:

8 Vulnerabilities Failed

Highlight:

1 Internet-Facing
Production System
Past SLA

because risk context matters.

Part 15 — Simulate Training Evidence

Control:

HR-001
Security Training

HR population:

1,000 Employees

Training:

985 Complete

15 Incomplete

Investigation:

10 New Hires
Within Grace Period

2 Employees Terminated

3 Overdue

Actual exception population:

This demonstrates why automated results still require context.

Part 16 — Simulate Backup Evidence

Control:

BCP-001
Daily Backup Completion

Jobs:

1,000 Backup Jobs

998 Successful

2 Failed

Automated status may show:

99.8%

But investigate:

Which Systems Failed?

Suppose:

1 Test System

1 Tier-1 Payment Database

The Tier-1 failure may justify escalation.

Part 17 — Simulate DevOps Evidence

Control:

DEV-001
Production Changes Require Review

Source:

Source Control

Population:

200 Production Pull Requests

Result:

194 Independently Reviewed

6 Missing Independent Approval

Coverage:

97%

Result:

FAIL

Part 18 — Design Manual Validation

Not every automated result should be immediately trusted.

Create a validation column:

Validation Required?

Values:

Yes

No

Controls likely requiring validation:

IAM-001

HR-001

BCP-001

DEV-001

VM-001

Part 19 — Create the Exception Register

Open:

06 Exceptions

Create:

Exception ID	Control	Issue	Risk	Owner	Status	Expiry
EX-001	IAM-001	2 admins without MFA	High	IAM	Open	30 Days
EX-002	LOG-001	Logging disabled in production account	High	SOC	Open	7 Days
EX-003	VM-001	Critical vulnerability past SLA	Critical	Security	Open	7 Days
EX-004	HR-001	3 overdue training users	Medium	HR	Open	14 Days
EX-005	DEV-001	Unreviewed production changes	High	Engineering	Open	30 Days

Part 20 — Approved Exceptions

Some exceptions may be formally accepted.

Add:

Approval Status

Approver

Compensating Control

Approved Until

Example:

Break-Glass Account

Reason:
Emergency Access

Compensating Controls:
Restricted Use
Monitoring
Vaulted Credential
Post-Use Review

Approval:
CISO

Expiry:
90 Days

Part 21 — Separate Failure from Approved Exception

Your reporting logic should distinguish:

Control Failure

from:

Approved Exception

Example:

5 Accounts
Appear Without MFA

may become:

2 Unauthorized Exceptions

2 Approved Break-Glass Accounts

1 Decommissioned Account

Part 22 — Build the Remediation Register

Open:

07 Remediation

Create:

Action	Source	Owner	Severity	Due	Status	Validation
A-001	EX-001	IAM	High	+7 days	In Progress	Pending
A-002	EX-002	SOC	High	+2 days	Open	Pending
A-003	EX-003	Security	Critical	+1 day	In Progress	Pending
A-004	EX-004	HR	Medium	+14 days	Open	Pending
A-005	EX-005	Engineering	High	+7 days	Open	Pending

Part 23 — Define Remediation Workflow

Use:

Control Failure
      ↓
Validate
      ↓
Create Exception
      ↓
Assign Owner
      ↓
Remediate
      ↓
Collect Updated Evidence
      ↓
Retest
      ↓
Close

Part 24 — Retesting

Example:

Before:

IAM-001

98% MFA

After remediation:

250 Accounts

250 Compliant

Result:

100%
PASS

Do not close until:

Updated Evidence
+
Retest

confirm the issue is resolved.

Part 25 — Create Framework Results

Open:

08 Reporting

For each framework, determine:

Applicable Controls

Passing Controls

Failing Controls

Controls Under Review

Missing Evidence

Active Exceptions

Example:

Framework	Applicable	Pass	Fail	Review
ISO 27001	8	3	4	1
SOC 2	8	3	4	1
PCI DSS	6	2	4	0

Part 26 — Calculate Framework Status

Example formula:

Passing Controls
----------------
Applicable Controls
× 100

For ISO:

3 ÷ 8 × 100
=
37.5%

This lab intentionally uses a small dataset, so percentages may appear lower than a mature enterprise program.

Part 27 — Risk-Weighted Reporting

Do not treat all controls equally.

Example:

HR-001 Training
=
Medium

while:

IAM-001 Privileged MFA
=
Critical

A simple pass percentage may not show this distinction.

Create:

Critical Control Failures

as a separate metric.

Part 28 — Create Reporting Categories

Use:

Pass

Fail

Review Required

Approved Exception

Evidence Missing

Evidence Stale

This produces a more accurate view than only:

Pass / Fail

Part 29 — Build Evidence Health Metrics

Calculate:

Total Evidence Items

Current Evidence

Stale Evidence

Missing Evidence

Evidence Awaiting Validation

Example:

Total                 8

Current               6

Stale                 1

Missing               0

Awaiting Validation   1

Part 30 — Build Automation Metrics

Calculate:

Total Controls

Automated Controls

Hybrid Controls

Manual Controls

Example:

Total Controls        8

Automated             6

Hybrid                2

Manual                0

Automation coverage:

6 ÷ 8 × 100
=
75%

Do not treat high automation coverage as a goal by itself.

Part 31 — Build the Management Summary

Open:

01 Management Summary

Create the following sections.

Compliance Status

ISO 27001

SOC 2

PCI DSS

Control Status

Passing

Failing

Review Required

Evidence Health

Current

Stale

Missing

Exceptions

Critical

High

Medium

Approved

Remediation

Open

Overdue

Pending Retest

Part 32 — Add Executive Indicators

Display:

Critical Control Failures

Critical Exceptions

Overdue High-Risk Actions

Stale Evidence

Frameworks at Risk

Example:

Critical Control Failures      1

High Control Failures          3

Critical Exceptions            1

Stale Evidence                 1

Overdue Remediation            0

Part 33 — Management Attention Required

Create:

Management Attention Required

Example:

1. Internet-facing critical vulnerability exceeds SLA.

2. One production account does not have required logging.

3. Two active privileged accounts lack MFA.

4. Six production changes lack independent review.

5. Three employees are overdue for mandatory security training.

This section should translate technical results into management priorities.

Part 34 — Create the Automated Reporting Flow

Document your final workflow:

Identity Provider ───────┐
Cloud Platform ──────────┤
Vulnerability Scanner ───┤
Training Platform ───────┤
Backup Platform ─────────┤
Source Control ──────────┘
                         ↓
                 Evidence Register
                         ↓
                   Control Tests
                         ↓
            PASS / FAIL / REVIEW
                         ↓
                 Framework Mapping
                         ↓
               Exception Register
                         ↓
                  Remediation
                         ↓
                Management Report

Part 35 — Add Reporting Frequency

Define how often reports should update.

Example:

Data	Frequency
MFA	Daily
Encryption	Daily
Logging	Daily
Vulnerabilities	Daily
Training	Weekly / Monthly
Backup	Daily
Access Review	Quarterly
Code Review	Daily

Part 36 — Define Report Frequency

Possible reporting:

Operational Compliance
→ Daily

GRC Management
→ Weekly

Executive
→ Monthly

Audit Committee
→ Quarterly

Part 37 — Design Alert Thresholds

Example:

Privileged MFA

100%
=
Normal

99–99.9%
=
Warning

<99%
=
Critical

If organizational policy requires absolute 100% coverage, any unauthorized exception should be escalated.

Critical Vulnerability SLA

0 Past SLA
=
Normal

1
=
Warning

>1
=
Critical

Part 38 — Add Data Quality Checks

Automation is unreliable when source data is incomplete.

Create checks for:

Population Completeness

Duplicate Records

Missing Owners

Missing Timestamps

Integration Failures

Missing Framework Mapping

Part 39 — Population Reconciliation

Example:

HR Population:
1,000

Identity Population:
980

Before reporting MFA compliance, investigate:

Where Are
the Missing 20?

Possible reasons:

Contractors

Terminated Users

Integration Failure

Accounts Missing

Scope Difference

Part 40 — Integration Health

Create a simple integration register.

Integration	Status	Last Sync	Owner
Identity Provider	Healthy	Today	IAM
AWS	Healthy	Today	Cloud
Vulnerability Scanner	Healthy	Today	Security
HR	Healthy	Today	HR
Source Control	Warning	Yesterday	Engineering

A failed integration can produce:

False Assurance

Part 41 — Evidence Lineage

Every reported result should trace:

Framework
      ↓
Requirement
      ↓
Control
      ↓
Automated Test
      ↓
Evidence
      ↓
Source System

Example:

PCI DSS
      ↓
Access Control
      ↓
IAM-001
      ↓
T-001
      ↓
E-001
      ↓
Identity Provider

Part 42 — Human Validation

Identify controls where professional judgment remains necessary.

Examples:

Access Reviews

Risk Assessments

Policy Approval

Vendor Due Diligence

Incident Response Testing

Board Oversight

Automation may support:

Evidence Collection

Workflow

Reminders

Status Tracking

but not replace judgment.

Part 43 — Automated Report Limitations

Your final report should acknowledge that automated compliance reporting may fail when:

Integration Scope Is Wrong

Source Data Is Incomplete

Test Logic Is Incorrect

Control Mapping Is Wrong

Evidence Is Stale

Exceptions Are Missing

Population Is Incomplete

Part 44 — Compliance Reporting Controls

The reporting process itself should be governed.

Define:

Report Owner

Data Owners

Review Frequency

Approval

Distribution

Access

Retention

Part 45 — Role-Based Reporting

Different audiences should receive different detail.

Executive

Material Compliance Exposure

Critical Failures

Risk

Remediation

Compliance Manager

Framework Status

Evidence

Exceptions

Assessments

Control Owner

My Failed Controls

My Evidence

My Remediation

Part 46 — Final Management Report

Your final report should contain:

Executive Summary

Framework Status

Critical Control Failures

Evidence Health

Exceptions

Remediation

Compliance Trends

Management Actions

Example Executive Summary

Current compliance monitoring identified
four failed controls and one control requiring
manual review.

The most significant exposure is a critical
internet-facing vulnerability that has exceeded
the approved remediation SLA.

Two active privileged accounts also remain
without MFA.

Remediation is currently assigned to Security
and IAM teams.

No evidence gaps were identified, but one
quarterly evidence item requires refresh.

This is more useful than simply reporting:

Compliance = 75%

Final Validation Checklist

Scope

frameworks identified.
systems identified.
populations understood.
authoritative sources identified.
integration scope validated.

Controls

common controls created.
owners assigned.
criticality assigned.
frequency defined.
automation type defined.

Mapping

controls mapped to frameworks.
duplicate controls avoided.
mappings validated.

Evidence

evidence sources identified.
evidence timestamps maintained.
evidence freshness evaluated.
evidence mapped to controls.
evidence lineage documented.

Automated Tests

test logic documented.
target defined.
current result recorded.
results calculated.
failures validated.

Exceptions

exceptions documented.
risk assigned.
owners assigned.
approvals recorded.
compensating controls captured.
expiry dates established.

Remediation

action owner assigned.
severity documented.
due dates established.
status tracked.
updated evidence collected.
retesting performed.

Reporting

framework results calculated.
critical failures highlighted.
evidence health displayed.
exception status reported.
remediation status reported.
management attention identified.

Lab Deliverables

At the end of this lab, you should have:

Automated-Compliance-Reporting.xlsx

containing:

01 Management Summary

02 Control Register

03 Framework Mapping

04 Evidence Register

05 Automated Tests

06 Exceptions

07 Remediation

08 Reporting

You should also be able to demonstrate this workflow:

Source System
      ↓
Evidence
      ↓
Control
      ↓
Test
      ↓
Framework
      ↓
Exception
      ↓
Remediation
      ↓
Report

Real-World Skills Developed

This lab develops skills in:

Compliance Automation

Control Mapping

Evidence Automation

Evidence Validation

Continuous Compliance

Framework Mapping

Exception Management

Control Testing

Remediation Tracking

Audit Readiness

Management Reporting

These skills are directly relevant to roles such as:

GRC Analyst

Compliance Analyst

Cyber Risk Analyst

Security Compliance Analyst

Technology Risk Analyst

GRC Automation Analyst

Compliance Engineer

Security Governance Analyst

Lab Completion Questions

Before marking this lab complete, answer:

Which controls can be fully automated?
Which controls require human review?
What is the authoritative evidence source for each control?
Are all evidence populations complete?
Which evidence is stale?
Which controls currently fail?
Which failed controls represent the greatest risk?
Which failed tests are genuine exceptions?
Which failures have approved exceptions?
Which exceptions require remediation?
Which framework is most affected?
Which controls support multiple frameworks?
Are there any duplicate controls?
Which remediation action has the highest priority?
Has updated evidence been collected after remediation?
Has the failed control been retested?
Are integrations healthy?
Can every compliance result be traced to evidence?
Can every evidence item be traced to a source system?
Does the management summary identify meaningful risks rather than only percentages?

Lab Success Criteria

You have successfully completed this mission when you can move from:

Raw Technical Data

to:

Control Evidence

then:

Compliance Status

then:

Exception & Risk

and finally:

Management Action

The final workflow should demonstrate:

Collect
      ↓
Evaluate
      ↓
Validate
      ↓
Report
      ↓
Remediate
      ↓
Retest

That is the foundation of automated compliance reporting.

What’s Next?

➡️ Next: Lab 03 — Risk Dashboard

In the next lab, you will move from automated compliance reporting into enterprise risk analytics.

You will build a practical dashboard covering:

Risk Register
      ↓
Inherent Risk
      ↓
Residual Risk
      ↓
Risk Appetite
      ↓
KRIs
      ↓
Risk Trends
      ↓
Treatment Plans
      ↓
Executive Reporting

The goal will be to transform a traditional risk register into an operational and executive risk intelligence dashboard that helps management understand where risk is increasing, where appetite is being exceeded, and where treatment requires intervention.