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

Latest commit

 

History

History
96 lines (74 loc) · 5.17 KB

README.md

File metadata and controls

96 lines (74 loc) · 5.17 KB

POAM Automation

  • Python Script to Pull AWS Inspector Results
  • Python Script to Push AWS Inspector Results to POAM
  • Python Script to Merge Tenable Audit Scans and Vulnerability Scans
  • Python Script that updates a POAM using Excel in Git Repo.
    • Still getting errors
  • Python script that updates a POAM using the Google Sheets API, running from a GitHub Action.
  • Python Script

Scripts

merge.py

Constants

  • DIRECTORY: Specifies the folder where the CSV files are stored.
  • OUTPUT_FILE: Location where the merged CSV will be saved.
  • FILE_EXTENSION: Specifies that only .csv files should be processed.

Functions

  1. load_and_label_csv(file_path, scan_type):
    • This function loads a CSV file into a Pandas DataFrame and adds a column to label the type of scan (STIG or vulnerability).
    • If there’s an issue reading the file, it catches the error and returns an empty DataFrame.
  2. identify_scan_type(file_name):
    • Determines the scan type based on the file name using regular expressions. If the name contains “AWS_CIS” or “STIG,” it’s labeled as “STIG Scan”; otherwise, it’s labeled as “Vulnerability Scan.”
  3. split_multi_value_column(df, column_name):
    • Some rows in the CSV might have multiple values in a single cell (e.g., newline-separated). This function splits such values into separate rows.
    • This ensures each value in the specified column (“Weakness Description”) appears in its own row.
  4. merge_csv_files(directory):
    • This is the core function that merges all CSV files from the given directory.
    • It iterates over all files, identifies their scan type, and loads them using the previous functions.
    • If a file has a “Weakness Description” column, it splits any multi-value cells.
    • Finally, it concatenates all DataFrames into one, and rearranges the columns to have “Scan_Type” at the front.
  5. save_to_csv(df, output_file):
    • Saves the merged DataFrame to the specified CSV file.

Main Execution (if name == "main":)

  • This section calls the merge_csv_files function to process the CSVs and then saves the result to OUTPUT_FILE.
  • A message is printed once the CSV is successfully saved.

paom-update-repo.py

File Paths

  • merged_vulnerability_data_path: Path to the CSV file containing the merged vulnerability data.
  • poam_excel_path: Path to the Excel file containing the POAM data.

Step-by-Step Breakdown

  1. Load the Data:
    • merged_df: Loads the vulnerability data from the CSV file while skipping the first two rows (likely to skip metadata or header information).
    • poam_df: Loads the POAM data from the Excel file using the openpyxl engine, again skipping the first two rows.
  2. Debugging:
    • print(merged_df.columns): Prints the column names of the vulnerability data to check if they match expected values for mapping.
    • print(merged_df.head()): Displays the first few rows of the merged vulnerability data for inspection.
  3. Mapping:
    • The mapping dictionary defines how columns in the Tenable vulnerability data (merged_df) should map to the POAM (poam_df).
    • For example, the “Plugin” column in the vulnerability data corresponds to the “Weakness Source Identifier” in the POAM.
  4. Check for New Vulnerabilities:
    • The script iterates over each row of the merged vulnerability data.
    • For each row, it checks if the combination of “Plugin ID” and “Host” from the vulnerability data exists in the POAM.
    • If this combination doesn’t exist in the POAM (i.e., it’s a new vulnerability), it finds the first empty row in the POAM and adds the new vulnerability.
    • It populates the row based on the predefined column mappings (mapping).
  5. Handling Errors:
    • KeyError: If any of the expected columns (like ‘Plugin’ or ‘Host’) are missing, it catches the error and prints a message.
    • IndexError: If there are no empty rows left in the POAM to add new vulnerabilities, it stops and prints a message.
  6. Save the Updated POAM:
    • The updated POAM is saved to a new Excel file, updated_poam_excel_path, using the openpyxl engine.

paom-update-gsheet.py

  1. Imports:
    • The code uses pandas to handle CSV data and gspread to interact with Google Sheets.
    • ServiceAccountCredentials is used to authenticate with Google APIs via a service account.
  2. Authentication:
    • The scope defines the permissions needed for accessing Google Sheets and Google Drive.
    • Credentials are loaded from a JSON file (creds.json), which contains the service account details.
  3. Accessing Google Sheets:
    • The code opens a Google Sheet using the sheet ID.
    • It accesses the first worksheet (get_worksheet(0)), assuming that’s where the data needs to be updated.
  4. Loading CSV Data:
    • A CSV file (merged_vulnerability_data.csv) is loaded into a Pandas DataFrame for easy manipulation.
  5. Converting Data:
    • The DataFrame is converted into a list of lists (df.values.tolist()) so it can be pushed into Google Sheets.
  6. Updating Google Sheets:
    • The update function uploads the list data to the Google Sheet, starting from cell A1.