# Import necessary libraries
import openpyxl
import os
import glob
import yaml

# Set the directory containing the YAML files to be read
sigma_rules_directory = "rules"

# Set the filename for the Excel file to be created
excel_filename = "rules.xlsx"

# Create a new workbook and worksheet using OpenPyXL
workbook = openpyxl.Workbook()
worksheet = workbook.active

# Write the headers for the columns in the first row of the worksheet
worksheet.cell(row=1, column=1, value="Title")
worksheet.cell(row=1, column=2, value="Description")
worksheet.cell(row=1, column=3, value="Tags")
worksheet.cell(row=1, column=4, value="Logsource")

# Find all YAML files in the specified directory
rule_files = glob.glob(os.path.join(sigma_rules_directory, "*.yml"))

# Loop over each YAML file and extract the relevant data
row = 2
for rule_file in rule_files:
    with open(rule_file, "r") as f:
        # Load the YAML file using PyYAML
        rule = yaml.safe_load(f)

        # Check if the rule has the required fields
        # Using only valid MITRE ATT&CK tactic and techniques tags
        if ("logsource" in rule and len(rule["logsource"]) > 0) and ("tags" in rule and len(rule["tags"]) > 0 and not (rule["tags"][0].startswith("attack.t") or rule["tags"][0].startswith("attack.s") or rule["tags"][0].startswith("attack.g") or rule["tags"][0].startswith("cve"))):
            # Extract the required data from the rule
            title = rule["title"]
            description = rule["description"]
            if "tags" in rule:
                tags = ", ".join(rule["tags"])
            else:
                tags = ""
            # Deleting SIGMA Log Source categorization, mapping is used only to one string
            logsource = rule["logsource"]
            logsource_values = [logsource.get("product", ""), logsource.get("category", ""),
                                logsource.get("service", "")]
            logsource_values = [v for v in logsource_values if v]  # remove empty values
            logsource_str = ", ".join(logsource_values)

            # Write the data to the next row in the worksheet
            worksheet.cell(row=row, column=1, value=title)
            worksheet.cell(row=row, column=2, value=description)
            worksheet.cell(row=row, column=3, value=tags)
            worksheet.cell(row=row, column=4, value=logsource_str)

            # Increment the row counter
            row += 1

# Save the workbook to the specified Excel file
workbook.save(excel_filename)

# After saving, linux and windows log sources are united to "windows" / "linux".
# After saving, use cases are selected manually ! Not all of them are used
