prepare_mapping.py

import openpyxl
import argparse
import re
import yaml
from pprint import pprint
from collections import defaultdict


parser = argparse.ArgumentParser(
    prog="prepare_mapping.py",
    description="prepare a mapping excel file for CISO Assistant",
)
parser.add_argument("source_yaml")
parser.add_argument("target_yaml")
args = parser.parse_args()
packager = "intuitem"

print("parsing", args.source_yaml, args.target_yaml)
with open(args.source_yaml, "r") as file:
    source = yaml.safe_load(file)
with open(args.target_yaml, "r") as file:
    target = yaml.safe_load(file)

source_library_urn = source["urn"]
target_library_urn = target["urn"]
source_framework_urn = source["objects"]["framework"]["urn"]
target_framework_urn = target["objects"]["framework"]["urn"]
source_framework_ref_id = source["objects"]["framework"]["ref_id"]
target_framework_ref_id = target["objects"]["framework"]["ref_id"]
source_framework_name = source["objects"]["framework"]["name"]
target_framework_name = target["objects"]["framework"]["name"]

source_ref_id = source_framework_urn.split(":")[-1]
target_ref_id = target_framework_urn.split(":")[-1]
ref_id = "mapping-" + source_ref_id + "-to-" + target_ref_id
name = f"{source_framework_ref_id} -> {target_framework_ref_id}"
description = f"Mapping from {source_framework_name} to {target_framework_name}"
output_file_name = f"{ref_id}.xlsx"

wb_output = openpyxl.Workbook()
ws = wb_output.active
ws.title = "library_content"
ws.append(["library_urn", f"urn:{packager.lower()}:risk:library:{ref_id}"])
ws.append(["library_version", "1"])
ws.append(["library_locale", source["locale"]])
ws.append(["library_ref_id", ref_id])
ws.append(["library_name", name])
ws.append(["library_description", description])
ws.append(["library_copyright", packager])
ws.append(["library_provider", packager])
ws.append(["library_packager", packager])
ws.append(["library_dependencies", f"{source_library_urn}, {target_library_urn}"])
ws.append(
    ["mapping_urn", f"urn:{packager.lower()}:risk:req_mapping_set:{source_ref_id}"]
)
ws.append(["mapping_ref_id", ref_id])
ws.append(["mapping_name", name])
ws.append(["mapping_description", description])
ws.append(["mapping_source_framework_urn", source_framework_urn])
ws.append(["mapping_target_framework_urn", target_framework_urn])
ws.append(
    [
        "mapping_source_node_base_urn",
        f"urn:{packager.lower()}:risk:req_node:{source_ref_id}",
    ]
)
ws.append(
    [
        "mapping_target_node_base_urn",
        f"urn:{packager.lower()}:risk:req_node:{target_ref_id}",
    ]
)
ws.append(["tab", "mappings", "mappings"])

ws1 = wb_output.create_sheet("mappings")
ws1.append(
    [
        "source_node_id",
        "target_node_id",
        "relationship",
        "rationale",
        "strength_of_relationship",
    ]
)
for node in source["objects"]["framework"]["requirement_nodes"]:
    if node["assessable"]:
        node_id = node["urn"].split(":")[-1]
        ws1.append([node_id])

ws2 = wb_output.create_sheet("guidelines")
ws2.append(["source_node_id", "use the last segment of the source URN"])
ws2.append(["target_node_id", "use the last segment of the target URN"])
ws2.append(["relationships", "use one of the following values"])
ws2.append(["", "subset"])
ws2.append(["", "intersect"])
ws2.append(["", "equal"])
ws2.append(["", "superset"])
ws2.append(["", "not_related"])
ws2.append(["rationale", "use one of the following values (or leave empty)"])
ws2.append(["", "syntactic"])
ws2.append(["", "semantic"])
ws2.append(["", "functional"])
ws2.append(["strength_of_relationship", "use an integer or empty value"])

ws3 = wb_output.create_sheet("source")
ws3.append(["node_id", "assessable", "urn", "ref_id", "name", "description"])
for node in source["objects"]["framework"]["requirement_nodes"]:
    node_id = node["urn"].split(":")[-1] if node["assessable"] else ""
    ws3.append(
        [
            node_id,
            node["assessable"],
            node["urn"],
            node.get("ref_id"),
            node.get("name"),
            node.get("description"),
        ]
    )

ws4 = wb_output.create_sheet("target")
ws4.append(["node_id", "assessable", "urn", "ref_id", "name", "description"])
for node in target["objects"]["framework"]["requirement_nodes"]:
    node_id = node["urn"].split(":")[-1] if node["assessable"] else ""
    ws4.append(
        [
            node_id,
            node["assessable"],
            node["urn"],
            node.get("ref_id"),
            node.get("name"),
            node.get("description"),
        ]
    )

print("generate ", output_file_name)
wb_output.save(output_file_name)