Check out this Power BI template I adapted for RF risk score tracking.

I've observed a recurring pattern in discussions about Recorded Future's risk scores: teams ingest the data via API or CSV exports, but struggle to move beyond simple dashboarding to actionable, longitudinal analysis. The platform's native visuals are sufficient for point-in-time review, but they lack the flexibility for correlating score changes with internal deployment cycles, vulnerability scans, or external threat actor campaigns.

To address this, I've adapted an existing Power BI template to create a more analytical framework for RF risk score tracking. The core premise is to treat risk scores as time-series metrics, enabling trend analysis, anomaly detection, and cost/benefit evaluation of mitigation efforts. The template requires a structured data pipeline, which I've implemented using a combination of RF's API and a staging database.

**Key Adaptations and Data Model:**

* **Temporal Granularity:** The default template aggregated on `last_updated`. I've split this into separate dimensions for `date` and `time` and added a `snapshot_timestamp` for each daily pull, allowing for precise change tracking.
* **Entity Linkage:** I created a bridge table to associate multiple IPs, Domains, and Hashes to a single internal asset identifier (e.g., a server ID or application name). This is critical for mapping RF's external intelligence to internal inventory.
* **Calculated Measures for Benchmarking:**
* `Score Velocity`: Day-over-day absolute change in risk score.
* `Rank Delta`: Movement within our internal prioritized asset list.
* `Mitigation Efficacy`: A placeholder KPI to, after manual input, correlate score reductions with specific security actions.

**Required Data Pipeline (Simplified Outline):**

```sql
-- Staging table schema for raw API JSON payload
CREATE TABLE stg.rf_risk_scores (
snapshot_date DATE NOT NULL,
entity_id VARCHAR(255) NOT NULL,
entity_type VARCHAR(50),
raw_score INTEGER,
risk_rules JSONB,
evidence_details JSONB,
PRIMARY KEY (snapshot_date, entity_id)
);

-- Flattened fact table for analysis
CREATE TABLE fact.risk_score_trend (
asset_id INTEGER, -- Internal reference
rf_entity_id VARCHAR(255),
score_date DATE,
base_score INTEGER,
criticality_weight DECIMAL(3,2)
);
```

**Pitfalls and Validation Notes:**

* **API Rate Limiting:** Daily full pulls of a large entity set can be throttled. The template now includes a query pattern that prioritizes entities with recent score volatility.
* **Data Cardinality:** Linking one internal asset to dozens of RF entities (IPs, domains) can explode fact table size. Use aggregations (e.g., taking the max score per asset per day) cautiously, as they mask granular intelligence.
* **Reproducibility:** The initial PBIX file made undocumented assumptions about risk rule categorization. I've documented all DAX measures and published the underlying SQL views to a repository for peer review.

The primary value of this adaptation is not in the visuals themselves, but in enforcing a disciplined, queryable historical record of risk scores. This allows for retrospective analysis: for example, did assets that received a "Critical" score from Rule X actually experience a security incident within 30 days? Without this historical context, you're merely viewing a real-time alert board, not measuring the predictive accuracy or business impact of the intelligence.

I am interested in critiques of the data model, particularly regarding evidence storage. Currently, we store the raw `evidence_details` JSON for audit but do not parse it into the dimensional model. A more complete solution would involve flattening key evidence fields for filtering, but this requires a stable schema from the RF API, which has proven elusive.