Analytics Dashboard (Grafana)

The Analytics Dashboard provides visual insights into vulnerability trends, host statistics, and future security posture predictions using Grafana.

Overview

Access the dashboard at /grafana/d/vulnerability_reports (requires login).

The dashboard focuses on three key areas: 1. Host-Level Metrics: Top vulnerable hosts and their risk profiles. 2. Historical Trends: Vulnerability counts over time. 3. Predictive Analytics: Forecasting future vulnerability counts based on historical trends.

Dashboard Panels

Top 10 Vulnerable Hosts

Displays the most critical hosts sorted by: 1. Average Vulnerability Severity 2. Total Vulnerability Count

Features: - Filterable by Selected Host variable. - Shows unique Products, Sources, and Locations for context.

Vulnerability Trend over Time (Forecast)

This is the centerpiece of the analytics dashboard. It visualizes: 1. Historical Data (Yellow): Actual daily vulnerability counts from scan reports. 2. Forecast Data (Green Dashed): A projection of future vulnerability counts for the next 30 days.

Methodology:

The forecast is generated using a Momentum-Based Linear Regression combined with Simulated Seasonality.

1. Data Selection (The Momentum) Instead of calculating the trend over the entire history (which often results in a static, flat average), the model focuses on Momentum. It selects only the Last 7 Days of historical data to calculate the slope. This ensures the forecast reacts quickly to recent changes (e.g., a sudden spike in vulnerabilities or a rapid remediation effort).

2. Linear Regression (The Direction) A “Least Squares” Linear Regression is performed on the normalized 7-day window. - X-Axis: Normalized to Days (0, 1, 2…) to ensure mathematical stability and avoid integer overflow errors. - Slope ($m$): Represents the daily rate of change (e.g., +5 vulnerabilities/day). - Intercept ($b$): The baseline starting point.

3. Seasonality (The Movement) Real-world data is rarely a straight line. To better simulate daily variance and uncertainty, a Sine Wave component is added to the projection. - Formula: $y_{pred} = (mx + b) + A cdot sin(x)$ - Effect: The forecast line “wobbles” slightly, providing a visual indication that the prediction is an estimate with expected variance, rather than a rigid target.

4. Bridge The forecast series explicitly starts at the exact timestamp and value of the last available historical data point. This ensures a seamless visual transition from “History” to “Future”.

Filters and Variables

The dashboard supports dynamic filtering:

  • Time Range: Defaults to Last 7 Days (essential for the momentum model).

  • Forecast Window: Select the historical range for the regression model (7, 30, 90, or 365 Days). Default: 365 Days.

  • Institution: Filter by Faculty or Department (e.g., “Fakultät für Informatik”).

  • Host: Select specific IPs or Hostnames.

  • Severity: Filter by risk level (High/Medium/Low).

  • Vulnerability: Focus on a specific CVE or NVT name.

  • OS: Filter by Operating System (e.g., “Ubuntu 22.04 LTS”).

Configuration

The dashboard is provisioned via JSON at src/grafana/dashboards/vulnerability_reports.json. Modifications to the SQL queries should be made in this file and applied by restarting the Grafana container.

docker compose restart grafana

Technical Implementation Details

The forecast is calculated dynamically using a multi-stage Common Table Expression (CTE) SQL query.

Query Breakdown

  1. historical_data

    • Purpose: Aggregates raw scan results into daily counts.

    • Logic: joins scan_results, scans, hosts, etc., and groups by DATE(scan_date).

  2. recent_stats

    • Purpose: Defines the time window for the momentum calculation.

    • Logic: Selects the MIN and MAX date from the Last 7 Days.

  3. recent_data_normalized

    • Purpose: Prepares data for regression by normalizing the X-axis.

    • Logic: Converts dates into integer days ($0, 1, 2…$) using DATEDIFF(time, start_date). This is crucial for numerical stability (avoiding integer overflow).

  4. regression

    • Purpose: Calculates the Slope ($m$) and Intercept ($b$) using the Least Squares Method.

    • Formula - Slope ($m$):

      \[m = \frac{N \sum(xy) - \sum x \sum y}{N \sum(x^2) - (\sum x)^2}\]

    • Formula - Intercept ($b$):

      \[b = \bar{y} - m\bar{x}\]

    • SQL Implementation: Uses SUM(), COUNT(), and AVG() aggregate functions on the normalized data.

  5. forecast_days

    • Purpose: Generates the future timeline.

    • Logic: Creates a sequence of integers (1, 2, 3… 30) representing “Days into the future”.

  6. Final SELECT (Forecast Union)

    • Purpose: Projects the regression line into the future and adds seasonality.

    • Formula:

      \[y_{pred} = b + m \cdot (x_{current} + n) + 5 \cdot \sin(n)\]

    • x_{current}: The total days from the start of the window to “today”.

    • n: The days into the future.

    • 5 * SIN(n): The sine wave component adding “wobble” (seasonality).