sample-sechub

gistfile1.txt

import boto3
from datetime import datetime, timedelta
import pandas as pd
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, PatternFill
from openpyxl.worksheet.table import Table, TableStyleInfo


def get_resource_name(resource_id):
    resource_tagging = boto3.client('resourcegroupstaggingapi')
    name_tag = None

    try:
        response = resource_tagging.get_resources(ResourceARNList=[resource_id])
        for resource in response['ResourceTagMappingList']:
            for tag in resource['Tags']:
                if tag['Key'] == 'Name':
                    name_tag = tag['Value']
                    break
#    except Exception as e:
#        print(f"Error getting 'Name' tag for {resource_id}: {e}")
    except:
        name_tag='NOTFOUND'
    print(f'Resource name for {resource_id} is {name_tag}')
    return name_tag


# Create a Security Hub client
securityhub = boto3.client('securityhub')

# Get the current time and the time 2 days ago
now = datetime.utcnow()
two_days_ago = now - timedelta(days=2)

# Initialize the paginator
paginator = securityhub.get_paginator('get_findings')

# Get a list of all findings from the last 2 days
finding_iterator = paginator.paginate(
    Filters={
        'SeverityLabel': [{'Value': 'HIGH', 'Comparison':'EQUALS'}, {'Value': 'MEDIUM','Comparison':'EQUALS'}],
        'LastObservedAt': [{'DateRange': { 'Value': 2, 'Unit':'DAYS'}}],
        'RecordState': [{'Value': 'ACTIVE', 'Comparison': 'EQUALS'}],
        'Region': [{'Value': 'us-west-2', 'Comparison': 'EQUALS'}],
        'ComplianceStatus': [{'Value': 'FAILED', 'Comparison': 'EQUALS'}]
    }
)

# Loop through each finding and store them in a list
findings_list = []
for page in finding_iterator:
    for finding in page['Findings']:
        findings_list.append(finding)

# Create a DataFrame from the list of findings
#findings_df = pd.DataFrame(findings_list)
findings_df = pd.json_normalize(findings_list)
print(findings_df.columns)

# Create a new column 'Name' and populate it with the 'Name' tag values
findings_df['Name'] = findings_df['ProductFields.Resources:0/Id'].apply(get_resource_name)
#ProductFields.Resources:0/Id

# Insert the new column 'Name' after the 'ProductFields.Resources:0/id' column
resource_id_column_index = findings_df.columns.get_loc('ProductFields.Resources:0/Id')
findings_df.insert(resource_id_column_index + 1, 'Name', findings_df.pop('Name'))

# Filter the DataFrame to only include findings with a 'Severity.Original' of HIGH
findings_df = findings_df[findings_df['Severity.Original'] == 'HIGH']

# Sort the DataFrame by 'Severity.Original' and 'Title'
findings_df = findings_df.sort_values(by=['Severity.Original', 'Title'])

# Create a new Excel workbook and add a worksheet
workbook = Workbook()
worksheet = workbook.active

# Write the DataFrame to the worksheet
for r, row in enumerate(findings_df.values, start=1):
    for c, col in enumerate(row, start=1):
        _ = worksheet.cell(row=r, column=c, value=col)

# Apply bold font to the header row
for cell in worksheet["1:1"]:
    cell.font = Font(bold=True)

# Set the built-in filter
worksheet.auto_filter.ref = f"A1:{get_column_letter(worksheet.max_column)}1"

# Define fill colors

header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
odd_row_fill = PatternFill(start_color="E7E6E6", end_color="E7E6E6", fill_type="solid")

# Apply fill colors to the header row and alternate rows
for row in worksheet.iter_rows():
    for cell in row:
        if cell.row == 1:
            cell.fill = header_fill
        elif cell.row % 2 == 0:
            cell.fill = odd_row_fill

# Save the workbook to a file
workbook.save("security_hub_findings.xlsx")