571 lines
21 KiB
Python
571 lines
21 KiB
Python
import hashlib
|
|
import hmac
|
|
import json
|
|
import os
|
|
from datetime import datetime, timezone
|
|
|
|
import psycopg
|
|
from flask import Flask, abort, jsonify, redirect, render_template, request, url_for
|
|
|
|
|
|
app = Flask(__name__)
|
|
|
|
CURRENT_EVENT_HOURS = 24
|
|
STALE_REPORT_HOURS = 36
|
|
|
|
|
|
def db_connection():
|
|
return psycopg.connect(
|
|
host=os.environ["DB_HOST"],
|
|
port=os.getenv("DB_PORT", "5432"),
|
|
dbname=os.environ["DB_NAME"],
|
|
user=os.environ["DB_USER"],
|
|
password=os.environ["DB_PASSWORD"],
|
|
connect_timeout=5,
|
|
)
|
|
|
|
|
|
def csrf_token():
|
|
secret = os.environ["DASHBOARD_CSRF_SECRET"].encode("utf-8")
|
|
return hmac.new(secret, b"recipient-rules", hashlib.sha256).hexdigest()
|
|
|
|
|
|
def require_csrf():
|
|
supplied = request.form.get("csrf_token", "")
|
|
if not hmac.compare_digest(supplied, csrf_token()):
|
|
abort(400)
|
|
|
|
|
|
def event_metadata(payload):
|
|
latest_event = None
|
|
for event in (payload or {}).get("Events", []):
|
|
value = event.get("Timestamp")
|
|
if not value:
|
|
continue
|
|
try:
|
|
timestamp = datetime.fromisoformat(value.replace("Z", "+00:00"))
|
|
except ValueError:
|
|
continue
|
|
if timestamp.tzinfo is None:
|
|
timestamp = timestamp.replace(tzinfo=timezone.utc)
|
|
if latest_event is None or timestamp > latest_event:
|
|
latest_event = timestamp
|
|
|
|
if latest_event is None:
|
|
return {"is_current": False, "label": "keine Ereignisse", "timestamp": None}
|
|
|
|
age_seconds = max(0, int((datetime.now(timezone.utc) - latest_event.astimezone(timezone.utc)).total_seconds()))
|
|
if age_seconds < 3600:
|
|
age_label = f"vor {max(1, age_seconds // 60)} Min."
|
|
elif age_seconds < 86400:
|
|
age_label = f"vor {age_seconds // 3600} Std."
|
|
else:
|
|
age_label = f"vor {age_seconds // 86400} Tg."
|
|
|
|
return {
|
|
"is_current": age_seconds <= CURRENT_EVENT_HOURS * 3600,
|
|
"label": age_label,
|
|
"timestamp": latest_event,
|
|
}
|
|
|
|
|
|
def payload_value(payload, *names, default=None):
|
|
if not isinstance(payload, dict):
|
|
return default
|
|
for name in names:
|
|
if name in payload:
|
|
return payload[name]
|
|
return default
|
|
|
|
|
|
@app.get("/")
|
|
def overview():
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute("SELECT * FROM ocsentinel.organization_summary")
|
|
summary = cursor.fetchone()
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT machine_name, organization_name, received_at, alert_state,
|
|
total_events, unique_ip_count, cve_total, cve_critical, payload
|
|
FROM (
|
|
SELECT machine_name, received_at, alert_state, total_events,
|
|
unique_ip_count, cve_total, cve_critical,
|
|
payload #>> '{NinjaOne,OrganizationName}' AS organization_name,
|
|
payload
|
|
FROM ocsentinel.current_device_status
|
|
) AS status
|
|
ORDER BY received_at DESC NULLS LAST
|
|
LIMIT 100
|
|
"""
|
|
)
|
|
reports = cursor.fetchall()
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT count(*) AS known_devices,
|
|
count(*) FILTER (WHERE received_at >= now() - interval '36 hours') AS reporting_devices,
|
|
count(*) FILTER (WHERE received_at IS NULL OR received_at < now() - interval '36 hours') AS stale_devices
|
|
FROM ocsentinel.current_device_status
|
|
"""
|
|
)
|
|
coverage = cursor.fetchone()
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT machine_name, alert_state, total_events, unique_ip_count,
|
|
received_at, payload
|
|
FROM ocsentinel.current_device_status
|
|
WHERE alert_state IN ('warning', 'critical')
|
|
ORDER BY CASE alert_state WHEN 'critical' THEN 0 ELSE 1 END, received_at DESC
|
|
"""
|
|
)
|
|
alerts = cursor.fetchall()
|
|
|
|
cursor.execute(
|
|
"""
|
|
WITH latest AS (
|
|
SELECT DISTINCT ON (d.machine_name_key, date_trunc('day', r.received_at))
|
|
date_trunc('day', r.received_at)::date AS day,
|
|
r.alert_state,
|
|
r.total_events
|
|
FROM ocsentinel.scan_report AS r
|
|
JOIN ocsentinel.device AS d ON d.id = r.device_id
|
|
WHERE r.received_at >= now() - interval '14 days'
|
|
ORDER BY d.machine_name_key, date_trunc('day', r.received_at), r.received_at DESC
|
|
)
|
|
SELECT day,
|
|
count(*) FILTER (WHERE alert_state = 'warning') AS warning_count,
|
|
count(*) FILTER (WHERE alert_state = 'critical') AS critical_count,
|
|
coalesce(sum(total_events), 0) AS event_count
|
|
FROM latest
|
|
GROUP BY day
|
|
ORDER BY day
|
|
"""
|
|
)
|
|
trend = cursor.fetchall()
|
|
|
|
cursor.execute(
|
|
"""
|
|
SELECT coalesce(payload #>> '{NinjaOne,OrganizationId}', 'unknown') AS organization_id,
|
|
coalesce(nullif(payload #>> '{NinjaOne,OrganizationName}', ''), 'Organisation unbekannt') AS organization_name,
|
|
count(*) AS device_count,
|
|
count(*) FILTER (WHERE alert_state = 'warning') AS warning_count,
|
|
count(*) FILTER (WHERE alert_state = 'critical') AS critical_count,
|
|
max(received_at) AS last_received_at
|
|
FROM ocsentinel.current_device_status
|
|
GROUP BY 1, 2
|
|
ORDER BY critical_count DESC, warning_count DESC, organization_name
|
|
"""
|
|
)
|
|
organizations = cursor.fetchall()
|
|
|
|
report_rows = []
|
|
for row in reports:
|
|
event = event_metadata(row[8])
|
|
report_rows.append(
|
|
{
|
|
"machine_name": row[0],
|
|
"organization_name": row[1],
|
|
"received_at": row[2],
|
|
"alert_state": row[3],
|
|
"total_events": row[4],
|
|
"unique_ip_count": row[5],
|
|
"event": event,
|
|
}
|
|
)
|
|
|
|
alert_rows = []
|
|
for row in alerts:
|
|
event = event_metadata(row[5])
|
|
alert_rows.append(
|
|
{
|
|
"machine_name": row[0],
|
|
"alert_state": row[1],
|
|
"total_events": row[2],
|
|
"unique_ip_count": row[3],
|
|
"received_at": row[4],
|
|
"event": event,
|
|
}
|
|
)
|
|
|
|
trend_rows = [
|
|
{
|
|
"day": row[0],
|
|
"warning_count": row[1],
|
|
"critical_count": row[2],
|
|
"event_count": row[3],
|
|
}
|
|
for row in trend
|
|
]
|
|
trend_max = max([row["event_count"] for row in trend_rows] or [1])
|
|
organization_rows = [
|
|
{
|
|
"id": row[0],
|
|
"name": row[1],
|
|
"device_count": row[2],
|
|
"warning_count": row[3],
|
|
"critical_count": row[4],
|
|
"last_received_at": row[5],
|
|
}
|
|
for row in organizations
|
|
]
|
|
|
|
return render_template(
|
|
"overview.html",
|
|
summary=summary,
|
|
coverage=coverage,
|
|
reports=report_rows,
|
|
alerts=alert_rows,
|
|
current_alert_count=sum(alert["event"]["is_current"] for alert in alert_rows),
|
|
trend=trend_rows,
|
|
trend_max=trend_max,
|
|
organizations=organization_rows,
|
|
)
|
|
|
|
|
|
@app.get("/organizations/<organization_id>")
|
|
def organization(organization_id):
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
SELECT machine_name, received_at, alert_state, total_events, unique_ip_count,
|
|
cve_critical, payload
|
|
FROM ocsentinel.current_device_status
|
|
WHERE coalesce(payload #>> '{NinjaOne,OrganizationId}', 'unknown') = %s
|
|
ORDER BY CASE alert_state WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
|
|
machine_name
|
|
""",
|
|
(organization_id,),
|
|
)
|
|
devices = cursor.fetchall()
|
|
|
|
if not devices:
|
|
abort(404)
|
|
|
|
organization_name = (devices[0][6] or {}).get("NinjaOne", {}).get("OrganizationName") or "Organisation unbekannt"
|
|
return render_template(
|
|
"organization.html",
|
|
organization_id=organization_id,
|
|
organization_name=organization_name,
|
|
devices=devices,
|
|
critical_count=sum(row[2] == "critical" for row in devices),
|
|
warning_count=sum(row[2] == "warning" for row in devices),
|
|
)
|
|
|
|
|
|
def load_network_flows(days=14):
|
|
days = max(1, min(days, 90))
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
SELECT machine_name, received_at, payload
|
|
FROM ocsentinel.current_device_status
|
|
WHERE received_at >= now() - (%s * interval '1 day')
|
|
""",
|
|
(days,),
|
|
)
|
|
reports = cursor.fetchall()
|
|
|
|
flows = {}
|
|
for machine_name, received_at, payload in reports:
|
|
ninja_context = (payload or {}).get("NinjaOne") or {}
|
|
organization_id = str(ninja_context.get("OrganizationId") or "unknown")
|
|
organization_name = ninja_context.get("OrganizationName") or "Organisation unbekannt"
|
|
for event in (payload or {}).get("Events", []):
|
|
source_ip = event.get("SourceIp") or ""
|
|
if not source_ip or source_ip in {"-", "127.0.0.1", "::1"}:
|
|
continue
|
|
account = event.get("Username") or "[unbekannt]"
|
|
target = event.get("Target") or "Anmeldung"
|
|
key = (source_ip, machine_name, account, target, organization_id)
|
|
entry = flows.setdefault(
|
|
key,
|
|
{
|
|
"source_ip": source_ip,
|
|
"machine_name": machine_name,
|
|
"account": account,
|
|
"target": target,
|
|
"organization_id": organization_id,
|
|
"organization_name": organization_name,
|
|
"count": 0,
|
|
"last_seen": received_at,
|
|
},
|
|
)
|
|
entry["count"] += 1
|
|
timestamp = event.get("Timestamp")
|
|
if timestamp and (entry["last_seen"] is None or str(timestamp) > str(entry["last_seen"])):
|
|
entry["last_seen"] = timestamp
|
|
|
|
flow_rows = sorted(flows.values(), key=lambda entry: (entry["count"], str(entry["last_seen"])), reverse=True)[:60]
|
|
max_count = max([entry["count"] for entry in flow_rows] or [1])
|
|
source_count = len({entry["source_ip"] for entry in flow_rows})
|
|
target_count = len({entry["machine_name"] for entry in flow_rows})
|
|
return flow_rows, max_count, source_count, target_count
|
|
|
|
|
|
@app.get("/network")
|
|
def network():
|
|
days = request.args.get("days", 14, type=int)
|
|
flows, _, source_count, target_count = load_network_flows(days)
|
|
return render_template(
|
|
"network.html",
|
|
source_count=source_count,
|
|
target_count=target_count,
|
|
total_events=sum(entry["count"] for entry in flows),
|
|
path_count=len(flows),
|
|
days=max(1, min(days, 90)),
|
|
organizations=sorted({(entry["organization_id"], entry["organization_name"]) for entry in flows}, key=lambda item: item[1]),
|
|
event_types=sorted({entry["target"] for entry in flows}),
|
|
)
|
|
|
|
|
|
@app.get("/api/network")
|
|
def network_api():
|
|
days = request.args.get("days", 14, type=int)
|
|
flows, max_count, source_count, target_count = load_network_flows(days)
|
|
nodes = {}
|
|
edges = []
|
|
for index, flow in enumerate(flows):
|
|
source_id = f"source:{flow['source_ip']}"
|
|
target_id = f"target:{flow['machine_name']}"
|
|
nodes[source_id] = {"data": {"id": source_id, "label": flow["source_ip"], "kind": "source"}}
|
|
nodes[target_id] = {"data": {"id": target_id, "label": flow["machine_name"], "kind": "target"}}
|
|
edges.append(
|
|
{
|
|
"data": {
|
|
"id": f"flow:{index}",
|
|
"source": source_id,
|
|
"target": target_id,
|
|
"count": flow["count"],
|
|
"account": flow["account"],
|
|
"event_type": flow["target"],
|
|
"last_seen": str(flow["last_seen"] or "-"),
|
|
"machine_name": flow["machine_name"],
|
|
"organization_id": flow["organization_id"],
|
|
"organization_name": flow["organization_name"],
|
|
}
|
|
}
|
|
)
|
|
return jsonify(
|
|
{
|
|
"elements": {"nodes": list(nodes.values()), "edges": edges},
|
|
"max_count": max_count,
|
|
"source_count": source_count,
|
|
"target_count": target_count,
|
|
}
|
|
)
|
|
|
|
|
|
@app.get("/device/<machine_name>")
|
|
def device(machine_name):
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
SELECT machine_name, first_seen_at, last_seen_at, last_client_version,
|
|
generated_at_utc, received_at, alert_state, base_alert_state,
|
|
total_events, unique_ip_count, cve_total, cve_critical, payload
|
|
FROM ocsentinel.current_device_status
|
|
WHERE machine_name = %s
|
|
""",
|
|
(machine_name,),
|
|
)
|
|
report = cursor.fetchone()
|
|
|
|
if report is None:
|
|
abort(404)
|
|
|
|
payload = report[12]
|
|
event_groups = {}
|
|
for entry in payload.get("Events") or payload.get("events") or []:
|
|
event_type = entry.get("Target") or entry.get("target") or "Sicherheitsereignis"
|
|
account = entry.get("Username") or entry.get("username") or "-"
|
|
source_ip = entry.get("SourceIp") or entry.get("sourceIp") or "-"
|
|
key = (event_type, account, source_ip)
|
|
group = event_groups.setdefault(
|
|
key,
|
|
{"type": event_type, "account": account, "source_ip": source_ip, "count": 0, "latest": "-"},
|
|
)
|
|
group["count"] += 1
|
|
timestamp = entry.get("Timestamp") or entry.get("timestamp") or "-"
|
|
if timestamp > group["latest"]:
|
|
group["latest"] = timestamp
|
|
|
|
security_events = sorted(
|
|
event_groups.values(),
|
|
key=lambda entry: (entry["latest"], entry["count"]),
|
|
reverse=True,
|
|
)[:25]
|
|
ransomware_raw = payload.get("RansomwareBeta") or payload.get("ransomwareBeta") or {}
|
|
sensor_labels = {
|
|
"security-process-4688": "Prozessstarts (Security 4688)",
|
|
"powershell-script-block-4104": "PowerShell-Skriptblöcke (4104)",
|
|
"sysmon-process-1": "Sysmon-Prozesse (1)",
|
|
"security-file-audit-4663": "Datei-Auditing (4663)",
|
|
}
|
|
ransomware_sensors = [
|
|
{
|
|
"label": sensor_labels.get(payload_value(sensor, "Name", "name"), payload_value(sensor, "Name", "name", default="Unbekannter Sensor")),
|
|
"enabled": payload_value(sensor, "Enabled", "enabled", default=False),
|
|
"available": payload_value(sensor, "Available", "available", default=False),
|
|
"state": payload_value(sensor, "State", "state", default="unknown"),
|
|
"event_count": payload_value(sensor, "EventCount", "eventCount", default=0),
|
|
}
|
|
for sensor in payload_value(ransomware_raw, "Sensors", "sensors", default=[])
|
|
]
|
|
ransomware_beta = {
|
|
"enabled": payload_value(ransomware_raw, "Enabled", "enabled", default=False),
|
|
"state": payload_value(ransomware_raw, "State", "state", default="disabled"),
|
|
"reason": payload_value(ransomware_raw, "Reason", "reason", default="Keine Ransomware-Beta-Daten verfuegbar."),
|
|
"signals": [
|
|
{
|
|
"timestamp": payload_value(signal, "Timestamp", "timestamp", default="-"),
|
|
"category": payload_value(signal, "Category", "category", default="Signal"),
|
|
"process": payload_value(signal, "Process", "process", default="-"),
|
|
"source": payload_value(signal, "Source", "source", default="-"),
|
|
"confidence": payload_value(signal, "Confidence", "confidence", default="low"),
|
|
}
|
|
for signal in payload_value(ransomware_raw, "Signals", "signals", default=[])
|
|
],
|
|
"smbSessions": [
|
|
{
|
|
"clientComputerName": payload_value(session, "ClientComputerName", "clientComputerName", default="-"),
|
|
"clientUserName": payload_value(session, "ClientUserName", "clientUserName", default="-"),
|
|
"openFileCount": payload_value(session, "OpenFileCount", "openFileCount", default=0),
|
|
"sessionId": payload_value(session, "SessionId", "sessionId", default="-"),
|
|
}
|
|
for session in payload_value(ransomware_raw, "SmbSessions", "smbSessions", default=[])
|
|
],
|
|
}
|
|
return render_template(
|
|
"device.html",
|
|
report=report,
|
|
event=event_metadata(payload),
|
|
payload=payload,
|
|
security_events=security_events,
|
|
ransomware_beta=ransomware_beta,
|
|
ransomware_sensors=ransomware_sensors,
|
|
payload_pretty=json.dumps(payload, indent=2, ensure_ascii=False),
|
|
)
|
|
|
|
|
|
@app.get("/reports")
|
|
def reports():
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
SELECT id, organization_name, period_start_utc, period_end_utc,
|
|
generated_at, device_count, warning_count, critical_count,
|
|
total_events
|
|
FROM ocsentinel.weekly_organization_report
|
|
ORDER BY period_end_utc DESC, organization_name
|
|
"""
|
|
)
|
|
weekly_reports = cursor.fetchall()
|
|
|
|
return render_template("reports.html", reports=weekly_reports)
|
|
|
|
|
|
@app.get("/reports/<int:report_id>")
|
|
def weekly_report(report_id):
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
SELECT organization_name, period_start_utc, period_end_utc,
|
|
generated_at, report_html
|
|
FROM ocsentinel.weekly_organization_report
|
|
WHERE id = %s
|
|
""",
|
|
(report_id,),
|
|
)
|
|
report = cursor.fetchone()
|
|
|
|
if report is None:
|
|
abort(404)
|
|
|
|
return render_template("weekly_report.html", report=report)
|
|
|
|
|
|
@app.get("/recipients")
|
|
def recipients():
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
SELECT id, organization_id, organization_name, recipient_email, enabled
|
|
FROM ocsentinel.organization_report_recipient
|
|
ORDER BY organization_id = '*', organization_name, recipient_email
|
|
"""
|
|
)
|
|
rules = cursor.fetchall()
|
|
cursor.execute(
|
|
"""
|
|
SELECT DISTINCT payload #>> '{NinjaOne,OrganizationId}',
|
|
payload #>> '{NinjaOne,OrganizationName}'
|
|
FROM ocsentinel.current_device_status
|
|
WHERE coalesce(payload #>> '{NinjaOne,OrganizationId}', '') <> ''
|
|
ORDER BY 2
|
|
"""
|
|
)
|
|
organizations = cursor.fetchall()
|
|
|
|
return render_template("recipients.html", rules=rules, organizations=organizations, csrf_token=csrf_token())
|
|
|
|
|
|
@app.post("/recipients")
|
|
def add_recipient():
|
|
require_csrf()
|
|
organization_id = request.form.get("organization_id", "").strip()
|
|
organization_name = request.form.get("organization_name", "").strip()
|
|
recipient_email = request.form.get("recipient_email", "").strip().lower()
|
|
if not organization_id or not organization_name or "@" not in recipient_email:
|
|
abort(400)
|
|
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"""
|
|
INSERT INTO ocsentinel.organization_report_recipient
|
|
(organization_id, organization_name, recipient_email)
|
|
VALUES (%s, %s, %s)
|
|
ON CONFLICT (organization_id, recipient_email) DO NOTHING
|
|
""",
|
|
(organization_id, organization_name, recipient_email),
|
|
)
|
|
connection.commit()
|
|
|
|
return redirect(url_for("recipients"))
|
|
|
|
|
|
@app.post("/recipients/<int:rule_id>/toggle")
|
|
def toggle_recipient(rule_id):
|
|
require_csrf()
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute(
|
|
"UPDATE ocsentinel.organization_report_recipient SET enabled = NOT enabled WHERE id = %s",
|
|
(rule_id,),
|
|
)
|
|
connection.commit()
|
|
return redirect(url_for("recipients"))
|
|
|
|
|
|
@app.post("/recipients/<int:rule_id>/delete")
|
|
def delete_recipient(rule_id):
|
|
require_csrf()
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute("DELETE FROM ocsentinel.organization_report_recipient WHERE id = %s", (rule_id,))
|
|
connection.commit()
|
|
return redirect(url_for("recipients"))
|
|
|
|
|
|
@app.get("/healthz")
|
|
def healthz():
|
|
try:
|
|
with db_connection() as connection, connection.cursor() as cursor:
|
|
cursor.execute("SELECT 1")
|
|
return {"status": "ok"}
|
|
except Exception:
|
|
return {"status": "unavailable"}, 503
|
|
|
|
|
|
if __name__ == "__main__":
|
|
app.run(host="0.0.0.0", port=8080)
|