# mylesaustin-design/vulnerability_tracking architecture

> Architecture diagram of the GitHub repository mylesaustin-design/vulnerability_tracking, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-06). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/mylesaustin-design/vulnerability_tracking
- Repository: https://github.com/mylesaustin-design/vulnerability_tracking

## Overview

##### Project type and purpose
- This is a **Vulnerability Tracking and Remediation System** built primarily with **Excel** and **Power BI**.
- Its purpose is to ingest large vulnerability scan datasets, dynamically segment and filter them by team, produce dashboards in Excel, and deliver interactive reports in Power BI with automated refreshes and alerts.

##### File structure
- Top‐level files: multiple `.xlsx` workbooks (team trackers, master data, past‐due tracker), a `.pbix` file (Power BI report), a source CSV, and documentation (`README.md`, `.docx`).
- There is no traditional code directory; most logic lives in Excel formulas (FILTER, VLOOKUP, Pivot Tables) and in the Power BI model (DirectQuery).
- Supplementary files: `Tracker-Images`, `Vulnerability-Tracker-Images`, and `index.html` likely host embedded visuals or a simple web landing page.

##### README for additional insights
- **Data ingestion**: a 300k-row CSV is standardized into the “Main Data” tab in Excel.
- **Excel workbooks**: dynamic FILTER-based segmentation per team; an Overview Dashboard using Pivot Tables & heatmaps.
- **Power BI**: DirectQuery to a vulnerability database, interactive filters, subscriptions/alerts, and automated dataset refreshes.
- **Remediation Past Due Tracker**: central Excel workbook leveraging VLOOKUPs to combine all teams’ overdue items.

##### Main components
a. Data Sources
- External Vulnerability Scanner / Database (CSV export)
- `Vulnerability_Tracking_Source Data.csv`
b. Excel Layer
- **Main Data Workbook** (raw import, standardized rows)
- **Team‐Specific Workbooks** (Application Dev, IT Security, Network Ops, Compliance) created via FILTER
- **Overview Dashboard** workbook with Pivot Tables, heatmaps, slicers
- **Remediation Past Due Tracker** workbook using VLOOKUP to aggregate overdue items
c. Power BI Layer
- **Power BI Desktop (.pbix)** with DirectQuery connection to vulnerability database or to the Excel “Main Data”
- **Interactive Reports**: device filters, date slicers, KPIs for compliance and remediation status
- **Subscriptions & Alerts** for stakeholders
d. Distribution & Automation
- Scheduled dataset refreshes (Power BI Service)
- Email alerts (via Power BI subscriptions)
- Manual “drop new data” step in Excel to trigger workbook updates

##### Relationships and data flows
- **Ingestion Flow**: Vulnerability CSV → Main Data tab (Excel)
- **Excel Processing**: Main Data → FILTER → Team Workbooks → Pivot Dashboards & Past Due Tracker
- **Reporting Flow**: Power BI DirectQuery ↔ Database or Excel Main Data → Interactive Dashboards
- **Automation & Alerts**: Scheduled refresh → Subscriber emails

##### Architectural patterns and design principles
- Data‐driven spreadsheet architecture: single source of truth (Main Data tab) + dynamic slicing
- BI layering: separation of data ingestion, transformation (Excel), and presentation (Excel dashboards + Power BI)
- Loose coupling: Excel and Power BI connected via DirectQuery or shared database, allowing independent updates

##### Relevant technologies, frameworks, or libraries
- Microsoft Excel functions: FILTER, VLOOKUP, Pivot Tables, Heatmaps
- Power BI: DirectQuery, Data Model, DAX measures, Service‐side refresh, subscriptions/alerts
- Optional: GitHub Actions (jekyll-gh-pages.yml) to deploy `index.html` if hosting documentation

##### Elements to include for clarity
- Arrowheads on all connectors denoting direction of data movement
- Callouts for key functions (e.g., “FILTER formulas for team segmentation”, “DirectQuery for real‐time data”)
- Version or refresh frequency notes (e.g., Daily Excel data drop, hourly Power BI refresh)

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Ingestion Layer
    subgraph "Ingestion Layer"
        direction TB
        CSV((Vulnerability_Tracking_Source Data.csv)):::dataSources
    end

    %% Excel Processing Layer
    subgraph "Excel Processing Layer"
        direction TB
        MasterExcel["Vulnerability_Tracking_Master.xlsx\n(Main Data Workbook)"]:::excel
        APPDev["APP Dev Team Tracker.xlsx"]:::excel
        ITSec["IT Security Team Tracker.xlsx"]:::excel
        NetOps["Network Ops Team Tracker.xlsx"]:::excel
        Compliance["Compliance Team Tracker.xlsx"]:::excel
        DBAdmins["Database Admins Team Tracker.xlsx"]:::excel
        Remediation["Remediation Past Due Tracker.xlsx"]:::excel
    end

    %% Power BI Layer
    subgraph "BI Reporting Layer"
        direction TB
        PBIX["FIRST CITIZENS BANK SECURITY ANALYSIS.pbix"]:::powerbi
    end

    %% Distribution & Automation Layer
    subgraph "Distribution & Automation Layer"
        direction TB
        Alerts["Email Alerts (Power BI Subscriptions)"]:::distribution
        README["README.md"]:::distribution
        SOPExcel["Mock Standard Operating Procedure Excel based Vulnerability Tracking.docx"]:::distribution
        SOPPB["Mock Standard Operating Procedure(PowerBi Analysis and Automation).docx"]:::distribution
        Index["index.html"]:::distribution
        TrackerImgs["Tracker-Images/"]:::distribution
        VulnImgs["Vulnerability-Tracker-Images/"]:::distribution
        CI[".github/workflows/jekyll-gh-pages.yml"]:::distribution
    end

    %% Connections
    CSV -->|"ingest into"| MasterExcel
    MasterExcel -->|"FILTER segmentation"| APPDev
    MasterExcel -->|"FILTER segmentation"| ITSec
    MasterExcel -->|"FILTER segmentation"| NetOps
    MasterExcel -->|"FILTER segmentation"| Compliance
    MasterExcel -->|"FILTER segmentation"| DBAdmins
    MasterExcel -->|"VLOOKUP aggregation"| Remediation
    MasterExcel -->|"source for DirectQuery"| PBIX
    PBIX -->|"subscriptions & alerts"| Alerts
    PBIX -.->|"scheduled refresh"| PBIX

    %% Click Events
    click CSV "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Vulnerability_Tracking_Source Data.csv"
    click MasterExcel "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Vulnerability_Tracking_Master.xlsx"
    click APPDev "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/APP Dev Team Tracker.xlsx"
    click ITSec "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/IT Security Team Tracker.xlsx"
    click NetOps "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Network Ops Team Tracker.xlsx"
    click Compliance "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Compliance Team Tracker.xlsx"
    click DBAdmins "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Database Admins Team Tracker.xlsx"
    click Remediation "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Remediation Past Due Tracker.xlsx"
    click PBIX "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/FIRST CITIZENS BANK SECURITY ANALYSIS.pbix"
    click README "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/README.md"
    click SOPExcel "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Mock Standard Operating Procedure Excel based Vulnerability Tracking.docx"
    click SOPPB "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/Mock Standard Operating Procedure(PowerBi Analysis and Automation).docx"
    click Index "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/index.html"
    click TrackerImgs "https://github.com/mylesaustin-design/vulnerability_tracking/tree/main/Tracker-Images/"
    click VulnImgs "https://github.com/mylesaustin-design/vulnerability_tracking/tree/main/Vulnerability-Tracker-Images/"
    click CI "https://github.com/mylesaustin-design/vulnerability_tracking/blob/main/.github/workflows/jekyll-gh-pages.yml"

    %% Styles
    classDef dataSources fill:#cce5ff,stroke:#3399ff,color:#000
    classDef excel fill:#d4edda,stroke:#28a745,color:#000
    classDef powerbi fill:#ffe5b4,stroke:#ff9933,color:#000
    classDef distribution fill:#e5ccff,stroke:#9933ff,color:#000
```
