Exported Excel Data

The exported Excel functionality in the POA&M Management App is designed to generate a FedRAMP-compliant Excel file containing detailed records of POA&M items, Configuration Findings, and associated compliance data. It uses predefined templates and maps data directly from the app's database to specific fields in the Excel sheets.

---

Key Components of Excel Export

1. Excel Template:
   • The app uses a preformatted FedRAMP-compliant Excel template (FedRAMP-POAM-Template.xlsx).
   • This template includes three primary sheets:
     • Open POA&M Items: Contains active POA&M items.
     • Closed POA&M Items: Contains resolved POA&M items.
     • Configuration Findings: Contains findings from configuration scans (e.g., CIS scans).
2. Database Integration:
   • The app queries the database (POAM, ConfigurationFinding, etc.) to fetch the required data.
   • Data is mapped to the appropriate fields in the Excel template based on predefined mappings.
3. Dynamic Data Population:
   • The app dynamically populates rows and columns in the Excel template with fetched data, ensuring that all necessary fields are accurately filled.
4. Excel Formatting:
   • The app preserves the template’s formatting, including headers, cell styles, and column widths.
5. File Saving:
   • The populated Excel file is saved as a new file (e.g., Exported_FedRAMP_POAM.xlsx) to avoid overwriting the template.
   • The file is then sent to the user for download.

---

How Export Works

1. User Request:

   • The user triggers an export action from the app (e.g., clicking "Export POA&M" or "Export Configuration Findings").

2. Data Retrieval:

   • The app queries the database for:
     • Active POA&M Items: For the "Open POA&M Items" sheet.
     • Resolved POA&M Items: For the "Closed POA&M Items" sheet.
     • Configuration Findings: For the "Configuration Findings" sheet.

3. Excel Manipulation:

   • The app uses the openpyxl library to load the Excel template and manipulate its contents.

4. Field Mapping:

   • Each column in the template is mapped to a specific field in the database.

   • Example mappings for

     Open POA&M Items:

     • Column A (POAM ID) -> POAM.id
     • Column B (Controls) -> POAM.controls
     • Column C (Weakness Name) -> POAM.weakness_name
     • And so on.

5. Saving and Sending:

   • The updated Excel file is saved.
   • It is then provided to the user as a downloadable file using Flask's send_file().

---

Detailed Example: Exporting POA&M Data

Database Models:

class POAM(db.Model):
 id = db.Column(db.Integer, primary_key=True)
 controls = db.Column(db.String(255))
 weakness_name = db.Column(db.String(255))
 weakness_description = db.Column(db.Text)
 detector_source = db.Column(db.String(50))
 detection_date = db.Column(db.DateTime)
 # Other fields...

Template Field Mappings:

Excel Column	Database Field	Sheet
A (POAM ID)	POAM.id	Open POA&M Items
B (Controls)	POAM.controls	Open POA&M Items
C (Weakness Name)	POAM.weakness_name	Open POA&M Items
...	...	...
A (POAM ID)	POAM.id	Closed POA&M Items
A (POAM ID)	ConfigurationFinding.id	Configuration Findings

Export Logic:

from openpyxl import load_workbook
from datetime import datetime
from flask import send_file

TEMPLATE_PATH = "FedRAMP-POAM-Template.xlsx"

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

 # Fetch data from the database
 open_items = POAM.query.filter_by(status="Active").all()
 closed_items = POAM.query.filter_by(status="Resolved").all()
 configuration_findings = ConfigurationFinding.query.all()

 # Populate Open POA&M Items
 open_sheet = workbook["Open POA&M Items"]
 open_row = 6
 for item in open_items:
     open_sheet[f"A{open_row}"] = item.id
     open_sheet[f"B{open_row}"] = item.controls
     open_sheet[f"C{open_row}"] = item.weakness_name
     # Add other fields...
     open_row += 1

 # Populate Closed POA&M Items
 closed_sheet = workbook["Closed POA&M Items"]
 closed_row = 6
 for item in closed_items:
     closed_sheet[f"A{closed_row}"] = item.id
     closed_sheet[f"B{closed_row}"] = item.controls
     closed_sheet[f"C{closed_row}"] = item.weakness_name
     # Add other fields...
     closed_row += 1

 # Populate Configuration Findings
 config_sheet = workbook["Configuration Findings"]
 config_row = 6
 for finding in configuration_findings:
     config_sheet[f"A{config_row}"] = finding.id
     config_sheet[f"B{config_row}"] = finding.controls
     config_sheet[f"C{config_row}"] = finding.weakness_name
     # Add other fields...
     config_row += 1

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

 return output_path

@app.route('/export-poam', methods=['GET'])
def export_poam():
 file_path = export_poam_with_template()
 return send_file(file_path, as_attachment=True)

---

Key Features of Export

1. Template-Driven:
   • The app uses a fixed template, ensuring compliance with standards like FedRAMP.
2. Dynamic Data Mapping:
   • Data from the database is dynamically written to the correct rows and columns in the Excel sheet.
3. Multiple Sheets:
   • Separate sheets for different types of data (e.g., Open Items, Closed Items, Configuration Findings).
4. Error Handling:
   • Fields with None values are handled gracefully to prevent errors during Excel generation.

---

Example Output

After exporting, the user downloads an Excel file with the following structure:

Open POA&M Items Sheet:

POAM ID	Controls	Weakness Name	Weakness Description	Detector Source	...
1	AC-2	SQL Injection	Vulnerability in DB	AWS Inspector	...
2	SC-12	Misconfiguration	Weak password policy	Tenable	...

Closed POA&M Items Sheet:

POAM ID	Controls	Weakness Name	Weakness Description	Detector Source	...
3	CM-2	Unpatched OS	Windows Server 2019	AWS Inspector	...

Configuration Findings Sheet:

POAM ID	Controls	Weakness Name	Weakness Description	Detector Source	...
4	AC-3	Weak Encryption	Outdated TLS Version	Tenable	...