Skip to content
This repository was archived by the owner on Jan 24, 2025. It is now read-only.

Closed POA&M Mapping

Jump to bottom
Austin Songer, CISSP edited this page Dec 23, 2024 · 2 revisions

Closed POA&M Mapping refers to the process of aligning resolved (closed) POA&M items with the corresponding fields in the Closed POA&M Items sheet of the FedRAMP-compliant Excel template. This ensures that all resolved items are accurately documented and tracked for compliance and audit purposes.

Key Components of Closed POA&M Mapping

  1. Data Source:

    • Closed POA&M items are pulled from the POAM database table with the status="Resolved".
    • These represent issues that have been fully remediated.

  2. Database Model:

    • The POAM model stores all POA&M items, including both active and resolved.

    • Example model:

      class POAM(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      controls = db.Column(db.String(255), nullable=True)
      weakness_name = db.Column(db.String(255), nullable=False)
      weakness_description = db.Column(db.Text, nullable=True)
      detector_source = db.Column(db.String(50), nullable=True)
      source_identifier = db.Column(db.String(50), nullable=True)
      asset_identifier = db.Column(db.Text, nullable=True)
      point_of_contact = db.Column(db.String(100), nullable=True)
      resources_required = db.Column(db.String(255), nullable=True)
      remediation_plan = db.Column(db.Text, nullable=True)
      detection_date = db.Column(db.DateTime, nullable=True)
      completion_date = db.Column(db.DateTime, nullable=True)
      planned_milestones = db.Column(db.Text, nullable=True)
      milestone_changes = db.Column(db.Text, nullable=True)
      status_date = db.Column(db.DateTime, nullable=True)
      vendor_dependency = db.Column(db.String(10), nullable=True)
      vendor_checkin_date = db.Column(db.DateTime, nullable=True)
      product_name = db.Column(db.String(255), nullable=True)
      original_risk_rating = db.Column(db.String(50), nullable=True)
      adjusted_risk_rating = db.Column(db.String(50), nullable=True)
      risk_adjustment = db.Column(db.String(10), nullable=True)
      false_positive = db.Column(db.String(10), nullable=True)
      operational_requirement = db.Column(db.String(10), nullable=True)
      deviation_rationale = db.Column(db.Text, nullable=True)
      supporting_documents = db.Column(db.Text, nullable=True)
      comments = db.Column(db.Text, nullable=True)
      auto_approve = db.Column(db.String(10), nullable=True)
      bod_22_01_tracking = db.Column(db.String(10), nullable=True)
      bod_22_01_due_date = db.Column(db.DateTime, nullable=True)
      cve = db.Column(db.String(255), nullable=True)
      service_name = db.Column(db.Text, nullable=True)
      status = db.Column(db.String(20), default="Active")  # Status (Active/Resolved)

  3. Excel Template Mapping:

    • Each column in the Closed POA&M Items sheet corresponds to a field in the POAM database model.

    • Example mapping:

      Excel Column Database Field Description
      A (POAM ID) POAM.id Unique ID for the POA&M item
      B (Controls) POAM.controls Applicable control (e.g., AC-2, SC-12)
      C (Weakness Name) POAM.weakness_name Name of the vulnerability or issue
      D (Weakness Description) POAM.weakness_description Detailed description of the weakness
      E (Weakness Detector Source) POAM.detector_source Tool or source that detected the weakness
      F (Weakness Source Identifier) POAM.source_identifier Plugin ID or other unique identifier
      G (Asset Identifier) POAM.asset_identifier Affected asset(s)
      ... ... ...

  4. Excel Population Logic:

    • During export, the app retrieves resolved POA&M items (status="Resolved") and dynamically writes them into the Closed POA&M Items sheet in the Excel template.

Closed POA&M Mapping Logic

Data Retrieval

Fetch the resolved (closed) POA&M items from the database:

def get_closed_poams():
 """
 Fetch all closed (resolved) POA&M items from the database.
 """
 return POAM.query.filter_by(status="Resolved").all()

Excel Population Logic

Populate the Closed POA&M Items tab in the template:

from openpyxl import load_workbook
from datetime import datetime

TEMPLATE_PATH = "FedRAMP-POAM-Template.xlsx"

def export_closed_poams():
 # Load the Excel template
 workbook = load_workbook(TEMPLATE_PATH)

 # Fetch Closed POA&M Items
 closed_items = get_closed_poams()
 closed_sheet = workbook["Closed POA&M Items"]

 # Populate Closed POA&M Items sheet
 closed_row = 6  # Assuming data starts at row 6
 for item in closed_items:
     closed_sheet[f"A{closed_row}"] = item.id  # POAM ID
     closed_sheet[f"B{closed_row}"] = item.controls  # Controls
     closed_sheet[f"C{closed_row}"] = item.weakness_name  # Weakness Name
     closed_sheet[f"D{closed_row}"] = item.weakness_description  # Weakness Description
     closed_sheet[f"E{closed_row}"] = item.detector_source  # Weakness Detector Source
     closed_sheet[f"F{closed_row}"] = item.source_identifier  # Weakness Source Identifier
     closed_sheet[f"G{closed_row}"] = item.asset_identifier  # Asset Identifier
     closed_sheet[f"H{closed_row}"] = item.point_of_contact  # Point of Contact
     closed_sheet[f"I{closed_row}"] = item.resources_required  # Resources Required
     closed_sheet[f"J{closed_row}"] = item.remediation_plan  # Overall Remediation Plan
     closed_sheet[f"K{closed_row}"] = datetime.strftime(item.detection_date, "%m/%d/%Y") if item.detection_date else ""  # Detection Date
     closed_sheet[f"L{closed_row}"] = datetime.strftime(item.completion_date, "%m/%d/%Y") if item.completion_date else ""  # Completion Date
     closed_sheet[f"M{closed_row}"] = item.planned_milestones  # Planned Milestones
     closed_sheet[f"N{closed_row}"] = item.milestone_changes  # Milestone Changes
     closed_sheet[f"O{closed_row}"] = datetime.strftime(item.status_date, "%m/%d/%Y") if item.status_date else ""  # Status Date
     closed_sheet[f"P{closed_row}"] = item.vendor_dependency  # Vendor Dependency
     closed_sheet[f"Q{closed_row}"] = datetime.strftime(item.vendor_checkin_date, "%m/%d/%Y") if item.vendor_checkin_date else ""  # Vendor Check-in Date
     closed_sheet[f"R{closed_row}"] = item.product_name  # Vendor Dependent Product Name
     closed_sheet[f"S{closed_row}"] = item.original_risk_rating  # Original Risk Rating
     closed_sheet[f"T{closed_row}"] = item.adjusted_risk_rating  # Adjusted Risk Rating
     closed_sheet[f"U{closed_row}"] = item.risk_adjustment  # Risk Adjustment
     closed_sheet[f"V{closed_row}"] = item.false_positive  # False Positive
     closed_sheet[f"W{closed_row}"] = item.operational_requirement  # Operational Requirement
     closed_sheet[f"X{closed_row}"] = item.deviation_rationale  # Deviation Rationale
     closed_sheet[f"Y{closed_row}"] = item.supporting_documents  # Supporting Documents
     closed_sheet[f"Z{closed_row}"] = item.comments  # Comments
     closed_sheet[f"AA{closed_row}"] = item.auto_approve  # Auto-Approve
     closed_sheet[f"AB{closed_row}"] = item.bod_22_01_tracking  # Binding Operational Directive 22-01 tracking
     closed_sheet[f"AC{closed_row}"] = datetime.strftime(item.bod_22_01_due_date, "%m/%d/%Y") if item.bod_22_01_due_date else ""  # BOD 22-01 Due Date
     closed_sheet[f"AD{closed_row}"] = item.cve  # CVE
     closed_sheet[f"AE{closed_row}"] = item.service_name  # Service Name
     closed_row += 1

 # Save the workbook
 output_path = "Exported_FedRAMP_POAM.xlsx"
 workbook.save(output_path)
 return output_path

Example Output in Excel

Closed POA&M Items Sheet:

POAM ID Controls Weakness Name Weakness Description Detector Source Source Identifier Asset Identifier Point of Contact Resources Required ...
2001 AC-3 SQL Injection SQL vulnerability in DB AWS Inspector Plugin-12345 db1.example.com John Doe Additional Funding ...
2002 SC-12 Misconfigured TLS TLS 1.0 enabled on server Tenable Plugin-67890 web1.example.com Jane Smith N/A ...
Clone this wiki locally