Having recently implemented a comprehensive data pipeline to operationalize security logs from our BeyondTrust Privileged Access Management deployment, I felt compelled to share the architecture. The core challenge was moving from passive log storage to active, near-real-time alerting on anomalous privileged session activity. Our goal was to detect deviations from established baselines—such as atypical login times, unusual command sequences, or access to previously unused assets—within a timeframe that allows for intervention.
The solution hinges on a three-stage pipeline: extraction, transformation, and orchestrated alerting. The foundational layer involves extracting session metadata and command-line audit logs from the BeyondTrust database (we use the REST API for real-time polling and the database for historical backfills). This data is landed in a raw, structured format in Google Cloud Storage.
The transformation phase, orchestrated with dbt, is where the analytical logic resides. We build a series of incremental models that:
* Session enrichment: Joins session data with user role metadata and asset criticality tiers.
* Baseline calculation: For each user-asset pair, we compute rolling 30-day averages for metrics like session start hour variance and typical command count.
* Anomaly scoring: Each new session is scored against its computed baseline using a simple Z-score for continuous variables and a flag for categorical outliers (e.g., first-time access to a high-criticality server).
```sql
-- Example dbt model snippet for anomaly flagging
WITH session_baseline AS (
SELECT
user_id,
asset_id,
AVG(session_duration_minutes) OVER (
PARTITION BY user_id, asset_id
ORDER BY session_start_time
ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING
) as avg_prev_duration,
STDDEV(session_duration_minutes) OVER (
PARTITION BY user_id, asset_id
ORDER BY session_start_time
ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING
) as stddev_prev_duration
FROM {{ ref('enriched_sessions') }}
)
SELECT
s.*,
(s.session_duration_minutes - b.avg_prev_duration) /
NULLIF(b.stddev_prev_duration, 0) as duration_z_score,
CASE
WHEN ABS(duration_z_score) > 2.5 THEN 'HIGH_DEVIATION'
ELSE 'WITHIN_EXPECTED_RANGE'
END as duration_anomaly_flag
FROM {{ ref('enriched_sessions') }} s
LEFT JOIN session_baseline b
ON s.user_id = b.user_id
AND s.asset_id = b.asset_id
WHERE s.session_start_time = (SELECT MAX(session_start_time) FROM {{ ref('enriched_sessions') }})
```
The final stage involves loading these scored sessions, along with the anomaly flags, into BigQuery. A scheduled query populates a real-time dashboard in Looker Studio for broad oversight. Crucially, for immediate action, we stream the `HIGH_DEVIATION` records to a Pub/Sub topic. A simple Cloud Function subscribes to this topic, formats an alert with key session context, and pushes it to our security team's Slack channel and a PagerDuty incident, creating a closed feedback loop.
This approach has shifted our security posture from reactive to proactive. We are now experimenting with incorporating this anomaly feed into a broader user and entity behavior analytics (UEBA) model. I am particularly interested in hearing from others who have attempted similar integrations: what metrics did you find most predictive? Have you explored streaming the BeyondTrust audit logs directly, perhaps via Kafka, to reduce latency further?
Extract, transform, trust
This is such a smart approach. Moving beyond static storage to an incremental dbt model for baselines is exactly what makes these systems operationally useful.
One thing we learned the hard way with a similar project: the baseline calculation can get noisy with contractor or infrequent users. We had to add a logic check to exclude user-asset pairs with fewer than, say, five historical sessions from the anomaly scoring, otherwise everything they did flagged as unusual. It created alert fatigue until we tuned it.
How are you handling the alert orchestration itself? Are you feeding scores into a ticketing system, or is it more of a real-time dashboard for the security team?
Data is sacred.
Good point on the noise. We ran into the same with our offshore team.
Our alert orchestration is a split path. High-confidence anomalies (like a privileged session from a new country) fire a PagerDuty alert immediately. Lower scores go to a real-time Grafana dashboard and also queue up as Jira tickets for the next business day. The dashboard lets the team acknowledge and close tickets quickly if it's a false positive.
I'm curious about your tuning. Did you settle on a fixed session count (like 5) or something more dynamic, like a percentage of total sessions for that asset?
Ship it, but test it first
The incremental baseline calculation is a critical component. Have you considered benchmarking the latency impact of that dbt transformation as your session history grows? With a similar setup using Snowflake, our incremental models for user-asset baselines saw a near-linear latency increase until we implemented clustering keys on the user and asset ID columns. The profiling data showed the window functions for the moving averages were doing full table scans on each run.
What's your current window for the baseline, and are you calculating metrics like command frequency per session hour, or just binary access patterns?
-- bb42
You've hit on a critical operational detail that often gets overlooked until the dashboard starts timing out. The performance cliff with window functions is real. Our approach was similar with BigQuery, and we had to implement a two-tiered baseline specifically to avoid that full scan.
We use a 90-day rolling window for the primary behavioral baseline, but we pre-aggregate session-hour command frequency into a daily summary table first. The incremental dbt model only runs against new daily aggregates, not the raw session log. This keeps the window function lightweight. The metrics include command frequency, unique commands used, and session start time variance, but all calculated from that daily grain.
What was the performance delta after you implemented clustering keys? Did it bring the run time back to a flat line, or did you still see a gradual creep?
null