xlsx-to-oscal-poam Task Tutorial¤

Overview¤

The xlsx-to-oscal-poam task transforms Plan of Action and Milestones (POA&M) Excel spreadsheets into OSCAL POAM JSON format. This task expects input in FedRAMP POAM template structure and creates valid OSCAL v1.1+ POAM documents.

Prerequisites¤

• Trestle installed (see Installation Guide)
• FedRAMP POAM Excel template or compatible spreadsheet
• Basic understanding of OSCAL POAM format
• Initialized trestle workspace (run trestle init in your working directory)

Quick Start¤

1. Create Configuration File¤

Create a configuration file (e.g., .trestle/config.ini):

[task.xlsx-to-oscal-poam]
xlsx-file = path/to/FedRAMP-POAM-Template.xlsx
output-dir = output/poams
title = MySystem Plan of Action and Milestones
version = 1.0

2. Run the Task¤

trestle task xlsx-to-oscal-poam -c .trestle/config.ini

3. Verify Output¤

The task creates output/poams/plan-of-action-and-milestones.json with your OSCAL POAM:

cat output/poams/plan-of-action-and-milestones.json | jq '.plan-of-action-and-milestones.poam-items | length'

Configuration Parameters¤

Required Parameters¤

Optional Parameters¤

Validation Modes¤

• on: Fail task if any validation errors occur (strict mode)
• warn: Log warnings but continue processing (default)
• off: Skip validation (not recommended)

Excel Template Requirements¤

Template Structure¤

The POAM Excel template must have:

• Rows 1-4: Template metadata and instructions
• Row 5: Column headers
• Row 6+: Data rows (one POAM item per row)

Required Columns¤

The following columns are required (task will fail if missing):

1. POAM ID: Unique identifier for each POAM item (e.g., P001)
2. Controls: NIST control IDs (e.g., AC-1, AC-2, SC-7(5))
3. Weakness Name: Title of the weakness/issue
4. Weakness Description: Detailed description

Optional Columns¤

All other columns are optional but recommended:

• Weakness Detector Source
• Weakness Source Identifier (e.g., CVE ID)
• Asset Identifier
• Point of Contact
• Resources Required
• Overall Remediation Plan
• Original Detection Date
• Scheduled Completion Date
• Planned Milestones
• Risk ratings (Original/Adjusted)
• Status fields (Risk Adjustment, False Positive, Operational Requirement)
• And more... (see Column Mapping below)

Column-to-OSCAL Mapping¤

PoamItem Mapping¤

Observation Mapping¤

Risk Mapping¤

Data Format Specifications¤

Control IDs¤

Control IDs must follow the pattern: XX-N or XX-N(N)

Valid examples:

• AC-1 (Access Control family, control 1)
• SC-7(5) (System and Communications Protection family, control 7, enhancement 5)
• AU-2, AU-3, AU-12 (Multiple controls, comma-separated)

Invalid examples:

• ac1 (missing hyphen)
• AC- (missing number)
• A-1 (family must be 2 letters)

Risk Ratings¤

Risk rating values must be:

• Low
• Moderate
• High
• N/A (not applicable)

Case-insensitive, but stored as shown above.

Yes/No/Pending Fields¤

Fields like Risk Adjustment, False Positive, and Operational Requirement accept:

• Yes
• No
• Pending

Case-insensitive.

Dates¤

Dates can be in:

1. Excel date format (automatically detected)
2. ISO 8601 string: YYYY-MM-DDTHH:MM:SS±HH:MM or YYYY-MM-DD

All dates are stored with UTC timezone in the output.

Milestones¤

Milestones can be formatted as:

Milestone 1: Complete analysis by 2024-03-01
Milestone 2: Deploy fix by 2024-06-30

The parser handles:

• Multi-line milestone text (separated by newlines)
• Optional dates (using by YYYY-MM-DD pattern)
• Numbered milestones (Milestone N: or M1:)

Usage Examples¤

Example 1: Basic Usage¤

[task.xlsx-to-oscal-poam]
xlsx-file = FedRAMP-POAM-Template.xlsx
output-dir = output
title = Production System POA&M
version = 1.0

trestle task xlsx-to-oscal-poam -c config.ini

Example 2: With System ID¤

[task.xlsx-to-oscal-poam]
xlsx-file = poam-2024-q1.xlsx
output-dir = output/2024-q1
title = Q1 2024 POA&M
version = 1.0
system-id = prod-system-001

Example 3: Strict Validation¤

[task.xlsx-to-oscal-poam]
xlsx-file = FedRAMP-POAM-Template.xlsx
output-dir = output
title = Validated POA&M
version = 1.0
validate-required-fields = on

This will fail if any required fields are missing.

Example 4: Custom Worksheet¤

[task.xlsx-to-oscal-poam]
xlsx-file = custom-poam.xlsx
output-dir = output
title = Custom POA&M
version = 1.0
work-sheet-name = Closed POA&M Items

Troubleshooting¤

Issue: "Excel file not found"¤

Cause: The xlsx-file path is incorrect or file doesn't exist.

Solution:

• Use absolute paths or paths relative to where you run the command
• Check file name spelling and extension (.xlsx)

Issue: "Worksheet not found"¤

Cause: The specified worksheet name doesn't exist in the Excel file.

Solution:

• Verify worksheet name matches exactly (case-sensitive)
• Default is "Open POA&M Items" - check if your template uses a different name
• Error message lists available worksheets

Issue: "Missing required field"¤

Cause: One or more required columns are missing or have empty values.

Solution:

• Required columns: POAM ID, Controls, Weakness Name, Weakness Description
• Check that column headers match exactly (case-sensitive)
• Ensure data rows have values in required columns

Issue: "Invalid control format"¤

Cause: Control IDs don't match expected pattern.

Solution:

• Use format: XX-N or XX-N(N) (e.g., AC-1, SC-7(5))
• Multiple controls: separate with commas: AC-1, AC-2
• In warn mode, invalid controls are skipped with warnings

Issue: "Invalid risk rating"¤

Cause: Risk rating value is not one of the valid options.

Solution:

• Valid values: Low, Moderate, High, N/A
• Check for typos or extra spaces
• Case-insensitive but must match one of the four values

Issue: Output file already exists¤

Cause: Output file exists and output-overwrite is false.

Solution:

• Set output-overwrite = true to allow overwriting
• Or delete/move the existing output file
• Or change the output-dir to a different location

Advanced Topics¤

UUID Generation Strategy¤

The task uses deterministic UUIDs (UUID5) for stability across runs:

NAMESPACE = 'e8d8efc6-c23e-4e3e-a2e8-bc8fc08ff6c3'

poam_item_uuid = uuid5(NAMESPACE, f'poam-item-{POAM_ID}')
observation_uuid = uuid5(NAMESPACE, f'observation-{POAM_ID}')
risk_uuid = uuid5(NAMESPACE, f'risk-{POAM_ID}')

Benefits:

• Same POAM ID always generates same UUID
• Enables stable updates when re-processing
• Facilitates merging with existing POAM files

Object Linking¤

Each Excel row creates three linked OSCAL objects:

1. PoamItem: Main weakness description

2. Links to → Observation (via related_observations)

3. Links to → Risk (via related_risks)

4. Observation: Detection details

5. Linked from → PoamItem

6. Risk: Risk assessment and remediation

7. Links to → Observation (via related_observations)

8. Linked from → PoamItem

This creates a comprehensive representation of each weakness.

Custom Property Namespaces¤

Properties are created without explicit namespaces by default. To add namespaces:

1. Modify the task source code in PoamBuilder.create_poam_item()
2. Add ns parameter to Property creation:

Property(name='poam-id', value=poam_id, ns='https://example.com/ns/poam')

Future Enhancements¤

Planned Features (Not Yet Implemented)¤

1. Update/Merge Support:

2. Read existing POAM JSON

3. Merge Excel changes with existing data
4. Preserve UUIDs for unchanged items

5. Track modifications

6. Closed POA&M Items Support:

7. Process "Closed POA&M Items" worksheet

8. Set Risk.status to "closed"

9. Validation Against Profile:

10. Validate control IDs against resolved profile catalog

11. Similar to csv-to-oscal-cd control validation

Reference¤

• OSCAL POAM Specification
• FedRAMP POAM Requirements
• NIST SP 800-53 Controls
• Trestle Documentation

Getting Help¤

• GitHub Issues: compliance-trestle/issues
• Command help: trestle task xlsx-to-oscal-poam -h
• Task info: trestle task xlsx-to-oscal-poam -i