| from openpyxl import Workbook |
| from openpyxl.styles import Font, PatternFill, Alignment, Border, Side |
| from openpyxl.utils import get_column_letter |
| from openpyxl.worksheet.datavalidation import DataValidation |
| from openpyxl.formatting.rule import CellIsRule |
|
|
| wb = Workbook() |
|
|
| |
| C = { |
| "navy": "FF1B2A4A", |
| "blue": "FF2E5F8A", |
| "mid_blue": "FF3A7BBF", |
| "pale_blue": "FFD6E8F7", |
| "ice": "FFEEF5FB", |
| "green_dk": "FF1E5631", |
| "green_lt": "FFC6EFCE", |
| "red_dk": "FF9C0006", |
| "red_lt": "FFFFC7CE", |
| "amber_dk": "FF7F4C00", |
| "amber_lt": "FFFFEB9C", |
| "purple_dk": "FF4B0082", |
| "purple_lt": "FFE8DAEF", |
| "gray_dk": "FF595959", |
| "gray_lt": "FFF2F2F2", |
| "white": "FFFFFFFF", |
| "yellow": "FFFFF2CC", |
| "orange_lt": "FFFCE4D6", |
| "orange_dk": "FF833C00", |
| } |
|
|
| def thin_border(color="FFBFBFBF"): |
| s = Side(style="thin", color=color) |
| return Border(left=s, right=s, top=s, bottom=s) |
|
|
| def med_border(): |
| m = Side(style="medium", color="FF1B2A4A") |
| return Border(left=m, right=m, top=m, bottom=m) |
|
|
| def style(ws, cell_ref, value=None, bg=None, fg="FF000000", bold=False, size=10, |
| halign="left", valign="center", wrap=False, italic=False, border=True, num_fmt=None): |
| c = ws[cell_ref] |
| if value is not None: |
| c.value = value |
| c.font = Font(name="Calibri", bold=bold, color=fg, size=size, italic=italic) |
| if bg: |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal=halign, vertical=valign, wrap_text=wrap) |
| if border: |
| c.border = thin_border() |
| if num_fmt: |
| c.number_format = num_fmt |
|
|
| def hdr(ws, cell_ref, value, bg=None, fg="FFFFFFFF", bold=True, size=10, |
| halign="center", wrap=True): |
| bg = bg or C["navy"] |
| style(ws, cell_ref, value, bg=bg, fg=fg, bold=bold, size=size, |
| halign=halign, valign="center", wrap=wrap) |
|
|
| def section_hdr(ws, cell_ref, value, bg=None): |
| bg = bg or C["blue"] |
| c = ws[cell_ref] |
| c.value = value |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=11) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False) |
| c.border = thin_border("FFFFFFFF") |
|
|
| def dv_list(ws, sqref, formula): |
| dv = DataValidation(type="list", formula1=formula, allow_blank=True, showErrorMessage=False) |
| dv.sqref = sqref |
| ws.add_data_validation(dv) |
|
|
| def cf_equal(ws, range_, formula, fill_color): |
| fill = PatternFill("solid", start_color=fill_color) |
| ws.conditional_formatting.add(range_, CellIsRule(operator="equal", formula=[formula], fill=fill)) |
|
|
| STATUS_DV = '"Not Started,In Progress,Implemented,Not Applicable,Partially Implemented"' |
| RISK_DV = '"Critical,High,Medium,Low,Informational"' |
| MATURITY_DV = '"0-Non-existent,1-Initial,2-Repeatable,3-Defined,4-Managed,5-Optimized"' |
| PRIORITY_DV = '"P1-Immediate,P2-High,P3-Medium,P4-Low"' |
| YESNO_DV = '"Yes,No,Partial,N/A"' |
|
|
| |
| |
| |
| ws_cover = wb.active |
| ws_cover.title = "Cover" |
| ws_cover.sheet_view.showGridLines = False |
| ws_cover.sheet_properties.tabColor = C["navy"] |
|
|
| for col in range(1, 9): |
| ws_cover.column_dimensions[get_column_letter(col)].width = 22 |
| ws_cover.row_dimensions[1].height = 8 |
| ws_cover.row_dimensions[2].height = 60 |
| ws_cover.row_dimensions[3].height = 30 |
|
|
| ws_cover.merge_cells("A2:H2") |
| c = ws_cover["A2"] |
| c.value = "ISO/IEC 27001:2022 | ISMS IMPLEMENTATION & AUDIT TRACKER" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=20) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| ws_cover.merge_cells("A3:H3") |
| c = ws_cover["A3"] |
| c.value = "Information Security Management System — Full Controls Coverage (Clauses 4–10 + Annex A)" |
| c.font = Font(name="Calibri", italic=True, color="FFFFFFFF", size=12) |
| c.fill = PatternFill("solid", start_color=C["blue"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| |
| info_rows = [ |
| (5, "Organization Name", ""), |
| (6, "Organization Scope", ""), |
| (7, "ISMS Scope Statement", ""), |
| (8, "Certification Body", ""), |
| (9, "Lead Auditor", ""), |
| (10, "Internal Audit Lead", ""), |
| (11, "Stage 1 Audit Date", ""), |
| (12, "Stage 2 Audit Date", ""), |
| (13, "Surveillance Audit 1", ""), |
| (14, "Surveillance Audit 2", ""), |
| (15, "Recertification Date", ""), |
| (16, "Document Version", "1.0"), |
| (17, "Last Updated", ""), |
| (18, "Prepared By", ""), |
| (19, "Approved By", ""), |
| ] |
|
|
| ws_cover.merge_cells("A4:H4") |
| hdr(ws_cover, "A4", "ORGANIZATION & AUDIT INFORMATION", C["navy"], "FFFFFFFF", True, 12, "left") |
|
|
| for row, label, default in info_rows: |
| ws_cover.row_dimensions[row].height = 22 |
| ws_cover.merge_cells(f"A{row}:C{row}") |
| style(ws_cover, f"A{row}", label, bg=C["pale_blue"], fg=C["navy"], bold=True, size=10, halign="right") |
| ws_cover.merge_cells(f"D{row}:H{row}") |
| style(ws_cover, f"D{row}", default, bg=C["white"], fg="FF000000", bold=False, size=10, halign="left") |
|
|
| |
| ws_cover.merge_cells("A21:H21") |
| hdr(ws_cover, "A21", "LIVE COMPLIANCE DASHBOARD", C["navy"], "FFFFFFFF", True, 12, "left") |
|
|
| ws_cover.row_dimensions[22].height = 20 |
| for col_letter, label in zip(["A","B","C","D","E","F","G","H"], |
| ["Clause/Domain","Total Controls","Implemented","In Progress","Not Started","Not Applicable","Partially Impl.","% Complete"]): |
| hdr(ws_cover, f"{col_letter}22", label, C["blue"], "FFFFFFFF", True, 10, "center") |
|
|
| domains = [ |
| ("Clause 4-10 (ISMS)", "'Clauses 4-10'"), |
| ("A.5 Org. Controls", "'Annex A - Org Controls'"), |
| ("A.6 People Controls", "'Annex A - People Controls'"), |
| ("A.7 Physical Controls","'Annex A - Physical Controls'"), |
| ("A.8 Tech Controls", "'Annex A - Tech Controls'"), |
| ] |
|
|
| for i, (domain, sheet_ref) in enumerate(domains, start=23): |
| ws_cover.row_dimensions[i].height = 20 |
| bg = C["ice"] if i % 2 == 0 else C["white"] |
| style(ws_cover, f"A{i}", domain, bg=bg, fg=C["navy"], bold=True, size=10, halign="left") |
| ws_cover[f"B{i}"] = f"=COUNTA({sheet_ref}!A3:A500)-COUNTBLANK({sheet_ref}!A3:A500)" |
| ws_cover[f"C{i}"] = f'=COUNTIF({sheet_ref}!G3:G500,"Implemented")' |
| ws_cover[f"D{i}"] = f'=COUNTIF({sheet_ref}!G3:G500,"In Progress")' |
| ws_cover[f"E{i}"] = f'=COUNTIF({sheet_ref}!G3:G500,"Not Started")' |
| ws_cover[f"F{i}"] = f'=COUNTIF({sheet_ref}!G3:G500,"Not Applicable")' |
| ws_cover[f"G{i}"] = f'=COUNTIF({sheet_ref}!G3:G500,"Partially Implemented")' |
| ws_cover[f"H{i}"] = f"=IFERROR(C{i}/B{i},0)" |
| ws_cover[f"H{i}"].number_format = "0.0%" |
| for col in ["B","C","D","E","F","G","H"]: |
| c = ws_cover[f"{col}{i}"] |
| c.font = Font(name="Calibri", size=10, bold=(col=="H")) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
| c.border = thin_border() |
|
|
| |
| ws_cover.row_dimensions[28].height = 22 |
| ws_cover.merge_cells("A28:A28") |
| style(ws_cover, "A28", "TOTAL", bg=C["navy"], fg="FFFFFFFF", bold=True, size=10, halign="center") |
| for col in ["B","C","D","E","F","G"]: |
| ws_cover[f"{col}28"] = f"=SUM({col}23:{col}27)" |
| ws_cover[f"{col}28"].font = Font(name="Calibri", bold=True, size=10, color="FFFFFFFF") |
| ws_cover[f"{col}28"].fill = PatternFill("solid", start_color=C["navy"]) |
| ws_cover[f"{col}28"].alignment = Alignment(horizontal="center", vertical="center") |
| ws_cover[f"{col}28"].border = thin_border("FFFFFFFF") |
| ws_cover["H28"] = "=IFERROR(C28/B28,0)" |
| ws_cover["H28"].number_format = "0.0%" |
| ws_cover["H28"].font = Font(name="Calibri", bold=True, size=12, color="FFFFFFFF") |
| ws_cover["H28"].fill = PatternFill("solid", start_color=C["navy"]) |
| ws_cover["H28"].alignment = Alignment(horizontal="center", vertical="center") |
| ws_cover["H28"].border = thin_border("FFFFFFFF") |
|
|
| |
| ws_cover.row_dimensions[30].height = 18 |
| ws_cover.merge_cells("A30:H30") |
| hdr(ws_cover, "A30", "STATUS COLOUR LEGEND", C["gray_dk"], "FFFFFFFF", True, 10, "left") |
|
|
| legend = [ |
| ("A31:B31", "Implemented", C["green_lt"], C["green_dk"]), |
| ("C31:D31", "In Progress", C["amber_lt"], C["amber_dk"]), |
| ("E31:F31", "Not Started", C["red_lt"], C["red_dk"]), |
| ("G31:G31", "N/A", C["gray_lt"], C["gray_dk"]), |
| ("H31:H31", "Partial", C["orange_lt"], C["orange_dk"]), |
| ] |
| for merge_range, label, bg, fg in legend: |
| ws_cover.merge_cells(merge_range) |
| start_cell = merge_range.split(":")[0] |
| style(ws_cover, start_cell, label, bg=bg, fg=fg, bold=True, size=10, halign="center") |
|
|
|
|
| |
| |
| |
| CTRL_COLS = [ |
| ("A", "Control ID", 12), |
| ("B", "Clause / Section", 22), |
| ("C", "Control Title", 32), |
| ("D", "Control Requirement", 50), |
| ("E", "Control Type", 18), |
| ("F", "Control Owner", 20), |
| ("G", "Status", 18), |
| ("H", "Maturity Level", 18), |
| ("I", "Priority", 14), |
| ("J", "Risk Level", 14), |
| ("K", "Implementation Notes",42), |
| ("L", "Evidence Required", 38), |
| ("M", "Evidence Location", 35), |
| ("N", "Gaps / Findings", 38), |
| ("O", "Remediation Action", 38), |
| ("P", "Responsible Person", 22), |
| ("Q", "Target Date", 14), |
| ("R", "Completion Date", 16), |
| ("S", "Reviewed By", 20), |
| ("T", "Review Date", 14), |
| ("U", "Auditor Comments", 38), |
| ("V", "Doc Reference", 25), |
| ] |
|
|
| def build_controls_sheet(ws, title, tab_color=None): |
| ws.sheet_view.showGridLines = False |
| if tab_color: |
| ws.sheet_properties.tabColor = tab_color |
| ws.row_dimensions[1].height = 38 |
| ws.row_dimensions[2].height = 14 |
|
|
| ws.merge_cells(f"A1:{get_column_letter(len(CTRL_COLS))}1") |
| c = ws["A1"] |
| c.value = f"ISO/IEC 27001:2022 — {title}" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| for col_letter, col_name, col_width in CTRL_COLS: |
| hdr(ws, f"{col_letter}3", col_name, C["blue"], "FFFFFFFF", True, 9, "center", True) |
| ws.column_dimensions[col_letter].width = col_width |
| ws.row_dimensions[3].height = 32 |
|
|
| dv_list(ws, "G4:G1000", STATUS_DV) |
| dv_list(ws, "H4:H1000", MATURITY_DV) |
| dv_list(ws, "I4:I1000", PRIORITY_DV) |
| dv_list(ws, "J4:J1000", RISK_DV) |
|
|
| |
| cf_equal(ws, "G4:G1000", '"Implemented"', C["green_lt"]) |
| cf_equal(ws, "G4:G1000", '"In Progress"', C["amber_lt"]) |
| cf_equal(ws, "G4:G1000", '"Not Started"', C["red_lt"]) |
| cf_equal(ws, "G4:G1000", '"Not Applicable"', C["gray_lt"]) |
| cf_equal(ws, "G4:G1000", '"Partially Implemented"', C["orange_lt"]) |
| cf_equal(ws, "J4:J1000", '"Critical"', C["red_lt"]) |
| cf_equal(ws, "J4:J1000", '"High"', C["orange_lt"]) |
| cf_equal(ws, "J4:J1000", '"Medium"', C["amber_lt"]) |
| cf_equal(ws, "J4:J1000", '"Low"', C["green_lt"]) |
|
|
| ws.freeze_panes = "A4" |
| return ws |
|
|
|
|
| def add_control_row(ws, row, data, is_section=False, section_bg=None): |
| ws.row_dimensions[row].height = 38 if not is_section else 22 |
| if is_section: |
| |
| last_col = get_column_letter(len(CTRL_COLS)) |
| ws.merge_cells(f"A{row}:{last_col}{row}") |
| c = ws[f"A{row}"] |
| c.value = data |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=10) |
| c.fill = PatternFill("solid", start_color=section_bg or C["mid_blue"]) |
| c.alignment = Alignment(horizontal="left", vertical="center") |
| c.border = thin_border("FFFFFFFF") |
| return |
|
|
| bg = C["ice"] if row % 2 == 0 else C["white"] |
| for j, (col_letter, _, _) in enumerate(CTRL_COLS): |
| val = data[j] if j < len(data) else "" |
| c = ws[f"{col_letter}{row}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| halign = "center" if col_letter in ["A","E","G","H","I","J","Q","R","T"] else "left" |
| c.alignment = Alignment(horizontal=halign, vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
|
|
| print("Part 1 loaded. Building sheets...") |
|
|
|
|
| |
| |
| |
| ws_cl = wb.create_sheet("Clauses 4-10") |
| build_controls_sheet(ws_cl, "ISMS Requirements — Clauses 4 to 10", C["navy"]) |
|
|
| clauses_data = [ |
| |
| ("§4", "CLAUSE 4: CONTEXT OF THE ORGANIZATION", True, C["navy"]), |
| ("4.1.1","4.1 Understanding the Organization","External & Internal Issues Identified","Determine external and internal issues relevant to the organization's purpose that affect its ability to achieve the intended outcomes of the ISMS.","Preventive","CISO / Management","Not Started","0-Non-existent","P1-Immediate","High","","Issue register, PESTLE/SWOT analysis","","","","","","","","","",""), |
| ("4.2.1","4.2 Interested Parties","Interested Parties Identified","Determine the interested parties relevant to the ISMS and their requirements (legal, regulatory, contractual).","Preventive","CISO","Not Started","0-Non-existent","P1-Immediate","High","","Stakeholder register, requirements matrix","","","","","","","","","",""), |
| ("4.3.1","4.3 Scope","ISMS Scope Defined","Determine the boundaries and applicability of the ISMS. The scope shall be available as documented information.","Preventive","CISO","Not Started","0-Non-existent","P1-Immediate","Critical","","Scope statement document, network boundary diagrams","","","","","","","","","",""), |
| ("4.4.1","4.4 ISMS","ISMS Established, Implemented, Maintained","Establish, implement, maintain and continually improve an information security management system.","Preventive","CISO / Top Management","Not Started","0-Non-existent","P1-Immediate","Critical","","ISMS framework documentation, policy suite","","","","","","","","","",""), |
| |
| ("§5", "CLAUSE 5: LEADERSHIP", True, C["navy"]), |
| ("5.1.1","5.1 Leadership & Commitment","Top Management Commitment Demonstrated","Top management shall demonstrate leadership and commitment with respect to the ISMS.","Organizational","Top Management / CEO","Not Started","0-Non-existent","P1-Immediate","Critical","","Board minutes, signed policy, resource allocation records","","","","","","","","","",""), |
| ("5.1.2","5.1 Leadership & Commitment","ISMS Objectives Aligned to Strategic Direction","Ensure ISMS objectives are compatible with the strategic direction of the organization.","Organizational","Top Management","Not Started","0-Non-existent","P1-Immediate","High","","Strategic plan, ISMS objectives document","","","","","","","","","",""), |
| ("5.2.1","5.2 Policy","Information Security Policy Established","Top management shall establish an information security policy that is appropriate to the purpose of the organization.","Organizational","CISO / Top Management","Not Started","0-Non-existent","P1-Immediate","Critical","","Information security policy (signed, dated, distributed)","","","","","","","","","",""), |
| ("5.2.2","5.2 Policy","Policy Communication","The information security policy shall be communicated within the organization and available to interested parties.","Organizational","CISO / HR","Not Started","0-Non-existent","P1-Immediate","High","","Policy distribution records, intranet posting, acknowledgement log","","","","","","","","","",""), |
| ("5.3.1","5.3 Roles & Responsibilities","Organizational Roles Assigned","Top management shall ensure that responsibilities and authorities for roles relevant to information security are assigned and communicated.","Organizational","Top Management / HR","Not Started","0-Non-existent","P1-Immediate","High","","RACI matrix, job descriptions, org chart","","","","","","","","","",""), |
| |
| ("§6", "CLAUSE 6: PLANNING", True, C["navy"]), |
| ("6.1.1","6.1.1 Risk & Opportunity","Actions to Address Risks and Opportunities","Determine risks and opportunities that need to be addressed to ensure the ISMS achieves its intended outcomes.","Preventive","Risk Officer / CISO","Not Started","0-Non-existent","P1-Immediate","Critical","","Risk treatment plan, opportunity register","","","","","","","","","",""), |
| ("6.1.2","6.1.2 Risk Assessment","Information Security Risk Assessment Process","Establish and apply a risk assessment process including criteria for risk acceptance and criteria for performing risk assessments.","Preventive","Risk Officer","Not Started","0-Non-existent","P1-Immediate","Critical","","Risk assessment methodology, risk register","","","","","","","","","",""), |
| ("6.1.3","6.1.3 Risk Treatment","Information Security Risk Treatment Process","Apply a risk treatment process. Select and implement risk treatment options. Produce a Statement of Applicability (SoA).","Preventive","Risk Officer / CISO","Not Started","0-Non-existent","P1-Immediate","Critical","","Risk treatment plan, Statement of Applicability (SoA)","","","","","","","","","",""), |
| ("6.2.1","6.2 Objectives","Information Security Objectives Established","Establish information security objectives at relevant functions and levels. Objectives shall be measurable, monitored, communicated, updated.","Organizational","CISO / Management","Not Started","0-Non-existent","P1-Immediate","High","","ISMS objectives register, KPI dashboard","","","","","","","","","",""), |
| ("6.3.1","6.3 Planning Changes","Planning of Changes","When the organization determines the need for changes to the ISMS, the changes shall be carried out in a planned manner.","Preventive","CISO","Not Started","0-Non-existent","P2-High","Medium","","Change management procedure, change log","","","","","","","","","",""), |
| |
| ("§7", "CLAUSE 7: SUPPORT", True, C["navy"]), |
| ("7.1.1","7.1 Resources","Resources Determined and Provided","Determine and provide resources needed for the establishment, implementation, maintenance and continual improvement of the ISMS.","Organizational","Top Management / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Budget records, resource allocation plan, tool inventory","","","","","","","","","",""), |
| ("7.2.1","7.2 Competence","Competence of Personnel Determined","Determine necessary competence, ensure competence based on education/training/experience, take actions where needed.","Organizational","HR / CISO","Not Started","0-Non-existent","P2-High","High","","Competence matrix, training records, certifications","","","","","","","","","",""), |
| ("7.3.1","7.3 Awareness","Information Security Awareness","All persons doing work under the organization's control shall be aware of the IS policy, their contribution, and implications of non-conformity.","Organizational","HR / CISO","Not Started","0-Non-existent","P2-High","High","","Awareness training completion records, quiz results","","","","","","","","","",""), |
| ("7.4.1","7.4 Communication","Internal & External Communication","Determine the need for internal and external communications relevant to the ISMS.","Organizational","CISO","Not Started","0-Non-existent","P2-High","Medium","","Communication plan, incident notification procedures","","","","","","","","","",""), |
| ("7.5.1","7.5 Documented Info","Documented Information Required by Standard","Create and maintain documented information required by the ISO 27001:2022 standard.","Organizational","CISO / Document Controller","Not Started","0-Non-existent","P1-Immediate","High","","Document register, controlled document list","","","","","","","","","",""), |
| ("7.5.2","7.5 Documented Info","Document Control — Creation & Updating","When creating and updating documented information, ensure appropriate identification, format, media, and suitability/adequacy.","Organizational","Document Controller","Not Started","0-Non-existent","P2-High","Medium","","Document control procedure, version history","","","","","","","","","",""), |
| ("7.5.3","7.5 Documented Info","Document Control — Distribution & Access","Documented information shall be available and suitable for use, protected, distributed, and stored appropriately.","Organizational","Document Controller / IT","Not Started","0-Non-existent","P2-High","Medium","","Document management system, access controls, retention schedule","","","","","","","","","",""), |
| |
| ("§8", "CLAUSE 8: OPERATION", True, C["navy"]), |
| ("8.1.1","8.1 Operational Planning","Operational Planning and Control","Plan, implement, control, and maintain processes needed to meet requirements. Implement plans determined in Clause 6.","Operational","CISO / Operations","Not Started","0-Non-existent","P1-Immediate","High","","Operational procedures, process documentation","","","","","","","","","",""), |
| ("8.1.2","8.1 Operational Planning","Control of Planned Changes","Control planned changes and review consequences of unintended changes, taking action to mitigate adverse effects.","Preventive","Change Manager / CISO","Not Started","0-Non-existent","P2-High","High","","Change management procedure, change log, CAB minutes","","","","","","","","","",""), |
| ("8.1.3","8.1 Operational Planning","Control of Outsourced Processes","Ensure outsourced processes are controlled. Supplier security requirements shall be documented and monitored.","Preventive","Procurement / CISO","Not Started","0-Non-existent","P2-High","High","","Supplier contracts, SLAs, vendor assessment records","","","","","","","","","",""), |
| ("8.2.1","8.2 Risk Assessment","Information Security Risk Assessment Performed","Perform risk assessments at planned intervals or when significant changes are proposed.","Preventive","Risk Officer","Not Started","0-Non-existent","P1-Immediate","Critical","","Risk assessment reports (dated), risk register","","","","","","","","","",""), |
| ("8.3.1","8.3 Risk Treatment","Information Security Risk Treatment Implemented","Implement the risk treatment plan. Retain documented information as evidence.","Preventive","Risk Officer / Control Owners","Not Started","0-Non-existent","P1-Immediate","Critical","","Risk treatment plan, implementation evidence, SoA","","","","","","","","","",""), |
| |
| ("§9", "CLAUSE 9: PERFORMANCE EVALUATION", True, C["navy"]), |
| ("9.1.1","9.1 Monitoring & Measurement","Monitoring, Measurement, Analysis & Evaluation","Determine what needs to be monitored and measured, methods, when to be performed, and when results analysed.","Detective","CISO / Compliance","Not Started","0-Non-existent","P2-High","High","","KPI dashboard, monitoring reports, metrics register","","","","","","","","","",""), |
| ("9.2.1","9.2 Internal Audit","Internal Audit Programme Established","Conduct internal audits at planned intervals to determine conformance and effective implementation.","Detective","Internal Audit Lead","Not Started","0-Non-existent","P1-Immediate","High","","Audit programme, internal audit reports, corrective action log","","","","","","","","","",""), |
| ("9.2.2","9.2 Internal Audit","Internal Audit Criteria & Scope Defined","Define audit criteria and scope for each audit. Select auditors ensuring objectivity and impartiality.","Detective","Internal Audit Lead","Not Started","0-Non-existent","P1-Immediate","Medium","","Audit plan, auditor selection criteria, independence declaration","","","","","","","","","",""), |
| ("9.3.1","9.3 Management Review","Management Review Conducted","Top management shall review the ISMS at planned intervals to ensure its continuing suitability, adequacy, and effectiveness.","Organizational","Top Management / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Management review meeting minutes, action log","","","","","","","","","",""), |
| ("9.3.2","9.3 Management Review","Management Review Inputs Considered","Review inputs shall include status of previous actions, changes in issues, feedback, risk assessment results, objectives performance.","Organizational","CISO","Not Started","0-Non-existent","P1-Immediate","Medium","","Management review agenda, status reports","","","","","","","","","",""), |
| ("9.3.3","9.3 Management Review","Management Review Outputs Documented","Outputs include decisions on continual improvement opportunities and changes to the ISMS.","Organizational","CISO","Not Started","0-Non-existent","P1-Immediate","Medium","","Management review minutes with decisions and action items","","","","","","","","","",""), |
| |
| ("§10", "CLAUSE 10: IMPROVEMENT", True, C["navy"]), |
| ("10.1.1","10.1 Continual Improvement","Continual Improvement of ISMS","Continually improve the suitability, adequacy, and effectiveness of the ISMS.","Organizational","CISO / Top Management","Not Started","0-Non-existent","P2-High","Medium","","Improvement register, trend analysis, lessons learned","","","","","","","","","",""), |
| ("10.2.1","10.2 Nonconformity & CAR","Nonconformity and Corrective Actions","React to nonconformities. Take action to control and correct it. Evaluate the need for action to eliminate causes.","Corrective","CISO / Compliance","Not Started","0-Non-existent","P1-Immediate","High","","Nonconformity register, corrective action reports (CARs)","","","","","","","","","",""), |
| ("10.2.2","10.2 Nonconformity & CAR","Root Cause Analysis Performed","Review nonconformities, determine root causes, implement corrective actions, review effectiveness.","Corrective","Compliance / Process Owners","Not Started","0-Non-existent","P1-Immediate","High","","Root cause analysis records, CAR effectiveness reviews","","","","","","","","","",""), |
| ] |
|
|
| row = 4 |
| for item in clauses_data: |
| if item[2] is True: |
| add_control_row(ws_cl, row, item[1], is_section=True, section_bg=item[3]) |
| else: |
| ctrl_id, clause, title, req, ctype, owner, status, mat, pri, risk, notes, evid, evid_loc, gaps, rem, resp, tgt, comp, rev, rev_dt, aud_cm, doc_ref = item |
| add_control_row(ws_cl, row, [ctrl_id, clause, title, req, ctype, owner, status, mat, pri, risk, notes, evid, evid_loc, gaps, rem, resp, tgt, comp, rev, rev_dt, aud_cm, doc_ref]) |
| row += 1 |
|
|
|
|
| print("Clauses 4-10 done. Building Annex A sheets...") |
|
|
|
|
| |
| |
| |
| ws_a5 = wb.create_sheet("Annex A - Org Controls") |
| build_controls_sheet(ws_a5, "Annex A.5 — Organisational Controls (Controls 5.1–5.37)", C["blue"]) |
|
|
| a5_data = [ |
| ("5.1","A.5.1","Policies for Information Security","Information security policy and topic-specific policies shall be defined, approved by management, published, communicated to relevant personnel and interested parties, acknowledged, and reviewed at planned intervals.","Organizational","CISO","Not Started","0-Non-existent","P1-Immediate","High","","IS policy suite, approval records, distribution evidence, review cycle","","","","","","","","","IS-POL-001"), |
| ("5.2","A.5.2","Information Security Roles and Responsibilities","Information security roles and responsibilities shall be defined and allocated according to the organization's information security needs.","Organizational","CISO / HR","Not Started","0-Non-existent","P1-Immediate","High","","RACI matrix, org chart, job descriptions with IS responsibilities","","","","","","","","","IS-POL-002"), |
| ("5.3","A.5.3","Segregation of Duties","Conflicting duties and conflicting areas of responsibility shall be segregated to reduce opportunities for unauthorized or unintentional modification or misuse of assets.","Preventive","IT Admin / HR","Not Started","0-Non-existent","P1-Immediate","High","","SoD matrix, role conflict analysis, access control rules","","","","","","","","","IS-CTL-001"), |
| ("5.4","A.5.4","Management Responsibilities","Management shall require all personnel to apply information security in accordance with established policy and procedures.","Organizational","Top Management / HR","Not Started","0-Non-existent","P1-Immediate","High","","HR policy, employment contracts, IS acknowledgement forms","","","","","","","","","IS-POL-003"), |
| ("5.5","A.5.5","Contact with Authorities","The organization shall establish and maintain contacts with relevant authorities (e.g., law enforcement, regulators, emergency services).","Organizational","CISO / Legal","Not Started","0-Non-existent","P2-High","Medium","","Contacts directory, escalation procedure for regulatory events","","","","","","","","","IS-PRO-001"), |
| ("5.6","A.5.6","Contact with Special Interest Groups","The organization shall maintain contacts with special interest groups, specialist security forums, and professional associations.","Organizational","CISO","Not Started","0-Non-existent","P3-Medium","Low","","Membership list, ISAC participation, security community engagement records","","","","","","","","","IS-PRO-002"), |
| ("5.7","A.5.7","Threat Intelligence","Information relating to information security threats shall be collected and analysed to produce threat intelligence.","Detective","Security Team / CISO","Not Started","0-Non-existent","P2-High","High","","Threat intel feeds, CTI reports, threat model, MITRE ATT&CK mapping","","","","","","","","","IS-PRO-003"), |
| ("5.8","A.5.8","Information Security in Project Management","Information security shall be integrated into project management throughout the project lifecycle.","Preventive","PMO / CISO","Not Started","0-Non-existent","P2-High","Medium","","Project management methodology, security gate criteria, DPIA/TPIA records","","","","","","","","","IS-PRO-004"), |
| ("5.9","A.5.9","Inventory of Information and Other Associated Assets","An inventory of information and other associated assets, including owners, shall be developed and maintained.","Preventive","IT / Asset Owner","Not Started","0-Non-existent","P1-Immediate","High","","Asset inventory/register (hardware, software, data, people), asset ownership","","","","","","","","","IS-PRO-005"), |
| ("5.10","A.5.10","Acceptable Use of Information and Other Associated Assets","Rules for acceptable use and procedures for handling information and associated assets shall be identified, documented, and implemented.","Preventive","CISO / HR","Not Started","0-Non-existent","P1-Immediate","High","","Acceptable use policy, user acknowledgement records","","","","","","","","","IS-POL-004"), |
| ("5.11","A.5.11","Return of Assets","Personnel and other interested parties shall return all organizational assets upon change or termination of employment or contract.","Preventive","HR / IT","Not Started","0-Non-existent","P2-High","High","","Offboarding checklist, asset return records","","","","","","","","","IS-PRO-006"), |
| ("5.12","A.5.12","Classification of Information","Information shall be classified according to the organization's information security needs based on confidentiality, integrity, and availability.","Preventive","Data Owner / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Data classification policy, classification scheme (e.g., Public/Internal/Confidential/Restricted)","","","","","","","","","IS-POL-005"), |
| ("5.13","A.5.13","Labelling of Information","An appropriate set of procedures for information labelling shall be developed and implemented in accordance with the classification scheme.","Preventive","Data Owner / IT","Not Started","0-Non-existent","P2-High","Medium","","Labelling procedure, document headers/footers, DLP configuration","","","","","","","","","IS-PRO-007"), |
| ("5.14","A.5.14","Information Transfer","Information transfer rules, procedures, or agreements shall exist for all types of transfer facilities within the organization and between the organization and other parties.","Preventive","CISO / Legal","Not Started","0-Non-existent","P1-Immediate","High","","Data transfer policy, NDAs, secure transfer procedures, email encryption config","","","","","","","","","IS-POL-006"), |
| ("5.15","A.5.15","Access Control","Rules to control physical and logical access to information and other associated assets shall be established and implemented based on business and IS requirements.","Preventive","IT Admin / CISO","Not Started","0-Non-existent","P1-Immediate","Critical","","Access control policy, IAM system config, role matrix, access request records","","","","","","","","","IS-POL-007"), |
| ("5.16","A.5.16","Identity Management","The full lifecycle of identities shall be managed. Unique IDs shall be assigned; shared accounts shall be avoided.","Preventive","IT Admin","Not Started","0-Non-existent","P1-Immediate","High","","Identity management procedure, Active Directory / IdP config, account lifecycle records","","","","","","","","","IS-PRO-008"), |
| ("5.17","A.5.17","Authentication Information","Allocation and management of authentication information shall be controlled by a management process, including advising personnel on appropriate handling.","Preventive","IT Admin / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Password policy, MFA configuration, credential management procedure","","","","","","","","","IS-PRO-009"), |
| ("5.18","A.5.18","Access Rights","Access rights to information and other associated assets shall be provisioned, reviewed, modified, and removed.","Preventive","IT Admin / HR","Not Started","0-Non-existent","P1-Immediate","High","","Access provisioning procedure, quarterly access reviews, user access matrix","","","","","","","","","IS-PRO-010"), |
| ("5.19","A.5.19","Information Security in Supplier Relationships","Processes and procedures shall be defined and implemented to manage information security risks associated with the use of supplier's products or services.","Preventive","Procurement / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Supplier security policy, vendor risk assessment, security questionnaire template","","","","","","","","","IS-POL-008"), |
| ("5.20","A.5.20","Addressing Information Security Within Supplier Agreements","Relevant IS requirements shall be established and agreed upon with each supplier based on the type of supplier relationship.","Preventive","Procurement / Legal","Not Started","0-Non-existent","P1-Immediate","High","","Supplier contracts with IS clauses, SLAs, DPA/DPAs, audit rights","","","","","","","","","IS-PRO-011"), |
| ("5.21","A.5.21","Managing Information Security in the ICT Supply Chain","Processes and procedures shall be defined to manage IS risks associated with the ICT product and service supply chain.","Preventive","CISO / Procurement","Not Started","0-Non-existent","P2-High","High","","ICT supply chain risk assessment, SBOM, component integrity verification","","","","","","","","","IS-PRO-012"), |
| ("5.22","A.5.22","Monitoring, Review and Change Management of Supplier Services","The organization shall regularly monitor, review, and manage changes to supplier information security practices and service delivery.","Detective","Procurement / CISO","Not Started","0-Non-existent","P2-High","High","","Supplier review schedule, audit reports, change notification process","","","","","","","","","IS-PRO-013"), |
| ("5.23","A.5.23","Information Security for Use of Cloud Services","Processes for acquisition, use, management, and exit from cloud services shall be established in accordance with IS requirements.","Preventive","IT / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Cloud security policy, CSP assessment (CAIQ/CSA STAR), shared responsibility matrix","","","","","","","","","IS-POL-009"), |
| ("5.24","A.5.24","Information Security Incident Management Planning and Preparation","The organization shall plan and prepare for managing IS incidents by defining processes, roles, and responsibilities.","Preventive","CISO / Security Team","Not Started","0-Non-existent","P1-Immediate","Critical","","Incident response plan (IRP), incident classification scheme, RACI for incidents","","","","","","","","","IS-PRO-014"), |
| ("5.25","A.5.25","Assessment and Decision on Information Security Events","IS events shall be assessed and decided if they are to be classified as IS incidents.","Detective","Security Operations","Not Started","0-Non-existent","P1-Immediate","High","","Event triage procedure, SIEM alert rules, incident classification criteria","","","","","","","","","IS-PRO-015"), |
| ("5.26","A.5.26","Response to Information Security Incidents","IS incidents shall be responded to in accordance with documented procedures.","Corrective","Security Team / CISO","Not Started","0-Non-existent","P1-Immediate","Critical","","IRP, incident response runbooks, post-incident review records","","","","","","","","","IS-PRO-016"), |
| ("5.27","A.5.27","Learning from Information Security Incidents","Knowledge gained from IS incidents shall be used to strengthen and improve IS controls.","Corrective","CISO / Security Team","Not Started","0-Non-existent","P2-High","High","","Lessons learned reports, control improvement records, trend analysis","","","","","","","","","IS-PRO-017"), |
| ("5.28","A.5.28","Collection of Evidence","The organization shall establish and implement procedures for the identification, collection, acquisition, and preservation of evidence related to IS events.","Corrective","Legal / Security Team","Not Started","0-Non-existent","P2-High","High","","Digital forensics procedure, chain of custody documentation, evidence preservation policy","","","","","","","","","IS-PRO-018"), |
| ("5.29","A.5.29","Information Security During Disruption","The organization shall plan how to maintain IS at an appropriate level during disruption.","Preventive","CISO / BCP Manager","Not Started","0-Non-existent","P1-Immediate","Critical","","BCP/DR plan with IS considerations, IS continuity objectives","","","","","","","","","IS-PRO-019"), |
| ("5.30","A.5.30","ICT Readiness for Business Continuity","ICT readiness shall be planned, implemented, maintained, and tested based on ICT continuity objectives and requirements.","Preventive","IT / BCP Manager","Not Started","0-Non-existent","P1-Immediate","Critical","","ICT recovery plans, RTO/RPO definitions, DR test results","","","","","","","","","IS-PRO-020"), |
| ("5.31","A.5.31","Legal, Statutory, Regulatory and Contractual Requirements","Legal, statutory, regulatory, and contractual requirements relevant to IS shall be identified, documented, and kept up to date.","Organizational","Legal / Compliance","Not Started","0-Non-existent","P1-Immediate","High","","Compliance register, legal obligations register, regulatory mapping","","","","","","","","","IS-PRO-021"), |
| ("5.32","A.5.32","Intellectual Property Rights","Procedures shall be implemented to protect intellectual property rights.","Preventive","Legal / Procurement","Not Started","0-Non-existent","P2-High","Medium","","IP policy, software licensing register, copyright procedure","","","","","","","","","IS-POL-010"), |
| ("5.33","A.5.33","Protection of Records","Records shall be protected from loss, destruction, falsification, unauthorized access, and unauthorized release.","Preventive","Records Manager / IT","Not Started","0-Non-existent","P2-High","High","","Records management policy, retention schedule, access controls for records","","","","","","","","","IS-POL-011"), |
| ("5.34","A.5.34","Privacy and Protection of Personal Information (PII)","The privacy and protection of PII shall be ensured as required by applicable legislation and regulations.","Preventive","DPO / Privacy Officer","Not Started","0-Non-existent","P1-Immediate","High","","Privacy policy, GDPR/DPDP compliance programme, DPIA records, RoPA","","","","","","","","","IS-POL-012"), |
| ("5.35","A.5.35","Independent Review of Information Security","The organization's approach to managing IS and its implementation shall be reviewed independently at planned intervals.","Detective","Internal/External Audit","Not Started","0-Non-existent","P1-Immediate","High","","Internal audit reports, penetration test reports, third-party assessment reports","","","","","","","","","IS-PRO-022"), |
| ("5.36","A.5.36","Compliance with Policies, Rules and Standards","Compliance with IS policy, topic-specific policies, and standards shall be regularly reviewed.","Detective","CISO / Compliance","Not Started","0-Non-existent","P2-High","High","","Compliance monitoring procedure, compliance dashboards, non-conformance log","","","","","","","","","IS-PRO-023"), |
| ("5.37","A.5.37","Documented Operating Procedures","Operating procedures for information processing facilities shall be documented and made available to all users who need them.","Organizational","IT / Operations","Not Started","0-Non-existent","P2-High","Medium","","Standard operating procedures (SOPs), IT runbooks, process documentation","","","","","","","","","IS-PRO-024"), |
| ] |
|
|
| row = 4 |
| for item in a5_data: |
| add_control_row(ws_a5, row, list(item)) |
| row += 1 |
|
|
| print("A.5 done. Building A.6...") |
|
|
| |
| |
| |
| ws_a6 = wb.create_sheet("Annex A - People Controls") |
| build_controls_sheet(ws_a6, "Annex A.6 — People Controls (Controls 6.1–6.8)", C["green_dk"]) |
|
|
| a6_data = [ |
| ("6.1","A.6.1","Screening","Background verification checks on all candidates for employment shall be carried out prior to joining and on an ongoing basis. Checks shall be proportional to the risk, classification, and applicable laws.","Preventive","HR","Not Started","0-Non-existent","P1-Immediate","High","","Background check policy, screening records (pre-employment), periodic re-check procedure","","","","","","","","","HR-POL-001"), |
| ("6.2","A.6.2","Terms and Conditions of Employment","Employment contracts shall state the personnel's and organization's responsibilities for IS. All employees shall sign and acknowledge IS policies.","Preventive","HR / Legal","Not Started","0-Non-existent","P1-Immediate","High","","Employment contracts with IS clauses, signed IS acknowledgement forms, confidentiality agreements","","","","","","","","","HR-POL-002"), |
| ("6.3","A.6.3","Information Security Awareness, Education and Training","All personnel and relevant interested parties shall receive appropriate IS awareness education and training, and regular updates of the organization's policies and procedures.","Preventive","HR / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Training plan, LMS completion records, phishing simulation results, awareness campaign evidence","","","","","","","","","HR-PRO-001"), |
| ("6.4","A.6.4","Disciplinary Process","A formal and communicated disciplinary process shall be in place to take action against personnel who have committed an IS breach.","Corrective","HR / Legal","Not Started","0-Non-existent","P2-High","Medium","","Disciplinary policy, IS breach register, HR records (anonymised), escalation procedure","","","","","","","","","HR-POL-003"), |
| ("6.5","A.6.5","Responsibilities After Termination or Change of Employment","IS responsibilities and duties that remain valid after termination or change of employment shall be defined, enforced, and communicated to the relevant personnel.","Preventive","HR / Legal / IT","Not Started","0-Non-existent","P1-Immediate","High","","Offboarding procedure, exit interview records, post-termination NDA clauses, access revocation log","","","","","","","","","HR-PRO-002"), |
| ("6.6","A.6.6","Confidentiality or Non-Disclosure Agreements","Confidentiality or non-disclosure agreements reflecting the organization's needs for the protection of information shall be identified, documented, regularly reviewed, and signed.","Preventive","Legal / HR","Not Started","0-Non-existent","P1-Immediate","High","","NDA template, signed NDA register, third-party NDA log, periodic review records","","","","","","","","","HR-POL-004"), |
| ("6.7","A.6.7","Remote Working","Security measures shall be implemented when personnel are working remotely to protect information accessed, processed, or stored outside the organization's premises.","Preventive","CISO / IT","Not Started","0-Non-existent","P1-Immediate","High","","Remote working policy, VPN configuration, endpoint encryption, BYOD policy, MDM configuration","","","","","","","","","IS-POL-013"), |
| ("6.8","A.6.8","Information Security Event Reporting","The organization shall provide a mechanism for personnel to report observed or suspected IS events through appropriate channels in a timely manner.","Detective","CISO / Security Team","Not Started","0-Non-existent","P1-Immediate","High","","IS event reporting procedure, reporting channels (helpdesk/hotline), awareness training on reporting, event log","","","","","","","","","IS-PRO-025"), |
| ] |
|
|
| row = 4 |
| for item in a6_data: |
| add_control_row(ws_a6, row, list(item)) |
| row += 1 |
|
|
| print("A.6 done. Building A.7...") |
|
|
| |
| |
| |
| ws_a7 = wb.create_sheet("Annex A - Physical Controls") |
| build_controls_sheet(ws_a7, "Annex A.7 — Physical Controls (Controls 7.1–7.14)", C["orange_dk"]) |
|
|
| a7_data = [ |
| ("7.1","A.7.1","Physical Security Perimeters","Security perimeters shall be defined and used to protect areas that contain information and other associated assets.","Preventive","Facilities / IT","Not Started","0-Non-existent","P1-Immediate","High","","Site security plan, perimeter definition (fences, walls, reception), CCTV coverage map","","","","","","","","","PHY-PRO-001"), |
| ("7.2","A.7.2","Physical Entry","Secure areas shall be protected by appropriate entry controls and access points.","Preventive","Facilities / IT","Not Started","0-Non-existent","P1-Immediate","High","","Access control system config (badge readers, biometrics), visitor log, tailgating controls","","","","","","","","","PHY-PRO-002"), |
| ("7.3","A.7.3","Securing Offices, Rooms and Facilities","Physical security for offices, rooms, and facilities shall be designed and implemented.","Preventive","Facilities","Not Started","0-Non-existent","P1-Immediate","High","","Physical security assessment, room security classifications, lock configuration","","","","","","","","","PHY-PRO-003"), |
| ("7.4","A.7.4","Physical Security Monitoring","Premises shall be continually monitored for unauthorized physical access.","Detective","Facilities / Security","Not Started","0-Non-existent","P1-Immediate","High","","CCTV system config and coverage, alarm system documentation, monitoring logs, guard schedule","","","","","","","","","PHY-PRO-004"), |
| ("7.5","A.7.5","Protecting Against Physical and Environmental Threats","Protection against physical and environmental threats, such as natural disasters, malicious attack, or accidents, shall be designed and implemented.","Preventive","Facilities / IT","Not Started","0-Non-existent","P1-Immediate","High","","Environmental threat assessment, fire suppression config, flood protection, UPS/generator records","","","","","","","","","PHY-PRO-005"), |
| ("7.6","A.7.6","Working in Secure Areas","Security measures for working in secure areas shall be designed and implemented.","Preventive","Facilities / CISO","Not Started","0-Non-existent","P2-High","Medium","","Secure area procedures, clean desk policy, visitor escort procedure, photography prohibition","","","","","","","","","PHY-PRO-006"), |
| ("7.7","A.7.7","Clear Desk and Clear Screen","Clear desk rules for papers and removable storage media and clear screen rules for information processing facilities shall be defined and appropriately enforced.","Preventive","CISO / HR","Not Started","0-Non-existent","P2-High","Medium","","Clear desk/screen policy, compliance audit results, screensaver timeout configuration","","","","","","","","","IS-POL-014"), |
| ("7.8","A.7.8","Equipment Siting and Protection","Equipment shall be sited and protected to reduce risks from environmental threats and hazards and unauthorized access.","Preventive","Facilities / IT","Not Started","0-Non-existent","P2-High","High","","Data centre layout, server rack placement records, environmental monitoring logs","","","","","","","","","PHY-PRO-007"), |
| ("7.9","A.7.9","Security of Assets Off-Premises","Off-site assets shall be protected using measures that account for the different risks of working outside the organization's premises.","Preventive","IT / CISO","Not Started","0-Non-existent","P2-High","High","","Portable device policy, laptop encryption configuration, mobile device inventory, travel security brief","","","","","","","","","IS-POL-015"), |
| ("7.10","A.7.10","Storage Media","Storage media shall be managed throughout their lifecycle — acquisition, use, transportation, and disposal — in accordance with the organization's classification scheme.","Preventive","IT / Records Manager","Not Started","0-Non-existent","P1-Immediate","High","","Media management procedure, media inventory, secure erasure records, media disposal certificates","","","","","","","","","IS-PRO-026"), |
| ("7.11","A.7.11","Supporting Utilities","Information processing facilities shall be protected from power failures and other disruptions caused by failures in supporting utilities.","Preventive","Facilities / IT","Not Started","0-Non-existent","P1-Immediate","High","","UPS configuration, generator test records, power monitoring logs, utility failure response procedure","","","","","","","","","PHY-PRO-008"), |
| ("7.12","A.7.12","Cabling Security","Cables carrying power or data communications or supporting information services shall be protected from interception, interference, or damage.","Preventive","Facilities / IT","Not Started","0-Non-existent","P2-High","Medium","","Cable plant documentation, cabling standards, physical inspection records","","","","","","","","","PHY-PRO-009"), |
| ("7.13","A.7.13","Equipment Maintenance","Equipment shall be maintained correctly to ensure the availability, integrity, and confidentiality of information.","Preventive","IT / Facilities","Not Started","0-Non-existent","P2-High","Medium","","Maintenance schedule, maintenance logs, service contracts, authorized maintainer list","","","","","","","","","IT-PRO-001"), |
| ("7.14","A.7.14","Secure Disposal or Re-Use of Equipment","Items of equipment containing storage media shall be verified to ensure that any sensitive data and licensed software has been removed or securely overwritten prior to disposal or re-use.","Preventive","IT / Procurement","Not Started","0-Non-existent","P1-Immediate","High","","Equipment disposal procedure, data wiping records (tool + standard used), ITAD vendor certificates","","","","","","","","","IT-PRO-002"), |
| ] |
|
|
| row = 4 |
| for item in a7_data: |
| add_control_row(ws_a7, row, list(item)) |
| row += 1 |
|
|
| print("A.7 done. Building A.8 (tech controls)...") |
|
|
| |
| |
| |
| ws_a8 = wb.create_sheet("Annex A - Tech Controls") |
| build_controls_sheet(ws_a8, "Annex A.8 — Technological Controls (Controls 8.1–8.34)", C["purple_dk"]) |
|
|
| a8_data = [ |
| ("8.1","A.8.1","User Endpoint Devices","Information stored on, processed by, or accessible via user endpoint devices shall be protected.","Preventive","IT / CISO","Not Started","0-Non-existent","P1-Immediate","High","","Endpoint protection policy, EDR/AV deployment report, MDM config, disk encryption status","","","","","","","","","IT-POL-001"), |
| ("8.2","A.8.2","Privileged Access Rights","The allocation and use of privileged access rights shall be restricted and managed.","Preventive","IT Admin","Not Started","0-Non-existent","P1-Immediate","Critical","","PAM system config, privileged account register, least privilege review, just-in-time access log","","","","","","","","","IT-PRO-003"), |
| ("8.3","A.8.3","Information Access Restriction","Access to information and application system functions shall be restricted in accordance with the access control policy.","Preventive","IT Admin / App Teams","Not Started","0-Non-existent","P1-Immediate","High","","Access control lists, RBAC configuration, system access matrices","","","","","","","","","IT-PRO-004"), |
| ("8.4","A.8.4","Access to Source Code","Read and write access to source code, development tools, and software libraries shall be appropriately managed.","Preventive","Dev / IT","Not Started","0-Non-existent","P2-High","High","","Source code access policy, repository access controls (GitHub/GitLab RBAC), code review process","","","","","","","","","DEV-POL-001"), |
| ("8.5","A.8.5","Secure Authentication","Secure authentication technologies and procedures shall be implemented based on information access restrictions.","Preventive","IT Admin","Not Started","0-Non-existent","P1-Immediate","Critical","","MFA configuration, SSO setup, authentication policy, failed login monitoring","","","","","","","","","IT-PRO-005"), |
| ("8.6","A.8.6","Capacity Management","The use of resources shall be monitored and adjusted and projections made of future capacity requirements to ensure the required system performance.","Preventive","IT / Operations","Not Started","0-Non-existent","P2-High","Medium","","Capacity monitoring reports, resource utilization dashboards, scaling procedures","","","","","","","","","IT-PRO-006"), |
| ("8.7","A.8.7","Protection Against Malware","Protection against malware shall be implemented and supported by appropriate user awareness.","Preventive","IT / Security","Not Started","0-Non-existent","P1-Immediate","Critical","","AV/EDR deployment report, malware protection policy, quarantine logs, user awareness training","","","","","","","","","IT-POL-002"), |
| ("8.8","A.8.8","Management of Technical Vulnerabilities","Information about technical vulnerabilities of information systems in use shall be obtained, the exposure evaluated, and appropriate measures taken.","Preventive","Security Team / IT","Not Started","0-Non-existent","P1-Immediate","Critical","","Vulnerability management procedure, scan reports, CVSS scoring, patch management log, SLA for patching","","","","","","","","","IT-PRO-007"), |
| ("8.9","A.8.9","Configuration Management","Configurations of hardware, software, services, and networks shall be established, documented, implemented, monitored, and reviewed.","Preventive","IT / DevOps","Not Started","0-Non-existent","P1-Immediate","High","","Configuration management procedure, baseline configs, CIS benchmark compliance reports, CMDB","","","","","","","","","IT-PRO-008"), |
| ("8.10","A.8.10","Information Deletion","Information stored in information systems, devices, or in any other storage media shall be deleted when no longer required.","Preventive","IT / Data Owner","Not Started","0-Non-existent","P2-High","High","","Data deletion procedure, retention schedule, deletion logs, automated purge configuration","","","","","","","","","IT-PRO-009"), |
| ("8.11","A.8.11","Data Masking","Data masking shall be used in accordance with the organization's topic-specific policy on access control and other related policies, and business requirements.","Preventive","Dev / DBA","Not Started","0-Non-existent","P2-High","High","","Data masking policy, masking configuration in non-production environments, tokenization records","","","","","","","","","IT-PRO-010"), |
| ("8.12","A.8.12","Data Leakage Prevention","Data leakage prevention (DLP) measures shall be applied to systems, networks, and devices that process, store, or transmit sensitive information.","Preventive","Security / IT","Not Started","0-Non-existent","P1-Immediate","High","","DLP tool configuration, policy rules, incident logs, data classification integration","","","","","","","","","IT-PRO-011"), |
| ("8.13","A.8.13","Information Backup","Backup copies of information, software, and systems shall be maintained and regularly tested.","Preventive","IT","Not Started","0-Non-existent","P1-Immediate","Critical","","Backup policy (3-2-1 rule), backup schedule, restoration test records, offsite backup evidence","","","","","","","","","IT-PRO-012"), |
| ("8.14","A.8.14","Redundancy of Information Processing Facilities","Information processing facilities shall be implemented with sufficient redundancy to meet availability requirements.","Preventive","IT / Architecture","Not Started","0-Non-existent","P1-Immediate","High","","High availability architecture diagrams, failover test results, RTO/RPO evidence","","","","","","","","","IT-PRO-013"), |
| ("8.15","A.8.15","Logging","Logs that record activities, exceptions, faults, and other relevant events shall be produced, stored, protected, and analysed.","Detective","IT / Security","Not Started","0-Non-existent","P1-Immediate","High","","Logging policy, SIEM configuration, log retention settings, sample log review","","","","","","","","","IT-PRO-014"), |
| ("8.16","A.8.16","Monitoring Activities","Networks, systems, and applications shall be monitored for anomalous behaviour and appropriate actions taken to evaluate potential IS incidents.","Detective","SOC / Security","Not Started","0-Non-existent","P1-Immediate","High","","Monitoring procedure, SIEM use cases, alert thresholds, 24x7 monitoring evidence, NOC/SOC records","","","","","","","","","IT-PRO-015"), |
| ("8.17","A.8.17","Clock Synchronisation","The clocks of information processing systems shall be synchronised to approved time sources.","Preventive","IT","Not Started","0-Non-existent","P3-Medium","Low","","NTP configuration, time synchronisation verification, time zone standards","","","","","","","","","IT-PRO-016"), |
| ("8.18","A.8.18","Use of Privileged Utility Programs","The use of utility programs that may be capable of overriding system and application controls shall be restricted and tightly controlled.","Preventive","IT Admin","Not Started","0-Non-existent","P2-High","High","","Privileged tool register, usage authorization procedure, audit logs for privileged tool usage","","","","","","","","","IT-PRO-017"), |
| ("8.19","A.8.19","Installation of Software on Operational Systems","Procedures and measures shall be implemented to securely manage software installation on operational systems.","Preventive","IT / Change Manager","Not Started","0-Non-existent","P1-Immediate","High","","Software installation policy, approved software list (allowlist), change management integration","","","","","","","","","IT-POL-003"), |
| ("8.20","A.8.20","Networks Security","Networks and network devices shall be secured, managed, and controlled to protect information in systems and applications.","Preventive","Network / IT","Not Started","0-Non-existent","P1-Immediate","Critical","","Network security policy, firewall rules, segmentation architecture diagram, IDS/IPS config","","","","","","","","","IT-POL-004"), |
| ("8.21","A.8.21","Security of Network Services","Security mechanisms, service levels, and service requirements of network services shall be identified, implemented, and monitored.","Preventive","Network / IT","Not Started","0-Non-existent","P2-High","High","","Network service agreements, SLAs, network service monitoring dashboards","","","","","","","","","IT-PRO-018"), |
| ("8.22","A.8.22","Segregation of Networks","Groups of information services, users, and information systems shall be segregated in networks.","Preventive","Network / IT","Not Started","0-Non-existent","P1-Immediate","High","","Network segmentation design (VLAN, firewall zones), DMZ configuration, micro-segmentation records","","","","","","","","","IT-PRO-019"), |
| ("8.23","A.8.23","Web Filtering","Access to external websites shall be managed to reduce exposure to malicious content.","Preventive","IT / Security","Not Started","0-Non-existent","P2-High","Medium","","Web filtering policy, proxy/firewall URL category configuration, blocked site logs","","","","","","","","","IT-PRO-020"), |
| ("8.24","A.8.24","Use of Cryptography","Rules for effective use of cryptography, including cryptographic key management, shall be defined and implemented.","Preventive","CISO / IT","Not Started","0-Non-existent","P1-Immediate","High","","Cryptography policy, approved algorithms list, key management procedure, TLS/encryption config","","","","","","","","","IT-POL-005"), |
| ("8.25","A.8.25","Secure Development Life Cycle","Rules for the secure development of software and systems shall be established and applied.","Preventive","Dev / Architecture","Not Started","0-Non-existent","P1-Immediate","High","","Secure SDLC policy, OWASP integration, security requirements template, code review checklist","","","","","","","","","DEV-POL-002"), |
| ("8.26","A.8.26","Application Security Requirements","Information security requirements shall be identified, specified, and approved when developing or acquiring applications.","Preventive","Dev / Architecture","Not Started","0-Non-existent","P2-High","High","","Security requirements specification template, security acceptance criteria, design reviews","","","","","","","","","DEV-PRO-001"), |
| ("8.27","A.8.27","Secure System Architecture and Engineering Principles","Principles for engineering secure systems shall be established, documented, maintained, and applied to any information system implementation.","Preventive","Architecture / Dev","Not Started","0-Non-existent","P2-High","High","","Security architecture standards, threat modelling records, architecture review board minutes","","","","","","","","","DEV-PRO-002"), |
| ("8.28","A.8.28","Secure Coding","Secure coding principles shall be applied to software development.","Preventive","Development Team","Not Started","0-Non-existent","P2-High","High","","Secure coding standard, SAST/DAST tool reports, code review records, developer training records","","","","","","","","","DEV-PRO-003"), |
| ("8.29","A.8.29","Security Testing in Development and Acceptance","Security testing processes shall be defined and implemented in the development and acceptance lifecycle.","Detective","Dev / QA / Security","Not Started","0-Non-existent","P2-High","High","","Security test plan, SAST results, DAST results, penetration test reports, vulnerability remediation tracking","","","","","","","","","DEV-PRO-004"), |
| ("8.30","A.8.30","Outsourced Development","The organization shall direct, monitor, and review the activities related to outsourced system development.","Preventive","Procurement / Dev","Not Started","0-Non-existent","P2-High","High","","Outsourced dev contracts with security clauses, code review procedure for external code, vendor assessment","","","","","","","","","DEV-PRO-005"), |
| ("8.31","A.8.31","Separation of Development, Test and Production Environments","Development, testing, and production environments shall be separated and secured.","Preventive","IT / Dev","Not Started","0-Non-existent","P1-Immediate","High","","Environment separation policy, network segmentation for environments, access control matrix by environment","","","","","","","","","IT-PRO-021"), |
| ("8.32","A.8.32","Change Management","Changes to information processing facilities and information systems shall be subject to change management procedures.","Preventive","Change Manager / IT","Not Started","0-Non-existent","P1-Immediate","High","","Change management policy, RFC process, CAB minutes, change log, rollback procedures","","","","","","","","","IT-PRO-022"), |
| ("8.33","A.8.33","Test Information","Test information shall be appropriately selected, protected, and managed.","Preventive","Dev / DBA","Not Started","0-Non-existent","P2-High","High","","Test data management procedure, data masking/anonymisation in test environments, test data retention","","","","","","","","","DEV-PRO-006"), |
| ("8.34","A.8.34","Protection of Information Systems During Audit Testing","Audit tests and other assurance activities involving assessment of operational systems shall be planned and agreed between the tester and appropriate management to minimise disruptions.","Preventive","Internal Audit / IT","Not Started","0-Non-existent","P2-High","Medium","","Audit testing procedure, pre-audit agreement documentation, read-only access provisioning records","","","","","","","","","IT-PRO-023"), |
| ] |
|
|
| row = 4 |
| for item in a8_data: |
| add_control_row(ws_a8, row, list(item)) |
| row += 1 |
|
|
| print("A.8 done. Building Statement of Applicability...") |
|
|
| |
| |
| |
| ws_soa = wb.create_sheet("Statement of Applicability") |
| ws_soa.sheet_view.showGridLines = False |
| ws_soa.sheet_properties.tabColor = C["mid_blue"] |
| ws_soa.row_dimensions[1].height = 38 |
| ws_soa.row_dimensions[2].height = 14 |
| ws_soa.row_dimensions[3].height = 35 |
|
|
| ws_soa.merge_cells("A1:K1") |
| c = ws_soa["A1"] |
| c.value = "ISO/IEC 27001:2022 — STATEMENT OF APPLICABILITY (SoA)" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| soa_headers = [ |
| ("A3","Control ID",10), ("B3","Control Title",32), ("C3","Annex A Ref",12), |
| ("D3","Applicable?\n(Yes/No/Partial)",16), ("E3","Justification for Inclusion/Exclusion",40), |
| ("F3","Risk Ref / Driving Requirement",30), ("G3","Implementation Status",20), |
| ("H3","Responsible Owner",20), ("I3","Implementation Notes",40), |
| ("J3","Evidence Reference",30), ("K3","Last Reviewed",14) |
| ] |
| for cell, label, width in soa_headers: |
| hdr(ws_soa, cell, label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_soa.column_dimensions[cell[0]].width = width |
|
|
| dv_list(ws_soa, "D4:D500", YESNO_DV) |
| dv_list(ws_soa, "G4:G500", STATUS_DV) |
|
|
| cf_equal(ws_soa, "D4:D500", '"Yes"', C["green_lt"]) |
| cf_equal(ws_soa, "D4:D500", '"No"', C["red_lt"]) |
| cf_equal(ws_soa, "D4:D500", '"Partial"', C["amber_lt"]) |
| cf_equal(ws_soa, "G4:G500", '"Implemented"', C["green_lt"]) |
| cf_equal(ws_soa, "G4:G500", '"In Progress"', C["amber_lt"]) |
| cf_equal(ws_soa, "G4:G500", '"Not Started"', C["red_lt"]) |
| cf_equal(ws_soa, "G4:G500", '"Not Applicable"', C["gray_lt"]) |
| cf_equal(ws_soa, "G4:G500", '"Partially Implemented"', C["orange_lt"]) |
|
|
| |
| all_soa = [] |
| for item in a5_data: |
| all_soa.append((item[0], item[2], "A.5")) |
| for item in a6_data: |
| all_soa.append((item[0], item[2], "A.6")) |
| for item in a7_data: |
| all_soa.append((item[0], item[2], "A.7")) |
| for item in a8_data: |
| all_soa.append((item[0], item[2], "A.8")) |
|
|
| row = 4 |
| for ctrl_id, ctrl_title, annex in all_soa: |
| bg = C["ice"] if row % 2 == 0 else C["white"] |
| for col, val in zip(["A","B","C","D","E","F","G","H","I","J","K"], |
| [ctrl_id, ctrl_title, annex, "Yes", "Control applicable to ISMS scope", "", "Not Started", "", "", "", ""]): |
| c = ws_soa[f"{col}{row}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","C","D","G","K"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
| ws_soa.row_dimensions[row].height = 32 |
| row += 1 |
|
|
| ws_soa.freeze_panes = "A4" |
|
|
| print("SoA done. Building Risk Register...") |
|
|
| |
| |
| |
| ws_risk = wb.create_sheet("Risk Register") |
| ws_risk.sheet_view.showGridLines = False |
| ws_risk.sheet_properties.tabColor = C["red_dk"] |
| ws_risk.row_dimensions[1].height = 38 |
| ws_risk.row_dimensions[3].height = 35 |
|
|
| ws_risk.merge_cells("A1:R1") |
| c = ws_risk["A1"] |
| c.value = "ISO/IEC 27001:2022 — INFORMATION SECURITY RISK REGISTER" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| risk_cols = [ |
| ("A","Risk ID",10), ("B","Asset / Process",25), ("C","Threat",28), |
| ("D","Vulnerability",28), ("E","Risk Description",42), ("F","Risk Category",18), |
| ("G","Likelihood\n(1-5)",12), ("H","Impact\n(1-5)",12), ("I","Inherent Risk\nScore",14), |
| ("J","Inherent Risk\nRating",14), ("K","Risk Treatment Option",20), |
| ("L","Controls Applied\n(Annex A Ref)",22), ("M","Residual Likelihood\n(1-5)",14), |
| ("N","Residual Impact\n(1-5)",14), ("O","Residual Risk\nScore",14), |
| ("P","Residual Risk\nRating",14), ("Q","Risk Owner",18), ("R","Review Date",14) |
| ] |
| for col, label, width in risk_cols: |
| hdr(ws_risk, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_risk.column_dimensions[col].width = width |
|
|
| |
| sample_risks = [ |
| ("R-001","All Information Assets","Phishing / Social Engineering","Lack of security awareness","Employees click malicious links leading to credential theft or malware infection","Human","4","4","=G4*H4","","Reduce","A.6.3, A.8.7","2","3","=M4*N4","","CISO",""), |
| ("R-002","User Credentials","Brute Force Attack","Weak password policy, no MFA","Attacker gains unauthorized access to systems via credential stuffing","Technical","3","5","=G5*H5","","Reduce","A.5.17, A.8.5","1","5","=M5*N5","","IT Admin",""), |
| ("R-003","Production Systems","Ransomware","Unpatched vulnerabilities, no EDR","Ransomware encrypts production data causing operational disruption","Technical","3","5","=G6*H6","","Reduce","A.8.7, A.8.8, A.8.13","1","4","=M6*N6","","Security Team",""), |
| ("R-004","Cloud Infrastructure","Misconfiguration","Lack of cloud security controls","Sensitive data exposed via public S3 bucket or open firewall rule","Technical","3","4","=G7*H7","","Reduce","A.5.23, A.8.9","1","3","=M7*N7","","Cloud Architect",""), |
| ("R-005","Third-Party Suppliers","Supply Chain Compromise","Inadequate supplier assessment","Vendor software update compromised with malicious code","Supplier","2","5","=G8*H8","","Reduce","A.5.19, A.5.21, A.5.22","1","4","=M8*N8","","Procurement",""), |
| ("R-006","Physical Premises","Unauthorized Physical Access","Inadequate access controls","Intruder gains access to server room or sensitive areas","Physical","2","4","=G9*H9","","Reduce","A.7.1, A.7.2, A.7.4","1","3","=M9*N9","","Facilities Manager",""), |
| ("R-007","Employee Data","Insider Threat","No SoD, excessive privileges","Privileged employee exfiltrates customer or sensitive company data","Human","2","5","=G10*H10","","Reduce","A.5.3, A.8.2, A.8.12","1","4","=M10*N10","","CISO",""), |
| ("R-008","Business Operations","DDoS Attack","No DDoS protection, single ISP","DDoS attack renders customer-facing services unavailable","Technical","3","4","=G11*H11","","Reduce","A.8.20, A.8.14","2","3","=M11*N11","","Network Team",""), |
| ] |
|
|
| for i, risk_row in enumerate(sample_risks, start=4): |
| bg = C["ice"] if i % 2 == 0 else C["white"] |
| ws_risk.row_dimensions[i].height = 40 |
| for j, (col, _, _) in enumerate(risk_cols): |
| val = risk_row[j] if j < len(risk_row) else "" |
| c = ws_risk[f"{col}{i}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","G","H","I","J","M","N","O","P","R"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
| |
| from openpyxl.formatting.rule import ColorScaleRule |
| ws_risk.conditional_formatting.add("I4:I100", |
| ColorScaleRule(start_type="num", start_value=1, start_color="FF63BE7B", |
| mid_type="num", mid_value=12, mid_color="FFFFEB84", |
| end_type="num", end_value=25, end_color="FFF8696B")) |
| ws_risk.conditional_formatting.add("O4:O100", |
| ColorScaleRule(start_type="num", start_value=1, start_color="FF63BE7B", |
| mid_type="num", mid_value=8, mid_color="FFFFEB84", |
| end_type="num", end_value=25, end_color="FFF8696B")) |
|
|
| |
| for i in range(4, 4+len(sample_risks)): |
| for score_col, rating_col in [("I","J"), ("O","P")]: |
| score_cell = f"{score_col}{i}" |
| rating_cell = f"{rating_col}{i}" |
| ws_risk[rating_cell] = f'=IF({score_cell}>=20,"Critical",IF({score_cell}>=15,"High",IF({score_cell}>=8,"Medium",IF({score_cell}>=3,"Low","Very Low"))))' |
| ws_risk[rating_cell].font = Font(name="Calibri", size=9, bold=True) |
| ws_risk[rating_cell].alignment = Alignment(horizontal="center", vertical="center") |
| ws_risk[rating_cell].border = thin_border() |
|
|
| cf_equal(ws_risk, "J4:J100", '"Critical"', C["red_lt"]) |
| cf_equal(ws_risk, "J4:J100", '"High"', C["orange_lt"]) |
| cf_equal(ws_risk, "J4:J100", '"Medium"', C["amber_lt"]) |
| cf_equal(ws_risk, "J4:J100", '"Low"', C["green_lt"]) |
| cf_equal(ws_risk, "P4:P100", '"Critical"', C["red_lt"]) |
| cf_equal(ws_risk, "P4:P100", '"High"', C["orange_lt"]) |
| cf_equal(ws_risk, "P4:P100", '"Medium"', C["amber_lt"]) |
| cf_equal(ws_risk, "P4:P100", '"Low"', C["green_lt"]) |
|
|
| dv_list(ws_risk, "K4:K200", '"Reduce,Accept,Transfer,Avoid"') |
|
|
| ws_risk.freeze_panes = "A4" |
|
|
| print("Risk Register done. Building Evidence Log...") |
|
|
| |
| |
| |
| ws_ev = wb.create_sheet("Evidence Log") |
| ws_ev.sheet_view.showGridLines = False |
| ws_ev.sheet_properties.tabColor = C["green_dk"] |
| ws_ev.row_dimensions[1].height = 38 |
| ws_ev.row_dimensions[3].height = 35 |
|
|
| ws_ev.merge_cells("A1:L1") |
| c = ws_ev["A1"] |
| c.value = "ISO/IEC 27001:2022 — EVIDENCE REGISTER" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| ev_cols = [ |
| ("A","Evidence ID",12), ("B","Control ID(s)",18), ("C","Evidence Name",32), |
| ("D","Evidence Type",18), ("E","Description",42), ("F","Collected By",18), |
| ("G","Date Collected",16), ("H","File Location / Path",38), ("I","Version",10), |
| ("J","Reviewed By",18), ("K","Review Date",14), ("L","Review Outcome",18) |
| ] |
| for col, label, width in ev_cols: |
| hdr(ws_ev, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_ev.column_dimensions[col].width = width |
|
|
| dv_list(ws_ev, "D4:D500", '"Policy,Procedure,Screenshot,Log Extract,Report,Contract,Certificate,Training Record,Audit Report,System Config,Other"') |
| dv_list(ws_ev, "L4:L500", '"Accepted,Minor Issues,Major Issues,Rejected"') |
|
|
| cf_equal(ws_ev, "L4:L500", '"Accepted"', C["green_lt"]) |
| cf_equal(ws_ev, "L4:L500", '"Minor Issues"', C["amber_lt"]) |
| cf_equal(ws_ev, "L4:L500", '"Major Issues"', C["orange_lt"]) |
| cf_equal(ws_ev, "L4:L500", '"Rejected"', C["red_lt"]) |
|
|
| sample_ev = [ |
| ("EV-001","5.1","Information Security Policy","Policy","Board-approved information security policy covering all domains","CISO","","SharePoint/Policies/IS_Policy_v2.0.pdf","v2.0","","",""), |
| ("EV-002","5.2","RACI Matrix","Procedure","IS roles and responsibilities RACI matrix","CISO","","SharePoint/ISMS/RACI_Matrix_v1.2.xlsx","v1.2","","",""), |
| ("EV-003","6.1.2","Risk Assessment Report","Report","Annual information security risk assessment","Risk Officer","","SharePoint/Risk/Risk_Assessment_2025.pdf","2025","","",""), |
| ("EV-004","6.1.3","Statement of Applicability","Report","Completed SoA with justifications for all 93 controls","CISO","","SharePoint/ISMS/SoA_v1.0.xlsx","v1.0","","",""), |
| ("EV-005","8.5","MFA Configuration","System Config","MFA enabled for all critical systems — screenshot evidence","IT Admin","","SharePoint/Evidence/MFA_Config_Screenshots.pdf","","","",""), |
| ("EV-006","6.3","Training Completion Records","Training Record","Annual IS awareness training completion report","HR","","SharePoint/HR/Training_Report_2025.xlsx","2025","","",""), |
| ] |
| for i, ev_row in enumerate(sample_ev, start=4): |
| bg = C["ice"] if i % 2 == 0 else C["white"] |
| ws_ev.row_dimensions[i].height = 32 |
| for j, (col, _, _) in enumerate(ev_cols): |
| val = ev_row[j] if j < len(ev_row) else "" |
| c = ws_ev[f"{col}{i}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","D","G","I","K","L"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
| ws_ev.freeze_panes = "A4" |
|
|
| print("Evidence Log done. Building Nonconformity & CAR sheet...") |
|
|
| |
| |
| |
| ws_nc = wb.create_sheet("Nonconformity & CAR") |
| ws_nc.sheet_view.showGridLines = False |
| ws_nc.sheet_properties.tabColor = C["red_dk"] |
| ws_nc.row_dimensions[1].height = 38 |
| ws_nc.row_dimensions[3].height = 35 |
|
|
| ws_nc.merge_cells("A1:N1") |
| c = ws_nc["A1"] |
| c.value = "ISO/IEC 27001:2022 — NONCONFORMITY & CORRECTIVE ACTION REGISTER" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| nc_cols = [ |
| ("A","CAR ID",10), ("B","Control ID",14), ("C","Source of Finding",20), |
| ("D","Nonconformity Description",45), ("E","Type",16), ("F","Root Cause",38), |
| ("G","Corrective Action",42), ("H","Responsible Owner",20), ("I","Target Date",14), |
| ("J","Status",16), ("K","Date Closed",14), ("L","Effectiveness Review",35), |
| ("M","Recurrence?",14), ("N","Auditor Verification",28) |
| ] |
| for col, label, width in nc_cols: |
| hdr(ws_nc, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_nc.column_dimensions[col].width = width |
|
|
| dv_list(ws_nc, "E4:E500", '"Minor Nonconformity,Major Nonconformity,Observation,Opportunity for Improvement"') |
| dv_list(ws_nc, "J4:J500", '"Open,In Progress,Closed,Verified"') |
| dv_list(ws_nc, "M4:M500", '"Yes,No"') |
|
|
| cf_equal(ws_nc, "E4:E500", '"Major Nonconformity"', C["red_lt"]) |
| cf_equal(ws_nc, "E4:E500", '"Minor Nonconformity"', C["orange_lt"]) |
| cf_equal(ws_nc, "E4:E500", '"Observation"', C["amber_lt"]) |
| cf_equal(ws_nc, "J4:J500", '"Open"', C["red_lt"]) |
| cf_equal(ws_nc, "J4:J500", '"In Progress"', C["amber_lt"]) |
| cf_equal(ws_nc, "J4:J500", '"Closed"', C["green_lt"]) |
| cf_equal(ws_nc, "J4:J500", '"Verified"', C["green_lt"]) |
|
|
| ws_nc.freeze_panes = "A4" |
|
|
| print("NC/CAR done. Building Internal Audit sheet...") |
|
|
| |
| |
| |
| ws_audit = wb.create_sheet("Internal Audit Programme") |
| ws_audit.sheet_view.showGridLines = False |
| ws_audit.sheet_properties.tabColor = C["amber_dk"] |
| ws_audit.row_dimensions[1].height = 38 |
| ws_audit.row_dimensions[3].height = 35 |
|
|
| ws_audit.merge_cells("A1:M1") |
| c = ws_audit["A1"] |
| c.value = "ISO/IEC 27001:2022 — INTERNAL AUDIT PROGRAMME" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| audit_cols = [ |
| ("A","Audit ID",10), ("B","Audit Title",30), ("C","Scope / Controls Covered",38), |
| ("D","Audit Criteria",25), ("E","Lead Auditor",18), ("F","Audit Team",20), |
| ("G","Planned Date",14), ("H","Actual Date",14), ("I","Status",16), |
| ("J","Report Reference",22), ("K","No. of NCs",12), ("L","No. of Observations",16), |
| ("M","Next Audit Date",14) |
| ] |
| for col, label, width in audit_cols: |
| hdr(ws_audit, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_audit.column_dimensions[col].width = width |
|
|
| dv_list(ws_audit, "I4:I200", '"Planned,In Progress,Completed,Cancelled"') |
| cf_equal(ws_audit, "I4:I200", '"Completed"', C["green_lt"]) |
| cf_equal(ws_audit, "I4:I200", '"In Progress"', C["amber_lt"]) |
| cf_equal(ws_audit, "I4:I200", '"Planned"', C["pale_blue"]) |
| cf_equal(ws_audit, "I4:I200", '"Cancelled"', C["gray_lt"]) |
|
|
| audit_samples = [ |
| ("IA-2025-01","ISMS Context & Leadership Audit","Clauses 4, 5 — Context, Interested Parties, Scope, Policy, Roles","ISO 27001:2022 Clauses 4-5","","","Q1 2025","","Planned","","","","Q1 2026"), |
| ("IA-2025-02","Planning & Risk Assessment Audit","Clause 6 — Risk Assessment, Risk Treatment, SoA, Objectives","ISO 27001:2022 Clause 6","","","Q1 2025","","Planned","","","","Q1 2026"), |
| ("IA-2025-03","Support & Operations Audit","Clauses 7, 8 — Resources, Competence, Awareness, Operations","ISO 27001:2022 Clauses 7-8","","","Q2 2025","","Planned","","","","Q2 2026"), |
| ("IA-2025-04","Annex A.5 Organisational Controls Audit","All 37 organisational controls","ISO 27001:2022 Annex A.5","","","Q2 2025","","Planned","","","","Q2 2026"), |
| ("IA-2025-05","Annex A.6 & A.7 People & Physical Audit","People controls 6.1-6.8, Physical controls 7.1-7.14","ISO 27001:2022 Annex A.6, A.7","","","Q3 2025","","Planned","","","","Q3 2026"), |
| ("IA-2025-06","Annex A.8 Technology Controls Audit","All 34 technological controls (8.1-8.34)","ISO 27001:2022 Annex A.8","","","Q3 2025","","Planned","","","","Q3 2026"), |
| ("IA-2025-07","Performance Evaluation & Improvement Audit","Clauses 9, 10 — Monitoring, Management Review, Improvement","ISO 27001:2022 Clauses 9-10","","","Q4 2025","","Planned","","","","Q4 2026"), |
| ("IA-2025-08","Full ISMS Follow-Up Audit","All clauses and Annex A — pre-certification sweep","ISO 27001:2022 Full Standard","","","Q4 2025","","Planned","","","","Q4 2026"), |
| ] |
|
|
| for i, a_row in enumerate(audit_samples, start=4): |
| bg = C["ice"] if i % 2 == 0 else C["white"] |
| ws_audit.row_dimensions[i].height = 35 |
| for j, (col, _, _) in enumerate(audit_cols): |
| val = a_row[j] if j < len(a_row) else "" |
| c = ws_audit[f"{col}{i}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","G","H","I","J","K","L","M"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
| ws_audit.freeze_panes = "A4" |
|
|
| print("Audit Programme done. Building Management Review...") |
|
|
| |
| |
| |
| ws_mgmt = wb.create_sheet("Management Review Log") |
| ws_mgmt.sheet_view.showGridLines = False |
| ws_mgmt.sheet_properties.tabColor = C["blue"] |
| ws_mgmt.row_dimensions[1].height = 38 |
| ws_mgmt.row_dimensions[3].height = 35 |
|
|
| ws_mgmt.merge_cells("A1:J1") |
| c = ws_mgmt["A1"] |
| c.value = "ISO/IEC 27001:2022 — MANAGEMENT REVIEW LOG" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| mgmt_cols = [ |
| ("A","Review ID",10), ("B","Review Date",14), ("C","Attendees",28), |
| ("D","Agenda Items Covered",40), ("E","ISMS Performance Summary",40), |
| ("F","Key Decisions",40), ("G","Action Items",40), |
| ("H","Action Owner",18), ("I","Due Date",14), ("J","Minutes Reference",20) |
| ] |
| for col, label, width in mgmt_cols: |
| hdr(ws_mgmt, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_mgmt.column_dimensions[col].width = width |
|
|
| |
| ws_mgmt.row_dimensions[4].height = 80 |
| mgmt_row = ["MR-001","","CEO, CISO, CTO, DPO, Audit Lead", |
| "1. Previous action items\n2. Internal/external audit results\n3. Risk assessment results\n4. Security incidents\n5. Objective achievement\n6. Stakeholder feedback\n7. Supplier performance\n8. Continual improvement", |
| "","","","","",""] |
| bg = C["ice"] |
| for j, (col, _, _) in enumerate(mgmt_cols): |
| c = ws_mgmt[f"{col}4"] |
| c.value = mgmt_row[j] |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","B","I"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
| ws_mgmt.freeze_panes = "A4" |
|
|
| print("Management Review done. Building Supplier Register...") |
|
|
| |
| |
| |
| ws_sup = wb.create_sheet("Supplier Register") |
| ws_sup.sheet_view.showGridLines = False |
| ws_sup.sheet_properties.tabColor = C["purple_dk"] |
| ws_sup.row_dimensions[1].height = 38 |
| ws_sup.row_dimensions[3].height = 35 |
|
|
| ws_sup.merge_cells("A1:N1") |
| c = ws_sup["A1"] |
| c.value = "ISO/IEC 27001:2022 — SUPPLIER & THIRD-PARTY SECURITY REGISTER" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| sup_cols = [ |
| ("A","Supplier ID",12), ("B","Supplier Name",25), ("C","Services Provided",32), |
| ("D","Data Accessed",28), ("E","Data Classification",18), ("F","Risk Level",14), |
| ("G","IS Assessment Completed",20), ("H","Assessment Date",16), |
| ("I","Certifications Held",25), ("J","Contract Ref",16), ("K","DPA / NDA Signed",16), |
| ("L","Audit Rights",14), ("M","Next Review Date",16), ("N","Notes",30) |
| ] |
| for col, label, width in sup_cols: |
| hdr(ws_sup, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_sup.column_dimensions[col].width = width |
|
|
| dv_list(ws_sup, "F4:F200", RISK_DV) |
| dv_list(ws_sup, "G4:G200", YESNO_DV) |
| dv_list(ws_sup, "K4:K200", YESNO_DV) |
| dv_list(ws_sup, "L4:L200", YESNO_DV) |
|
|
| cf_equal(ws_sup, "F4:F200", '"Critical"', C["red_lt"]) |
| cf_equal(ws_sup, "F4:F200", '"High"', C["orange_lt"]) |
| cf_equal(ws_sup, "F4:F200", '"Medium"', C["amber_lt"]) |
| cf_equal(ws_sup, "F4:F200", '"Low"', C["green_lt"]) |
| cf_equal(ws_sup, "G4:G200", '"No"', C["red_lt"]) |
| cf_equal(ws_sup, "G4:G200", '"Yes"', C["green_lt"]) |
|
|
| ws_sup.freeze_panes = "A4" |
|
|
| print("Supplier Register done. Building Training Register...") |
|
|
| |
| |
| |
| ws_trn = wb.create_sheet("Training Register") |
| ws_trn.sheet_view.showGridLines = False |
| ws_trn.sheet_properties.tabColor = C["green_dk"] |
| ws_trn.row_dimensions[1].height = 38 |
| ws_trn.row_dimensions[3].height = 35 |
|
|
| ws_trn.merge_cells("A1:K1") |
| c = ws_trn["A1"] |
| c.value = "ISO/IEC 27001:2022 — IS TRAINING & AWARENESS REGISTER" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| trn_cols = [ |
| ("A","Training ID",12), ("B","Training Title",32), ("C","Target Audience",22), |
| ("D","Type",18), ("E","Frequency",14), ("F","Delivery Method",18), |
| ("G","Last Delivered",14), ("H","Next Due",14), ("I","Completion Rate",16), |
| ("J","Pass Rate",12), ("K","Records Location",30) |
| ] |
| for col, label, width in trn_cols: |
| hdr(ws_trn, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_trn.column_dimensions[col].width = width |
|
|
| dv_list(ws_trn, "D4:D200", '"Mandatory,Role-Based,Awareness,Technical,Management"') |
| dv_list(ws_trn, "E4:E200", '"Annual,Semi-Annual,Quarterly,Monthly,Ad-Hoc,On-Boarding"') |
|
|
| trn_samples = [ |
| ("TRN-001","Information Security Awareness","All Staff","Mandatory","Annual","E-Learning (LMS)","","","","","SharePoint/Training/IS_Awareness/"), |
| ("TRN-002","Phishing Simulation","All Staff","Awareness","Quarterly","Simulated Phishing Campaign","","","","","SharePoint/Training/Phishing_Reports/"), |
| ("TRN-003","Acceptable Use Policy","All Staff","Mandatory","Annual","E-Learning + Quiz","","","","","SharePoint/Training/AUP_Records/"), |
| ("TRN-004","ISMS Roles & Responsibilities","ISMS Team","Role-Based","Annual","Workshop","","","","","SharePoint/Training/ISMS_Team/"), |
| ("TRN-005","Secure Coding","Development Team","Technical","Annual","Instructor-Led","","","","","SharePoint/Training/Dev_SecureCoding/"), |
| ("TRN-006","Privacy & Data Protection (GDPR)","All Staff","Mandatory","Annual","E-Learning","","","","","SharePoint/Training/Privacy/"), |
| ("TRN-007","Incident Response","Security Team","Role-Based","Annual","Tabletop Exercise","","","","","SharePoint/Training/IR_Exercises/"), |
| ("TRN-008","Supplier Security Requirements","Procurement","Role-Based","Annual","Workshop","","","","","SharePoint/Training/Supplier_Security/"), |
| ("TRN-009","New Employee IS Induction","All New Joiners","Mandatory","On-Boarding","E-Learning + Briefing","","","","","SharePoint/Training/Induction/"), |
| ("TRN-010","IS Risk Management","Risk Team, CISO","Role-Based","Annual","Workshop","","","","","SharePoint/Training/Risk_Mgmt/"), |
| ] |
| for i, t_row in enumerate(trn_samples, start=4): |
| bg = C["ice"] if i % 2 == 0 else C["white"] |
| ws_trn.row_dimensions[i].height = 30 |
| for j, (col, _, _) in enumerate(trn_cols): |
| val = t_row[j] if j < len(t_row) else "" |
| c = ws_trn[f"{col}{i}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","D","E","F","G","H","I","J"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
| ws_trn.freeze_panes = "A4" |
|
|
| print("Training Register done. Building Asset Inventory...") |
|
|
| |
| |
| |
| ws_ast = wb.create_sheet("Asset Inventory") |
| ws_ast.sheet_view.showGridLines = False |
| ws_ast.sheet_properties.tabColor = C["mid_blue"] |
| ws_ast.row_dimensions[1].height = 38 |
| ws_ast.row_dimensions[3].height = 35 |
|
|
| ws_ast.merge_cells("A1:M1") |
| c = ws_ast["A1"] |
| c.value = "ISO/IEC 27001:2022 — INFORMATION ASSET INVENTORY (A.5.9)" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=14) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
|
|
| ast_cols = [ |
| ("A","Asset ID",12), ("B","Asset Name",28), ("C","Asset Type",18), |
| ("D","Asset Description",40), ("E","Asset Owner",18), ("F","Asset Custodian",18), |
| ("G","Classification",16), ("H","Location",22), ("I","Criticality",14), |
| ("J","Availability Req.",14), ("K","Integrity Req.",14), ("L","Confidentiality Req.",18), |
| ("M","Review Date",14) |
| ] |
| for col, label, width in ast_cols: |
| hdr(ws_ast, f"{col}3", label, C["navy"], "FFFFFFFF", True, 9, "center", True) |
| ws_ast.column_dimensions[col].width = width |
|
|
| dv_list(ws_ast, "C4:C500", '"Information,Software,Hardware,Service,People,Facility,Intangible"') |
| dv_list(ws_ast, "G4:G500", '"Public,Internal,Confidential,Restricted"') |
| dv_list(ws_ast, "I4:I500", '"Critical,High,Medium,Low"') |
| dv_list(ws_ast, "J4:J500", '"High,Medium,Low"') |
| dv_list(ws_ast, "K4:K500", '"High,Medium,Low"') |
| dv_list(ws_ast, "L4:L500", '"High,Medium,Low"') |
|
|
| cf_equal(ws_ast, "G4:G500", '"Restricted"', C["red_lt"]) |
| cf_equal(ws_ast, "G4:G500", '"Confidential"', C["orange_lt"]) |
| cf_equal(ws_ast, "G4:G500", '"Internal"', C["amber_lt"]) |
| cf_equal(ws_ast, "G4:G500", '"Public"', C["green_lt"]) |
| cf_equal(ws_ast, "I4:I500", '"Critical"', C["red_lt"]) |
| cf_equal(ws_ast, "I4:I500", '"High"', C["orange_lt"]) |
| cf_equal(ws_ast, "I4:I500", '"Medium"', C["amber_lt"]) |
| cf_equal(ws_ast, "I4:I500", '"Low"', C["green_lt"]) |
|
|
| ast_samples = [ |
| ("AST-001","Customer Database","Information","Database containing PII of all customers","Data Owner (CTO)","DBA","Restricted","AWS RDS - eu-west-1","Critical","High","High","High",""), |
| ("AST-002","Source Code Repository","Software","GitHub Enterprise — all application source code","Dev Lead","IT Admin","Confidential","GitHub Enterprise Cloud","Critical","High","High","High",""), |
| ("AST-003","Active Directory / IdP","Software","User identity and authentication system (Entra ID)","IT Admin","IT Admin","Restricted","Azure AD - Multi-region","Critical","High","High","High",""), |
| ("AST-004","Financial Records","Information","Financial ledgers, invoices, tax records","CFO","Finance Manager","Confidential","SharePoint / Accounting System","High","High","High","High",""), |
| ("AST-005","Employee HR Data","Information","Personnel files, salaries, performance reviews","HR Director","HR Manager","Restricted","HR System (Workday)","High","High","High","High",""), |
| ("AST-006","ISMS Documentation","Information","All ISMS policies, procedures, risk registers","CISO","Document Controller","Confidential","SharePoint/ISMS","High","High","High","Medium",""), |
| ("AST-007","Production Web Servers","Hardware","Apache/Nginx servers hosting customer applications","IT Admin","IT Team","Confidential","AWS EC2 - eu-west-1","Critical","High","High","Medium",""), |
| ("AST-008","Backup Systems","Hardware","Veeam backup appliance + AWS S3 offsite backups","IT Admin","IT Team","Confidential","On-prem + AWS S3","Critical","High","High","High",""), |
| ("AST-009","Corporate Network Infrastructure","Hardware","Cisco switches, routers, firewalls","Network Admin","IT Team","Internal","Server Room / Cloud","High","High","High","Medium",""), |
| ("AST-010","Email System","Service","Microsoft 365 Exchange Online","IT Admin","IT Team","Internal","Microsoft 365 Cloud","High","High","Medium","Medium",""), |
| ] |
| for i, a_row in enumerate(ast_samples, start=4): |
| bg = C["ice"] if i % 2 == 0 else C["white"] |
| ws_ast.row_dimensions[i].height = 32 |
| for j, (col, _, _) in enumerate(ast_cols): |
| val = a_row[j] if j < len(a_row) else "" |
| c = ws_ast[f"{col}{i}"] |
| c.value = val |
| c.font = Font(name="Calibri", size=9) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="center" if col in ["A","C","G","I","J","K","L","M"] else "left", |
| vertical="top", wrap_text=True) |
| c.border = thin_border() |
|
|
| ws_ast.freeze_panes = "A4" |
|
|
| print("Asset Inventory done. Building Instructions sheet...") |
|
|
| |
| |
| |
| ws_ins = wb.create_sheet("Instructions & Guidance") |
| ws_ins.sheet_view.showGridLines = False |
| ws_ins.sheet_properties.tabColor = C["gray_dk"] |
|
|
| for col in ["A","B","C","D","E","F"]: |
| ws_ins.column_dimensions[col].width = 25 |
|
|
| ws_ins.merge_cells("A1:F1") |
| c = ws_ins["A1"] |
| c.value = "ISO/IEC 27001:2022 TRACKER — INSTRUCTIONS & GUIDANCE" |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=16) |
| c.fill = PatternFill("solid", start_color=C["navy"]) |
| c.alignment = Alignment(horizontal="center", vertical="center") |
| ws_ins.row_dimensions[1].height = 45 |
|
|
| guidance = [ |
| ("Cover Sheet", C["blue"], |
| "Fill in the Organization Name, Scope, Certification Body, Audit dates, and Prepared/Approved By fields. The dashboard table updates automatically from the four Annex A control sheets. Monitor overall % Complete to track audit readiness."), |
| ("Clauses 4–10", C["navy"], |
| "Track ISMS mandatory requirements (Clauses 4 to 10). Each clause is a required conformance item — none can be excluded. Fill in Owner, Status (dropdown), Maturity Level, Priority, Evidence Required, and Evidence Location for each item. Update 'Status' as you implement each requirement."), |
| ("Annex A Controls (Sheets A.5 – A.8)", C["blue"], |
| "ISO 27001:2022 has 93 controls across 4 domains: A.5 Organisational (37), A.6 People (8), A.7 Physical (14), A.8 Technological (34). Each control row has: Control ID, Title, Requirement description, Owner, Status, Maturity, Priority, Risk Level, Evidence Required, and Evidence Location. All 93 controls are pre-populated. Use the Status dropdown on each row."), |
| ("Statement of Applicability (SoA)", C["mid_blue"], |
| "The SoA is a mandatory ISO 27001 document. For each of the 93 Annex A controls, confirm whether it is Applicable (Yes/No/Partial) and document the justification. The SoA must be signed off by management before Stage 2 audit. Pre-populated with all 93 controls — just fill in Applicable, Justification, and Status columns."), |
| ("Risk Register", C["blue"], |
| "Document all identified information security risks. For each risk, record the asset, threat, vulnerability, Likelihood (1-5) and Impact (1-5). The Inherent Risk Score is calculated automatically (L×I). After applying controls, record Residual Likelihood and Impact. The Residual Score and Rating are auto-calculated. Risk treatment options: Reduce, Accept, Transfer, Avoid."), |
| ("Evidence Log", C["green_dk"], |
| "Log every piece of evidence you collect. Reference the Control ID(s) it supports. Include file path or link to the actual document. This provides your auditor with a clear map from control to evidence. Use the Evidence Type dropdown. Evidence should be current, complete, and show the control is operating."), |
| ("Nonconformity & CAR", C["red_dk"], |
| "Log all findings from internal audits, management reviews, or external audits. Classify as Major NC, Minor NC, Observation, or OFI. Conduct root cause analysis for each nonconformity. Track corrective actions through to closure. Verify effectiveness before marking Closed."), |
| ("Internal Audit Programme", C["amber_dk"], |
| "Plan your full annual internal audit cycle. ISO 27001 requires internal audits at planned intervals. Pre-populated with 8 suggested audit cycles covering all clauses. Assign Lead Auditors (must be independent of the area being audited). Record actual dates, NCs, and observations from each audit."), |
| ("Management Review Log", C["blue"], |
| "Top management must review the ISMS at planned intervals (typically annually, min. 1x per year). Record attendance, agenda items, ISMS performance summary, decisions, and action items. Minutes must be retained as documented information. The agenda template is pre-filled in row 4."), |
| ("Supplier Register", C["purple_dk"], |
| "Maintain a register of all suppliers with access to your information or systems. Assess each supplier's risk level. Ensure security clauses are in all supplier contracts. Record certifications (ISO 27001, SOC 2, etc.), DPA/NDA status, and audit rights. Review suppliers at least annually."), |
| ("Training Register", C["green_dk"], |
| "Track all IS training and awareness activities. ISO 27001 requires all staff to be aware of the IS policy, their role, and consequences of non-conformity. Record completion rates and next due dates. Phishing simulations are strongly recommended as evidence of security culture."), |
| ("Asset Inventory", C["mid_blue"], |
| "Maintain a complete inventory of all information assets per A.5.9. Each asset must have a designated Owner. Assets are classified by Confidentiality level. Assess CIA (Confidentiality, Integrity, Availability) requirements for each asset. The asset inventory is foundational to risk assessment and access control."), |
| ("Status Values", C["gray_dk"], |
| "Not Started = Control not yet addressed.\nIn Progress = Work underway, not yet complete.\nImplemented = Control fully implemented with evidence.\nPartially Implemented = Some aspects implemented but gaps remain.\nNot Applicable = Control excluded via SoA with documented justification."), |
| ("Maturity Levels", C["gray_dk"], |
| "0-Non-existent: No process exists.\n1-Initial: Ad hoc, undocumented.\n2-Repeatable: Process exists but not formally defined.\n3-Defined: Documented, standardised process.\n4-Managed: Measured and controlled.\n5-Optimized: Continuously improved, best practice."), |
| ("Certification Timeline", C["navy"], |
| "Stage 1 Audit: Document review — auditor checks ISMS documentation is complete and suitable.\nStage 2 Audit: Implementation audit — auditor verifies controls are implemented and effective.\nSurveillance Audits: Annual checks to maintain certification.\nRecertification: Full re-audit every 3 years.\nRecommendation: Achieve ≥80% 'Implemented' before Stage 1."), |
| ] |
|
|
| row = 3 |
| for title, bg, text in guidance: |
| ws_ins.row_dimensions[row].height = 22 |
| ws_ins.merge_cells(f"A{row}:F{row}") |
| c = ws_ins[f"A{row}"] |
| c.value = title |
| c.font = Font(name="Calibri", bold=True, color="FFFFFFFF", size=11) |
| c.fill = PatternFill("solid", start_color=bg) |
| c.alignment = Alignment(horizontal="left", vertical="center") |
| row += 1 |
|
|
| ws_ins.row_dimensions[row].height = max(60, len(text)//3) |
| ws_ins.merge_cells(f"A{row}:F{row}") |
| c = ws_ins[f"A{row}"] |
| c.value = text |
| c.font = Font(name="Calibri", size=10) |
| c.fill = PatternFill("solid", start_color=C["ice"]) |
| c.alignment = Alignment(horizontal="left", vertical="top", wrap_text=True) |
| c.border = thin_border() |
| row += 2 |
|
|
|
|
| |
| out = "/app/ISO27001_2022_Tracker.xlsx" |
| wb.save(out) |
| print(f"\nSaved: {out}") |
