Hi everyone. First post here, feeling a bit out of my depth but wanted to share a project I just finished. It's a risk scoring system for actions taken by our Absolute Secure Access agents.
The goal: flag unusual agent behavior (like mass deletions or config changes from a new IP) for security review.
My stack:
* **Airbyte** to pull agent event logs from the Absolute API.
* **BigQuery** as the warehouse.
* **dbt** to transform the raw logs and calculate scores.
* A simple Python script to orchestrate it daily.
The core logic is in dbt. We score each agent action on things like:
* `action_rarity` (how unusual is this event type?)
* `volume_deviation` (is this 10x their normal daily activity?)
* `location_risk` (is the request coming from a new country?)
Here's a snippet of the scoring model:
```sql
WITH scored_events AS (
SELECT
agent_id,
event_timestamp,
event_type,
-- Scoring factors
action_rarity_score,
volume_deviation_score,
location_risk_score,
-- Composite score
(action_rarity_score * 0.5 +
volume_deviation_score * 0.3 +
location_risk_score * 0.2) as risk_score
FROM {{ ref('agent_events_enriched') }}
)
SELECT * FROM scored_events
WHERE risk_score > 0.7
```
It outputs a daily list of high-risk events for our security team. It's simple, but it's my first end-to-end pipeline that's actually in production! 😅
I'm sure there are better ways to do this. How do you handle weighting the risk factors? Is a linear combination too naive? Also, any tips on monitoring the Airbyte connection for API changes?
Thanks for sharing this, and welcome. This is a really practical use case that a lot of teams in the endpoint management space could benefit from. You've clearly thought about the key behavioral signals.
One thing I'd gently push on is your static weighting for the composite score. Using fixed values like 0.5 and 0.3 for the factors might not adapt if an attacker learns your scoring model. Have you considered a way to periodically reassess those weights, maybe based on which factors were most predictive in past genuine incidents? You could end up over-penalizing location changes if an agent legitimately travels.
What's your threshold for flagging an agent, and how are those alerts routed to your security team for review?
Quality over quantity.
Great point about the static weights. It's a solid starting point, but you're right - it can become a rigid formula that misses new attack patterns.
For alerting, we push high-risk scores (anything above 85/100) directly to a Slack channel via a simple webhook from the Python orchestrator. It includes a link to the agent's event history in our internal dashboard. The routing is reliable, but I'm curious about your last question - how do you handle the review process to avoid alert fatigue? Do you gate it with a second system?
Webhooks or bust.
Threshold-based alerts directly to a team channel is a recipe for burnout, full stop. That internal dashboard link won't get clicked after the third false positive about an admin working from a coffee shop.
You need a second system, but not another magical scoring layer. You need a simple state machine. We implemented a "mute" status in our Postgres review table that a security analyst can set with one click from the Slack alert itself, using a shortcut that calls a tiny Flask endpoint. The next run of the scoring pipeline filters out muted agents for 24 hours. It's not elegant, but it moves the triage out of the inbox and into the data model.
Beyond that, you have to feed back what was a true positive. We run a weekly query comparing high-scoring events against our ticketing system's "confirmed incident" tags, then adjust the volume deviation thresholds by agent role. It's manual, but it keeps the weights from becoming completely detached from reality.
I'm intrigued by your choice to implement the core scoring logic entirely within dbt. While it's architecturally clean for a first pass, have you quantified the latency impact of running those window functions over your full event history in BigQuery? The `volume_deviation` calculation requiring a baseline of "normal daily activity" sounds particularly heavy.
Shifting this to an incremental, stateful model could reduce your daily batch runtime significantly. Instead of a full recalculation, you could maintain a small lookup table of per-agent behavioral baselines (rolling 30-day percentiles) and update it incrementally with each run. The scoring logic would then become a cheap lookup and a simple arithmetic operation.
That approach would also pave the way for moving from daily to near-real-time scoring, which is often where the actual security value is for these agent actions. A daily batch job can only tell you yesterday's problems.
--perf
Agree in principle, but your "mute for 24 hours" is just shifting the problem. It's a one-size-fits-all grace period.
What about the analyst on vacation for two weeks? Or the agent that's genuinely compromised on day 2? The mute status needs context, like tying it to the specific risk factor that triggered the alert - a location mute might last 24h, but a volume deviation mute should be shorter.
Your feedback loop is too slow. Weekly manual adjustments mean your scoring is wrong for days after a major incident. That's a lot of noise or missed signals.
Show me the methodology.
Good point on the mute needing context. A fixed 24-hour window for everything would drive me nuts.
I've seen teams attach a "snooze reason" to these mutes, pulled from a short list tied to the risk factor. So an analyst picks "agent on travel" from a dropdown and it auto-applies a 72-hour location mute, but a "volume spike-review needed" mute might default to 4 hours. It creates that granularity without overcomplicating the click.
But the slow feedback loop is the real killer. Waiting a week to tune the scoring means you're just documenting failure, not preventing it.
null
Love the choice of dbt for the scoring logic. It keeps everything version-controlled and testable.
That `volume_deviation_score` calculation, though - how are you defining the baseline? Are you comparing against a rolling average, or a fixed historical window? I tried something similar and the initial window of sparse data gave us crazy false positives. We ended up using a 7-day trailing median which smoothed things out a lot.
Also, have you thought about adding a simple coefficient table for those 0.5, 0.3, 0.2 weights? Putting them in a config YAML or a tiny seed table makes tuning them way easier without touching the core model.