I've been auditing our Umbrella Roaming Client deployment data, and the reporting inconsistencies are a significant hurdle for clean analysis. The core issue seems to be the separation of data across different reports, with no clear join key.
For example, to get a complete device-level view, you must reconcile:
* `Identities` from the **Roaming Computers** report
* `DNS Activity` from the **Activity Search** report
* `Security Activity` from the **Security Reports**
Attempting to correlate these often results in mismatched counts. The `Device ID` in one report doesn't reliably map to the `Internal IP` in another over time, especially for laptops that move networks.
Has anyone built a reliable external data model or ETL pipeline to unify these streams? I'm considering using the Investigate API to pull logs into a warehouse and model it myself with dbt, but the schema mapping is non-trivial.
Key pain points:
* Lack of a stable, unique device key across all data tables.
* Timezone handling in CSV exports vs. the dashboard is inconsistent.
* The "First Connected" date in the Roaming Computers report can reset, breaking time-series analysis.
EXPLAIN ANALYZE
You've precisely identified the core schema problem. The absence of a durable, global device key forces a probabilistic join on temporal fields and unstable identifiers, which is untenable for accurate analysis.
I've implemented a pipeline using the Reporting API, not just Investigate, to ingest into BigQuery. The required data model uses a synthesized key based on a hash of the machine GUID and the organization ID, supplemented by a slowly changing dimension table to track `First Connected` resets. The timezone inconsistency you noted is critical; all timestamps must be normalized to UTC at ingestion, ignoring the dashboard's presentation layer.
Your approach with dbt is correct, but the transformation logic must account for the `Roaming Computers` report being a point-in-time snapshot, not a log. Joining it to activity streams requires a `valid_from`/`valid_to` window to correctly associate historical events, otherwise you'll see the mismatched counts. Have you validated whether the `Device ID` remains constant across a device's re-provisioning event?
Your synthesized key approach is a solid engineering workaround, but it feels like we're building a data warehouse to compensate for a product's schema oversight. The real contrarian point is whether we should even be trying to build these permanent joins.
> the `Device ID` remains constant across a device's re-provisioning event
It doesn't. I've seen it roll on a clean reinstall of the client, which completely decouples the 'new' device from its prior security events. Your slowly changing dimension table becomes a history of ghosts. We're left creating a consensus identity from a pile of unstable identifiers, which is frankly more art than science. Sometimes the correct answer is to treat these as three separate, loosely related datasets and avoid the join entirely.
You're right about the unstable Device ID, but building a data model for a broken source is a trap.
The real problem is expecting a "complete device-level view" from a tool designed for network-level security, not device forensics. You're fighting the product's purpose. Every hour you spend on that dbt model is an hour not spent asking why you need this joined view in the first place.
Your last pain point says it all. If "First Connected" can reset, the vendor clearly doesn't guarantee a durable identity. So stop trying to build one. 😉