Implemented the baseline/noise and SQLite growth improvements.
This commit is contained in:
larssand
@@ -15,6 +15,12 @@ from .detectors import DETECTOR_MINIMUMS, event_detector_categories
|
||||
from .normalization import canonical_value
|
||||
from .query_details import event_query_details
|
||||
|
||||
DEFAULT_RETENTION_DAYS = 14
|
||||
DEFAULT_VALUE_RETENTION_DAYS = 7
|
||||
DEFAULT_MAX_VALUES_PER_FIELD = 2000
|
||||
MIN_REPORTED_DEVIATION_SCORE = 15
|
||||
MAX_RARE_VALUES_PER_ENTITY = 5
|
||||
|
||||
|
||||
def _number(value: str | None) -> int:
|
||||
try:
|
||||
@@ -128,6 +134,27 @@ class BaselineStore:
|
||||
);
|
||||
"""
|
||||
)
|
||||
self._migrate(connection)
|
||||
|
||||
def _migrate(self, connection: sqlite3.Connection) -> None:
|
||||
columns = {row[1] for row in connection.execute("pragma table_info(seen_events)").fetchall()}
|
||||
if "first_seen" not in columns:
|
||||
connection.execute("alter table seen_events add column first_seen integer not null default 0")
|
||||
columns = {row[1] for row in connection.execute("pragma table_info(profile_seen_events)").fetchall()}
|
||||
if "first_seen" not in columns:
|
||||
connection.execute("alter table profile_seen_events add column first_seen integer not null default 0")
|
||||
columns = {row[1] for row in connection.execute("pragma table_info(profile_values)").fetchall()}
|
||||
if "last_seen" not in columns:
|
||||
connection.execute("alter table profile_values add column last_seen integer not null default 0")
|
||||
connection.executescript(
|
||||
"""
|
||||
create index if not exists idx_profile_buckets_lookup on profile_buckets(stream_id, entity, field, bucket_start);
|
||||
create index if not exists idx_profile_temporal_lookup on profile_temporal_buckets(stream_id, entity, field, weekday, hour, bucket_start);
|
||||
create index if not exists idx_profile_detector_lookup on profile_detector_buckets(stream_id, entity, detector, bucket_start);
|
||||
create index if not exists idx_profile_detector_temporal_lookup on profile_detector_temporal_buckets(stream_id, entity, detector, weekday, hour, bucket_start);
|
||||
create index if not exists idx_profile_values_lookup on profile_values(stream_id, entity, field, seen_count);
|
||||
"""
|
||||
)
|
||||
|
||||
def _connect(self) -> sqlite3.Connection:
|
||||
return sqlite3.connect(self.path)
|
||||
@@ -141,7 +168,7 @@ class BaselineStore:
|
||||
if not event.src_ip:
|
||||
continue
|
||||
fingerprint = hashlib.sha256(event.raw.encode("utf-8", errors="replace")).hexdigest()
|
||||
if connection.execute("insert or ignore into seen_events values (?)", (fingerprint,)).rowcount != 1:
|
||||
if connection.execute("insert or ignore into seen_events(fingerprint, first_seen) values (?, ?)", (fingerprint, observed_at)).rowcount != 1:
|
||||
continue
|
||||
bucket = observed_at - (observed_at % self.bucket_seconds)
|
||||
values = pending[(event.src_ip, bucket)]
|
||||
@@ -182,7 +209,7 @@ class BaselineStore:
|
||||
if not profile:
|
||||
continue
|
||||
fingerprint = hashlib.sha256(f"{stream_id}|{event.raw}".encode("utf-8", errors="replace")).hexdigest()
|
||||
if connection.execute("insert or ignore into profile_seen_events values (?)", (fingerprint,)).rowcount != 1:
|
||||
if connection.execute("insert or ignore into profile_seen_events(fingerprint, first_seen) values (?, ?)", (fingerprint, observed_at)).rowcount != 1:
|
||||
continue
|
||||
entities = profile_entities(event, profile)
|
||||
if not entities:
|
||||
@@ -217,8 +244,8 @@ class BaselineStore:
|
||||
connection.execute("""insert into profile_temporal_buckets values (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||||
on conflict(stream_id, entity, field, weekday, hour, bucket_start) do update set events=events+excluded.events,numeric_sum=numeric_sum+excluded.numeric_sum,numeric_sum_squares=numeric_sum_squares+excluded.numeric_sum_squares""", (stream_id, entity, field, weekday, hour, bucket, *values))
|
||||
for key, count in pending_values.items():
|
||||
connection.execute("""insert into profile_values values (?, ?, ?, ?, ?)
|
||||
on conflict(stream_id, entity, field, value) do update set seen_count=seen_count+excluded.seen_count""", (*key, count))
|
||||
connection.execute("""insert into profile_values values (?, ?, ?, ?, ?, ?)
|
||||
on conflict(stream_id, entity, field, value) do update set seen_count=seen_count+excluded.seen_count,last_seen=excluded.last_seen""", (*key, count, observed_at))
|
||||
for (stream_id, entity, detector, bucket), count in detector_pending.items():
|
||||
connection.execute("""insert into profile_detector_buckets values (?, ?, ?, ?, ?)
|
||||
on conflict(stream_id, entity, detector, bucket_start) do update set events=events+excluded.events""", (stream_id, entity, detector, bucket, count))
|
||||
@@ -276,7 +303,8 @@ class BaselineStore:
|
||||
confidence = _baseline_confidence(len(rows), temporal=temporal)
|
||||
base_score = 18 if confidence == "high" else 15 if confidence == "medium" else 10
|
||||
score, weight = _weighted_score(base_score, profile, field, "numeric_baseline")
|
||||
output[entity].append({"detector": "numeric_baseline", "field": field, "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": confidence, "baseline_samples": len(rows), "baseline_scope": baseline_scope, "reason": reason, "current": round(current_value, 2), "baseline": round(mean(history), 2), "sample_values": sample_values, "sample_events": evidence_events})
|
||||
if score >= MIN_REPORTED_DEVIATION_SCORE:
|
||||
output[entity].append({"detector": "numeric_baseline", "field": field, "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": confidence, "baseline_samples": len(rows), "baseline_scope": baseline_scope, "reason": reason, "current": round(current_value, 2), "baseline": round(mean(history), 2), "sample_values": sample_values, "sample_events": evidence_events})
|
||||
|
||||
# Event-rate burst is calculated once per stream/entity, rather than once per selected field.
|
||||
for (stream, entity), matching in entity_events.items():
|
||||
@@ -305,7 +333,8 @@ class BaselineStore:
|
||||
base_score = min(30, (15 if confidence == "high" else 12 if confidence == "medium" else 8) + int(z_score))
|
||||
score, weight = _weighted_score(base_score, profile, "event_rate", "event_rate_burst")
|
||||
samples = [_sample_event(event) for event in matching[:5]]
|
||||
output[entity].append({"detector": "event_rate_burst", "field": "event_rate", "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": confidence, "baseline_samples": len(rows), "baseline_scope": baseline_scope, "reason": f"event rate burst above its {baseline_scope} baseline (z={z_score:.1f})", "current": current_value, "baseline": round(mean(history), 2), "sample_values": [], "sample_events": samples})
|
||||
if score >= MIN_REPORTED_DEVIATION_SCORE:
|
||||
output[entity].append({"detector": "event_rate_burst", "field": "event_rate", "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": confidence, "baseline_samples": len(rows), "baseline_scope": baseline_scope, "reason": f"event rate burst above its {baseline_scope} baseline (z={z_score:.1f})", "current": current_value, "baseline": round(mean(history), 2), "sample_values": [], "sample_events": samples})
|
||||
|
||||
for (stream, entity, detector), current_value in detector_current.items():
|
||||
profile = profiles.get(stream)
|
||||
@@ -336,8 +365,10 @@ class BaselineStore:
|
||||
base_score = min(35, (18 if detector == "auth_failure" else 15 if detector == "deny_action" else 12) + int(z_score))
|
||||
score, weight = _weighted_score(base_score, profile, detector, f"{detector}_burst")
|
||||
samples = [_sample_event(event, detector) for event in matching[:5]]
|
||||
output[entity].append({"detector": f"{detector}_burst", "field": detector, "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": confidence, "baseline_samples": len(rows), "baseline_scope": baseline_scope, "reason": f"{detector.replace('_', ' ')} burst above its {baseline_scope} baseline (z={z_score:.1f}, minimum={minimum})", "current": current_value, "baseline": round(mean(history), 2), "sample_values": [detector], "sample_events": samples})
|
||||
if score >= MIN_REPORTED_DEVIATION_SCORE:
|
||||
output[entity].append({"detector": f"{detector}_burst", "field": detector, "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": confidence, "baseline_samples": len(rows), "baseline_scope": baseline_scope, "reason": f"{detector.replace('_', ' ')} burst above its {baseline_scope} baseline (z={z_score:.1f}, minimum={minimum})", "current": current_value, "baseline": round(mean(history), 2), "sample_values": [detector], "sample_events": samples})
|
||||
# Detect selected categorical values that have not appeared for this entity in prior data.
|
||||
rare_counts: Counter[tuple[str, str]] = Counter()
|
||||
for event in events:
|
||||
profile = profiles.get(event.fields.get("fgai_stream_id", ""))
|
||||
if not profile:
|
||||
@@ -355,13 +386,69 @@ class BaselineStore:
|
||||
known = connection.execute("select seen_count from profile_values where stream_id=? and entity=? and field=? and value=?", (stream, entity, field, value)).fetchone()
|
||||
known_total = connection.execute("select coalesce(sum(seen_count), 0) from profile_values where stream_id=? and entity=? and field=?", (stream, entity, field)).fetchone()[0]
|
||||
if known is None and known_total >= 30:
|
||||
if rare_counts[(stream, entity)] >= MAX_RARE_VALUES_PER_ENTITY:
|
||||
continue
|
||||
samples = [item for item in events if item.fields.get("fgai_stream_id") == stream and entity in profile_entities(item, profile) and item.fields.get(field) == value]
|
||||
score, weight = _weighted_score(12, profile, field, "rare_value")
|
||||
evidence = {"detector": "rare_value", "field": field, "stream_id": stream, "score": score, "base_score": 12, "weight": weight, "confidence": "medium", "baseline_samples": int(known_total), "baseline_scope": "known field values", "reason": f"new {field} value for this entity", "value": value, "sample_values": [value], "sample_events": [_sample_event(item, value) for item in samples[:5]]}
|
||||
base_score = 18
|
||||
score, weight = _weighted_score(base_score, profile, field, "rare_value")
|
||||
if score < MIN_REPORTED_DEVIATION_SCORE:
|
||||
continue
|
||||
evidence = {"detector": "rare_value", "field": field, "stream_id": stream, "score": score, "base_score": base_score, "weight": weight, "confidence": "medium", "baseline_samples": int(known_total), "baseline_scope": "known field values", "reason": f"new {field} value for this entity", "value": value, "sample_values": [value], "sample_events": [_sample_event(item, value) for item in samples[:5]]}
|
||||
if evidence not in output[entity]:
|
||||
output[entity].append(evidence)
|
||||
rare_counts[(stream, entity)] += 1
|
||||
return output
|
||||
|
||||
def maintenance(
|
||||
self,
|
||||
*,
|
||||
retention_days: int = DEFAULT_RETENTION_DAYS,
|
||||
value_retention_days: int = DEFAULT_VALUE_RETENTION_DAYS,
|
||||
max_values_per_field: int = DEFAULT_MAX_VALUES_PER_FIELD,
|
||||
vacuum: bool = False,
|
||||
) -> dict[str, object]:
|
||||
now = int(time.time())
|
||||
bucket_cutoff = now - max(1, retention_days) * 86400
|
||||
value_cutoff = now - max(1, value_retention_days) * 86400
|
||||
deleted: dict[str, int] = {}
|
||||
with self._connect() as connection:
|
||||
for table in (
|
||||
"source_buckets",
|
||||
"profile_buckets",
|
||||
"profile_temporal_buckets",
|
||||
"profile_detector_buckets",
|
||||
"profile_detector_temporal_buckets",
|
||||
):
|
||||
deleted[table] = connection.execute(f"delete from {table} where bucket_start < ?", (bucket_cutoff,)).rowcount
|
||||
deleted["seen_events"] = connection.execute("delete from seen_events where first_seen > 0 and first_seen < ?", (bucket_cutoff,)).rowcount
|
||||
deleted["profile_seen_events"] = connection.execute("delete from profile_seen_events where first_seen > 0 and first_seen < ?", (bucket_cutoff,)).rowcount
|
||||
deleted["profile_values_stale_low_count"] = connection.execute("delete from profile_values where last_seen > 0 and last_seen < ? and seen_count <= 1", (value_cutoff,)).rowcount
|
||||
if max_values_per_field > 0:
|
||||
rows = connection.execute("select stream_id, entity, field, count(*) from profile_values group by stream_id, entity, field having count(*) > ?", (max_values_per_field,)).fetchall()
|
||||
trimmed = 0
|
||||
for stream_id, entity, field, _count in rows:
|
||||
keep = {
|
||||
row[0]
|
||||
for row in connection.execute(
|
||||
"""select value from profile_values
|
||||
where stream_id=? and entity=? and field=?
|
||||
order by seen_count desc, last_seen desc limit ?""",
|
||||
(stream_id, entity, field, max_values_per_field),
|
||||
).fetchall()
|
||||
}
|
||||
placeholders = ",".join("?" for _ in keep)
|
||||
if keep:
|
||||
trimmed += connection.execute(
|
||||
f"delete from profile_values where stream_id=? and entity=? and field=? and value not in ({placeholders})",
|
||||
(stream_id, entity, field, *keep),
|
||||
).rowcount
|
||||
deleted["profile_values_capped"] = trimmed
|
||||
connection.execute("pragma optimize")
|
||||
if vacuum:
|
||||
with self._connect() as connection:
|
||||
connection.execute("vacuum")
|
||||
return {"retention_days": retention_days, "value_retention_days": value_retention_days, "max_values_per_field": max_values_per_field, "vacuum": vacuum, "deleted": deleted, "size_bytes": self.path.stat().st_size if self.path.exists() else 0}
|
||||
|
||||
def profile_readiness(self, profiles: dict[str, object]) -> list[dict[str, object]]:
|
||||
rows: list[dict[str, object]] = []
|
||||
with self._connect() as connection:
|
||||
|
||||
Reference in New Issue
Block a user