How mylesaustin-design/vulnerability_tracking works
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)