2159 lines
44 KiB
SQL
2159 lines
44 KiB
SQL
-- SIEM-lite vollständiges MariaDB-kompatibles Datenbankschema
|
|
-- Stand: Partitionierung event_logs/event_logs_raw, 3h-Partitionen, Raw-XML-Auslagerung,
|
|
-- Baseline-Buckets, UEBA, SOC/Risk, UI-Bewertungen.
|
|
--
|
|
-- Getestet/ausgelegt für MariaDB/MySQL InnoDB.
|
|
--
|
|
-- WICHTIG:
|
|
-- 1. Dieses Script löscht bestehende Tabellen.
|
|
-- 2. Partitionierung erfolgt nach DATETIME(6)-Spalte ts.
|
|
-- 3. MariaDB erlaubt ON UPDATE UTC_TIMESTAMP(6) nicht zuverlässig.
|
|
-- Deshalb nutzt dieses Schema DEFAULT CURRENT_TIMESTAMP(6) / ON UPDATE CURRENT_TIMESTAMP(6).
|
|
-- 4. Stelle für echte UTC-Speicherung im Go-DSN zusätzlich die DB-Session auf UTC:
|
|
-- ?parseTime=true&loc=UTC&time_zone=%27%2B00%3A00%27
|
|
-- oder setze beim Verbindungsaufbau SET time_zone = '+00:00'.
|
|
-- 5. Spalten in zusammengesetzten Keys wurden auf VARCHAR(191) begrenzt,
|
|
-- damit utf8mb4 und 3072-Byte-Key-Limit keine Fehler erzeugen.
|
|
|
|
SET NAMES utf8mb4;
|
|
SET time_zone = '+00:00';
|
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
DROP PROCEDURE IF EXISTS ensure_siem_partitions;
|
|
|
|
DROP TABLE IF EXISTS event_count_buckets;
|
|
DROP TABLE IF EXISTS ueba_context_buckets;
|
|
DROP TABLE IF EXISTS host_risk_scores;
|
|
DROP TABLE IF EXISTS baseline_exclusions;
|
|
DROP TABLE IF EXISTS baseline_event_stats;
|
|
DROP TABLE IF EXISTS detection_suppressions;
|
|
DROP TABLE IF EXISTS detections;
|
|
DROP TABLE IF EXISTS detection_rules;
|
|
DROP TABLE IF EXISTS user_privilege_baseline;
|
|
DROP TABLE IF EXISTS user_source_ip_seen;
|
|
DROP TABLE IF EXISTS ueba_user_baseline;
|
|
DROP TABLE IF EXISTS privileged_users;
|
|
DROP TABLE IF EXISTS event_logs_raw;
|
|
DROP TABLE IF EXISTS event_logs;
|
|
DROP TABLE IF EXISTS agents;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Agents
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE agents (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
hostname VARCHAR(191) NOT NULL,
|
|
api_key_hash CHAR(64) NOT NULL,
|
|
first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
last_ip VARCHAR(64) NOT NULL DEFAULT '',
|
|
is_enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_agents_hostname (hostname),
|
|
KEY idx_agents_enabled_last_seen (is_enabled, last_seen),
|
|
KEY idx_agents_last_seen (last_seen)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Event Logs, normalisierte Haupttabelle
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE event_logs (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
ts DATETIME(6) NOT NULL,
|
|
agent_id BIGINT UNSIGNED NOT NULL,
|
|
hostname VARCHAR(191) NOT NULL,
|
|
channel_name VARCHAR(128) NOT NULL,
|
|
event_id INT UNSIGNED NOT NULL,
|
|
source VARCHAR(191) NOT NULL DEFAULT '',
|
|
|
|
computer VARCHAR(191) NOT NULL DEFAULT '',
|
|
provider_name VARCHAR(191) NOT NULL DEFAULT '',
|
|
|
|
level_value INT UNSIGNED NOT NULL DEFAULT 0,
|
|
task_value INT UNSIGNED NOT NULL DEFAULT 0,
|
|
opcode_value INT UNSIGNED NOT NULL DEFAULT 0,
|
|
keywords VARCHAR(191) NOT NULL DEFAULT '',
|
|
|
|
target_user VARCHAR(191) NOT NULL DEFAULT '',
|
|
target_user_norm VARCHAR(191) NOT NULL DEFAULT '',
|
|
target_domain VARCHAR(191) NOT NULL DEFAULT '',
|
|
|
|
subject_user VARCHAR(191) NOT NULL DEFAULT '',
|
|
subject_user_norm VARCHAR(191) NOT NULL DEFAULT '',
|
|
subject_domain VARCHAR(191) NOT NULL DEFAULT '',
|
|
|
|
workstation VARCHAR(191) NOT NULL DEFAULT '',
|
|
src_ip VARCHAR(64) NOT NULL DEFAULT '',
|
|
src_port VARCHAR(32) NOT NULL DEFAULT '',
|
|
logon_type VARCHAR(32) NOT NULL DEFAULT '',
|
|
process_name VARCHAR(768) NOT NULL DEFAULT '',
|
|
authentication_package VARCHAR(128) NOT NULL DEFAULT '',
|
|
logon_process VARCHAR(128) NOT NULL DEFAULT '',
|
|
status_text VARCHAR(128) NOT NULL DEFAULT '',
|
|
sub_status_text VARCHAR(128) NOT NULL DEFAULT '',
|
|
failure_reason VARCHAR(768) NOT NULL DEFAULT '',
|
|
|
|
received_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
msg_sha256 CHAR(64) NOT NULL,
|
|
|
|
-- Optionaler Kompatibilitätsrest. Raw XML gehört in event_logs_raw.
|
|
msg MEDIUMTEXT NULL,
|
|
|
|
PRIMARY KEY (id, ts),
|
|
|
|
KEY idx_event_logs_id (id),
|
|
KEY idx_event_logs_agent_ts (agent_id, ts),
|
|
KEY idx_event_logs_ts_host_channel_event (ts, hostname, channel_name, event_id),
|
|
KEY idx_event_logs_hostname_ts (hostname, ts),
|
|
KEY idx_event_logs_channel_event_ts (channel_name, event_id, ts),
|
|
KEY idx_event_logs_target_user_norm_ts (target_user_norm, ts),
|
|
KEY idx_event_logs_subject_user_norm_ts (subject_user_norm, ts),
|
|
KEY idx_event_logs_src_ip_ts (src_ip, ts),
|
|
|
|
KEY idx_event_logs_security_logon_user (
|
|
channel_name,
|
|
event_id,
|
|
ts,
|
|
target_user_norm,
|
|
hostname,
|
|
src_ip
|
|
),
|
|
|
|
KEY idx_event_logs_ueba_context (
|
|
channel_name,
|
|
event_id,
|
|
ts,
|
|
hostname,
|
|
target_user_norm,
|
|
src_ip,
|
|
workstation
|
|
),
|
|
|
|
KEY idx_event_logs_password_spray (
|
|
channel_name,
|
|
event_id,
|
|
ts,
|
|
src_ip,
|
|
target_user_norm,
|
|
hostname
|
|
),
|
|
|
|
KEY idx_event_logs_dedupe_lookup (
|
|
hostname,
|
|
channel_name,
|
|
event_id,
|
|
ts,
|
|
msg_sha256
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
PARTITION BY RANGE COLUMNS(ts) (
|
|
PARTITION pmax VALUES LESS THAN (MAXVALUE)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Raw XML Tabelle
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE event_logs_raw (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
event_log_id BIGINT UNSIGNED NOT NULL,
|
|
ts DATETIME(6) NOT NULL,
|
|
msg_sha256 CHAR(64) NOT NULL,
|
|
msg LONGBLOB NOT NULL,
|
|
is_gzip TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (id, ts),
|
|
|
|
KEY idx_event_logs_raw_id (id),
|
|
KEY idx_event_logs_raw_event_log_id (event_log_id),
|
|
KEY idx_event_logs_raw_ts (ts),
|
|
KEY idx_event_logs_raw_sha (msg_sha256),
|
|
UNIQUE KEY uq_event_logs_raw_event_ts (event_log_id, ts)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
PARTITION BY RANGE COLUMNS(ts) (
|
|
PARTITION pmax VALUES LESS THAN (MAXVALUE)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Detection-Regeln
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE detection_rules (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(191) NOT NULL,
|
|
description TEXT NULL,
|
|
severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
|
|
channel VARCHAR(512) NOT NULL DEFAULT 'Security',
|
|
event_ids VARCHAR(1024) NOT NULL,
|
|
|
|
match_field VARCHAR(128) NOT NULL DEFAULT '',
|
|
match_operator ENUM('', 'eq', 'contains', 'in') NOT NULL DEFAULT '',
|
|
match_value TEXT NULL,
|
|
|
|
threshold_count INT NOT NULL DEFAULT 1,
|
|
threshold_window_seconds INT NOT NULL DEFAULT 0,
|
|
suppress_for_seconds INT NOT NULL DEFAULT 3600,
|
|
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_detection_rules_name (name),
|
|
KEY idx_detection_rules_enabled (enabled)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Detections
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE detections (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
rule_name VARCHAR(191) NOT NULL,
|
|
severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'medium',
|
|
hostname VARCHAR(191) NOT NULL DEFAULT '',
|
|
channel_name VARCHAR(128) NOT NULL DEFAULT '',
|
|
event_id INT UNSIGNED NOT NULL DEFAULT 0,
|
|
score DOUBLE NOT NULL DEFAULT 0,
|
|
|
|
window_start DATETIME(6) NOT NULL,
|
|
window_end DATETIME(6) NOT NULL,
|
|
|
|
summary TEXT NOT NULL,
|
|
details_json LONGTEXT NULL CHECK (JSON_VALID(details_json)),
|
|
|
|
status ENUM(
|
|
'open',
|
|
'acknowledged',
|
|
'investigating',
|
|
'plausible',
|
|
'legitimate',
|
|
'false_positive',
|
|
'resolved',
|
|
'suppressed',
|
|
'confirmed_incident'
|
|
) NOT NULL DEFAULT 'open',
|
|
|
|
analyst_note TEXT NULL,
|
|
reviewed_by VARCHAR(191) NOT NULL DEFAULT '',
|
|
reviewed_at DATETIME(6) NULL,
|
|
is_false_positive TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_legitimate TINYINT(1) NOT NULL DEFAULT 0,
|
|
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (id),
|
|
|
|
UNIQUE KEY uq_detection_dedupe (
|
|
rule_name,
|
|
hostname,
|
|
channel_name,
|
|
event_id,
|
|
window_start,
|
|
window_end
|
|
),
|
|
|
|
KEY idx_detections_created (created_at),
|
|
KEY idx_detections_status_created (status, created_at),
|
|
KEY idx_detections_severity_created (severity, created_at),
|
|
KEY idx_detections_host_created (hostname, created_at),
|
|
KEY idx_detections_rule_host_created (rule_name, hostname, created_at),
|
|
KEY idx_detections_created_status_severity_host (
|
|
created_at,
|
|
status,
|
|
severity,
|
|
hostname
|
|
),
|
|
KEY idx_detections_window (
|
|
hostname,
|
|
window_start,
|
|
window_end
|
|
),
|
|
KEY idx_detections_soc (
|
|
status,
|
|
severity,
|
|
hostname,
|
|
created_at
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Detection Suppressions
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE detection_suppressions (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
rule_name VARCHAR(191) NOT NULL,
|
|
hostname VARCHAR(191) NOT NULL DEFAULT '',
|
|
channel_name VARCHAR(128) NOT NULL DEFAULT '',
|
|
event_id INT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
reason TEXT NULL,
|
|
created_by VARCHAR(191) NOT NULL DEFAULT '',
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
expires_at DATETIME(6) NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
|
|
PRIMARY KEY (id),
|
|
KEY idx_detection_suppressions_lookup (
|
|
enabled,
|
|
rule_name,
|
|
hostname,
|
|
channel_name,
|
|
event_id,
|
|
expires_at
|
|
),
|
|
KEY idx_detection_suppressions_expires (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Event Count Buckets für Baseline
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE event_count_buckets (
|
|
bucket_start DATETIME(6) NOT NULL,
|
|
bucket_end DATETIME(6) NOT NULL,
|
|
|
|
hostname VARCHAR(191) NOT NULL,
|
|
channel_name VARCHAR(128) NOT NULL,
|
|
event_id INT UNSIGNED NOT NULL,
|
|
|
|
cnt BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
first_event_ts DATETIME(6) NULL,
|
|
last_event_ts DATETIME(6) NULL,
|
|
|
|
finalized TINYINT(1) NOT NULL DEFAULT 0,
|
|
anomaly_checked_at DATETIME(6) NULL,
|
|
learned_at DATETIME(6) NULL,
|
|
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (bucket_start, hostname, channel_name, event_id),
|
|
|
|
KEY idx_event_count_buckets_lookup (
|
|
hostname,
|
|
channel_name,
|
|
event_id,
|
|
bucket_start
|
|
),
|
|
|
|
KEY idx_event_count_buckets_anomaly (
|
|
finalized,
|
|
anomaly_checked_at,
|
|
bucket_start
|
|
),
|
|
|
|
KEY idx_event_count_buckets_learn (
|
|
finalized,
|
|
learned_at,
|
|
bucket_start
|
|
),
|
|
|
|
KEY idx_event_count_buckets_time (
|
|
bucket_start,
|
|
bucket_end
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Baseline Stats
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE baseline_event_stats (
|
|
hostname VARCHAR(191) NOT NULL,
|
|
channel_name VARCHAR(128) NOT NULL,
|
|
event_id INT UNSIGNED NOT NULL,
|
|
hour_of_day TINYINT UNSIGNED NOT NULL,
|
|
day_of_week TINYINT UNSIGNED NOT NULL,
|
|
|
|
avg_count DOUBLE NOT NULL DEFAULT 0,
|
|
m2_count DOUBLE NOT NULL DEFAULT 0,
|
|
stddev_count DOUBLE NOT NULL DEFAULT 0,
|
|
sample_count INT NOT NULL DEFAULT 0,
|
|
|
|
first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
last_updated DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (
|
|
hostname,
|
|
channel_name,
|
|
event_id,
|
|
hour_of_day,
|
|
day_of_week
|
|
),
|
|
|
|
KEY idx_baseline_event_stats_event (
|
|
channel_name,
|
|
event_id,
|
|
hour_of_day,
|
|
day_of_week
|
|
),
|
|
|
|
KEY idx_baseline_event_stats_updated (last_updated)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE baseline_exclusions (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
hostname VARCHAR(191) NOT NULL DEFAULT '',
|
|
channel_name VARCHAR(128) NOT NULL DEFAULT '',
|
|
event_id INT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
reason TEXT NULL,
|
|
created_by VARCHAR(191) NOT NULL DEFAULT '',
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
expires_at DATETIME(6) NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
|
|
PRIMARY KEY (id),
|
|
|
|
KEY idx_baseline_exclusions_lookup (
|
|
enabled,
|
|
hostname,
|
|
channel_name,
|
|
event_id,
|
|
expires_at
|
|
),
|
|
KEY idx_baseline_exclusions_expires (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- UEBA Tabellen
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE ueba_user_baseline (
|
|
username VARCHAR(191) NOT NULL,
|
|
hostname VARCHAR(191) NOT NULL,
|
|
src_ip VARCHAR(64) NOT NULL,
|
|
workstation VARCHAR(191) NOT NULL,
|
|
|
|
first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
seen_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
PRIMARY KEY (username, hostname, src_ip, workstation),
|
|
|
|
KEY idx_ueba_user_baseline_last_seen (last_seen),
|
|
KEY idx_ueba_user_baseline_user_last_seen (username, last_seen),
|
|
KEY idx_ueba_user_baseline_host_last_seen (hostname, last_seen)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE user_source_ip_seen (
|
|
username VARCHAR(191) NOT NULL,
|
|
src_ip VARCHAR(64) NOT NULL,
|
|
hostname VARCHAR(191) NOT NULL,
|
|
|
|
first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
seen_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
PRIMARY KEY (username, src_ip, hostname),
|
|
|
|
KEY idx_user_source_ip_seen_last_seen (last_seen),
|
|
KEY idx_user_source_ip_seen_user_last_seen (username, last_seen),
|
|
KEY idx_user_source_ip_seen_src_ip_last_seen (src_ip, last_seen)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE user_privilege_baseline (
|
|
username VARCHAR(191) NOT NULL,
|
|
|
|
first_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
last_seen DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
seen_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
|
|
PRIMARY KEY (username),
|
|
KEY idx_user_privilege_baseline_last_seen (last_seen)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE ueba_context_buckets (
|
|
bucket_start DATETIME(6) NOT NULL,
|
|
bucket_end DATETIME(6) NOT NULL,
|
|
|
|
username VARCHAR(191) NOT NULL,
|
|
hostname VARCHAR(191) NOT NULL,
|
|
src_ip VARCHAR(64) NOT NULL,
|
|
workstation VARCHAR(191) NOT NULL,
|
|
|
|
cnt BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
first_event_ts DATETIME(6) NULL,
|
|
last_event_ts DATETIME(6) NULL,
|
|
|
|
finalized TINYINT(1) NOT NULL DEFAULT 0,
|
|
checked_at DATETIME(6) NULL,
|
|
learned_at DATETIME(6) NULL,
|
|
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (
|
|
bucket_start,
|
|
username,
|
|
hostname,
|
|
src_ip,
|
|
workstation
|
|
),
|
|
|
|
KEY idx_ueba_context_check (
|
|
finalized,
|
|
checked_at,
|
|
bucket_start
|
|
),
|
|
|
|
KEY idx_ueba_context_learn (
|
|
finalized,
|
|
learned_at,
|
|
bucket_start
|
|
),
|
|
|
|
KEY idx_ueba_context_lookup (
|
|
username,
|
|
hostname,
|
|
src_ip,
|
|
workstation,
|
|
bucket_start
|
|
)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Privileged Users
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE privileged_users (
|
|
username VARCHAR(191) NOT NULL,
|
|
reason TEXT NULL,
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (username),
|
|
KEY idx_privileged_users_enabled (enabled)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Host Risk Scores / SOC
|
|
-- ---------------------------------------------------------------------
|
|
|
|
CREATE TABLE host_risk_scores (
|
|
hostname VARCHAR(191) NOT NULL,
|
|
risk_score DOUBLE NOT NULL DEFAULT 0,
|
|
severity ENUM('info','low','medium','high','critical') NOT NULL DEFAULT 'info',
|
|
|
|
open_detections INT NOT NULL DEFAULT 0,
|
|
high_detections INT NOT NULL DEFAULT 0,
|
|
critical_detections INT NOT NULL DEFAULT 0,
|
|
confirmed_incidents INT NOT NULL DEFAULT 0,
|
|
|
|
last_detection_at DATETIME(6) NULL,
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
|
|
PRIMARY KEY (hostname),
|
|
KEY idx_host_risk_scores_risk (risk_score),
|
|
KEY idx_host_risk_scores_severity_risk (severity, risk_score),
|
|
KEY idx_host_risk_scores_updated (updated_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Partition Management Stored Procedure
|
|
-- Erzeugt 3h-Partitionen von UTC now - 6h bis UTC now + 24h.
|
|
-- ---------------------------------------------------------------------
|
|
|
|
DELIMITER $$
|
|
|
|
CREATE PROCEDURE ensure_siem_partitions()
|
|
BEGIN
|
|
DECLARE v_start DATETIME;
|
|
DECLARE v_end DATETIME;
|
|
DECLARE v_current DATETIME;
|
|
DECLARE v_part_end DATETIME;
|
|
DECLARE v_part_name VARCHAR(32);
|
|
DECLARE v_exists INT DEFAULT 0;
|
|
|
|
SET v_start = FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(UTC_TIMESTAMP()) / 10800) * 10800);
|
|
SET v_start = DATE_SUB(v_start, INTERVAL 6 HOUR);
|
|
SET v_end = DATE_ADD(FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(UTC_TIMESTAMP()) / 10800) * 10800), INTERVAL 27 HOUR);
|
|
|
|
SET v_current = v_start;
|
|
|
|
WHILE v_current < v_end DO
|
|
SET v_part_end = DATE_ADD(v_current, INTERVAL 3 HOUR);
|
|
SET v_part_name = CONCAT('p', DATE_FORMAT(v_current, '%Y%m%d%H'));
|
|
|
|
SELECT COUNT(*)
|
|
INTO v_exists
|
|
FROM information_schema.PARTITIONS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'event_logs'
|
|
AND PARTITION_NAME = v_part_name;
|
|
|
|
IF v_exists = 0 THEN
|
|
SET @sql_event_logs = CONCAT(
|
|
'ALTER TABLE event_logs REORGANIZE PARTITION pmax INTO (',
|
|
'PARTITION ', v_part_name, ' VALUES LESS THAN (''', DATE_FORMAT(v_part_end, '%Y-%m-%d %H:%i:%s'), '''),',
|
|
'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
|
|
);
|
|
|
|
PREPARE stmt_event_logs FROM @sql_event_logs;
|
|
EXECUTE stmt_event_logs;
|
|
DEALLOCATE PREPARE stmt_event_logs;
|
|
END IF;
|
|
|
|
SELECT COUNT(*)
|
|
INTO v_exists
|
|
FROM information_schema.PARTITIONS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = 'event_logs_raw'
|
|
AND PARTITION_NAME = v_part_name;
|
|
|
|
IF v_exists = 0 THEN
|
|
SET @sql_event_logs_raw = CONCAT(
|
|
'ALTER TABLE event_logs_raw REORGANIZE PARTITION pmax INTO (',
|
|
'PARTITION ', v_part_name, ' VALUES LESS THAN (''', DATE_FORMAT(v_part_end, '%Y-%m-%d %H:%i:%s'), '''),',
|
|
'PARTITION pmax VALUES LESS THAN (MAXVALUE))'
|
|
);
|
|
|
|
PREPARE stmt_event_logs_raw FROM @sql_event_logs_raw;
|
|
EXECUTE stmt_event_logs_raw;
|
|
DEALLOCATE PREPARE stmt_event_logs_raw;
|
|
END IF;
|
|
|
|
SET v_current = v_part_end;
|
|
END WHILE;
|
|
END$$
|
|
|
|
DELIMITER ;
|
|
|
|
CALL ensure_siem_partitions();
|
|
-- ---------------------------------------------------------------------
|
|
-- Initiale Beispielregeln
|
|
-- Optional. Kann gelöscht werden, wenn Regeln nur über UI gepflegt werden sollen.
|
|
-- ---------------------------------------------------------------------
|
|
|
|
INSERT INTO detection_rules
|
|
(
|
|
name,
|
|
description,
|
|
severity,
|
|
channel,
|
|
event_ids,
|
|
match_field,
|
|
match_operator,
|
|
match_value,
|
|
threshold_count,
|
|
threshold_window_seconds,
|
|
suppress_for_seconds,
|
|
enabled
|
|
)
|
|
VALUES
|
|
|
|
-- ============================================================
|
|
-- Kritische Manipulationen / Spurenverwischung
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_security_log_cleared',
|
|
'Security Log wurde gelöscht. Das ist fast immer sicherheitsrelevant.',
|
|
'high',
|
|
'Security',
|
|
'1102',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
86400,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_audit_policy_changed',
|
|
'Audit Policy wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'4719',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_system_audit_policy_changed',
|
|
'System Audit Policy wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'4902,4904,4905,4906,4907,4908,4912',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_special_logon',
|
|
'Anmeldung mit speziellen Rechten erkannt.',
|
|
'medium',
|
|
'Security',
|
|
'4672',
|
|
'',
|
|
'',
|
|
'',
|
|
20,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_privileged_service_called',
|
|
'Privilegierter Dienst wurde aufgerufen.',
|
|
'medium',
|
|
'Security',
|
|
'4673,4674',
|
|
'',
|
|
'',
|
|
'',
|
|
10,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Benutzerverwaltung
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_user_created',
|
|
'Neuer AD-Benutzer wurde erstellt.',
|
|
'medium',
|
|
'Security',
|
|
'4720',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_user_enabled',
|
|
'AD-Benutzer wurde aktiviert.',
|
|
'medium',
|
|
'Security',
|
|
'4722',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_user_disabled',
|
|
'AD-Benutzer wurde deaktiviert.',
|
|
'low',
|
|
'Security',
|
|
'4725',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_user_deleted',
|
|
'AD-Benutzer wurde gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'4726',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_user_changed',
|
|
'AD-Benutzerobjekt wurde geändert.',
|
|
'low',
|
|
'Security',
|
|
'4738',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_password_changed_by_user',
|
|
'Benutzer hat eigenes Passwort geändert.',
|
|
'low',
|
|
'Security',
|
|
'4723',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_password_reset',
|
|
'Passwort wurde durch Admin oder berechtigten Benutzer zurückgesetzt.',
|
|
'medium',
|
|
'Security',
|
|
'4724',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_account_lockout',
|
|
'Benutzerkonto wurde gesperrt.',
|
|
'low',
|
|
'Security',
|
|
'4740',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_account_lockout_spike',
|
|
'Viele Account Lockouts innerhalb kurzer Zeit.',
|
|
'medium',
|
|
'Security',
|
|
'4740',
|
|
'',
|
|
'',
|
|
'',
|
|
5,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Gruppenverwaltung allgemein
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_security_group_created',
|
|
'Security-Gruppe wurde erstellt.',
|
|
'medium',
|
|
'Security',
|
|
'4727,4731,4754',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_security_group_deleted',
|
|
'Security-Gruppe wurde gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'4730,4734,4758',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_security_group_changed',
|
|
'Security-Gruppe wurde geändert.',
|
|
'medium',
|
|
'Security',
|
|
'4735,4737,4755',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_group_member_added',
|
|
'Mitglied wurde zu einer Security-Gruppe hinzugefügt.',
|
|
'medium',
|
|
'Security',
|
|
'4728,4732,4756',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_group_member_removed',
|
|
'Mitglied wurde aus einer Security-Gruppe entfernt.',
|
|
'medium',
|
|
'Security',
|
|
'4729,4733,4757',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Privilegierte Gruppen
|
|
-- Achtung: target_user ist hier normalerweise der Gruppenname.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_privileged_group_member_added',
|
|
'Mitglied wurde zu einer privilegierten AD-Gruppe hinzugefügt.',
|
|
'high',
|
|
'Security',
|
|
'4728,4732,4756',
|
|
'target_user',
|
|
'in',
|
|
'Domain Admins,Enterprise Admins,Schema Admins,Administrators,Account Operators,Server Operators,Backup Operators,Print Operators,DNSAdmins,Group Policy Creator Owners,Cert Publishers,Key Admins,Enterprise Key Admins',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_privileged_group_member_removed',
|
|
'Mitglied wurde aus einer privilegierten AD-Gruppe entfernt.',
|
|
'medium',
|
|
'Security',
|
|
'4729,4733,4757',
|
|
'target_user',
|
|
'in',
|
|
'Domain Admins,Enterprise Admins,Schema Admins,Administrators,Account Operators,Server Operators,Backup Operators,Print Operators,DNSAdmins,Group Policy Creator Owners,Cert Publishers,Key Admins,Enterprise Key Admins',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_domain_admins_changed',
|
|
'Änderung an Domain Admins erkannt.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4735,4737',
|
|
'target_user',
|
|
'eq',
|
|
'Domain Admins',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_enterprise_admins_changed',
|
|
'Änderung an Enterprise Admins erkannt.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4735,4737',
|
|
'target_user',
|
|
'eq',
|
|
'Enterprise Admins',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_schema_admins_changed',
|
|
'Änderung an Schema Admins erkannt.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4735,4737',
|
|
'target_user',
|
|
'eq',
|
|
'Schema Admins',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_dnsadmins_changed',
|
|
'Änderung an DNSAdmins erkannt. DNSAdmins können in vielen Umgebungen kritisch sein.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4732,4733,4756,4757',
|
|
'target_user',
|
|
'eq',
|
|
'DNSAdmins',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Computer-Konten
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_computer_account_created',
|
|
'Computer-Konto wurde erstellt.',
|
|
'low',
|
|
'Security',
|
|
'4741',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_computer_account_changed',
|
|
'Computer-Konto wurde geändert.',
|
|
'low',
|
|
'Security',
|
|
'4742',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_computer_account_deleted',
|
|
'Computer-Konto wurde gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'4743',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Kerberos / Authentifizierung
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_kerberos_preauth_failure',
|
|
'Kerberos PreAuth Fehler erkannt.',
|
|
'low',
|
|
'Security',
|
|
'4771',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
300,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_kerberos_preauth_spike',
|
|
'Viele Kerberos PreAuth Fehler. Möglicher Password-Spray oder Bruteforce.',
|
|
'high',
|
|
'Security',
|
|
'4771',
|
|
'',
|
|
'',
|
|
'',
|
|
20,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_kerberos_tgt_failed_spike',
|
|
'Viele fehlgeschlagene Kerberos-TGT-Anfragen.',
|
|
'medium',
|
|
'Security',
|
|
'4768',
|
|
'',
|
|
'',
|
|
'',
|
|
30,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_kerberos_service_ticket_spike',
|
|
'Viele Kerberos-Service-Ticket-Anfragen. Kann auf Kerberoasting oder ungewöhnliche Service-Nutzung hindeuten.',
|
|
'medium',
|
|
'Security',
|
|
'4769',
|
|
'',
|
|
'',
|
|
'',
|
|
100,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_ntlm_authentication_spike',
|
|
'Viele NTLM-Authentifizierungen erkannt.',
|
|
'medium',
|
|
'Security',
|
|
'4776',
|
|
'',
|
|
'',
|
|
'',
|
|
50,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_logon_failed_spike',
|
|
'Viele fehlgeschlagene Logons.',
|
|
'medium',
|
|
'Security',
|
|
'4625',
|
|
'',
|
|
'',
|
|
'',
|
|
25,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_explicit_credentials_used',
|
|
'Anmeldung mit expliziten Anmeldeinformationen erkannt.',
|
|
'medium',
|
|
'Security',
|
|
'4648',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_many_explicit_credentials_used',
|
|
'Viele Anmeldungen mit expliziten Anmeldeinformationen.',
|
|
'high',
|
|
'Security',
|
|
'4648',
|
|
'',
|
|
'',
|
|
'',
|
|
20,
|
|
300,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Dienste, Tasks, Persistenz
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_service_installed_security',
|
|
'Neuer Dienst laut Security Log installiert.',
|
|
'high',
|
|
'Security',
|
|
'4697',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_service_installed_system',
|
|
'Neuer Dienst laut System Log installiert.',
|
|
'high',
|
|
'System',
|
|
'7045',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_scheduled_task_created',
|
|
'Geplanter Task wurde erstellt.',
|
|
'high',
|
|
'Security',
|
|
'4698',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_scheduled_task_updated',
|
|
'Geplanter Task wurde geändert.',
|
|
'medium',
|
|
'Security',
|
|
'4702',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_scheduled_task_deleted',
|
|
'Geplanter Task wurde gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'4699',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_scheduled_task_enabled_disabled',
|
|
'Geplanter Task wurde aktiviert oder deaktiviert.',
|
|
'medium',
|
|
'Security',
|
|
'4700,4701',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Prozess / PowerShell / Script Logging
|
|
-- Nur aktivieren, wenn diese Events bei dir tatsächlich gesammelt werden.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_process_created',
|
|
'Neuer Prozess wurde erstellt. Nur sinnvoll bei aktiviertem Process Creation Auditing.',
|
|
'low',
|
|
'Security',
|
|
'4688',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
300,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_suspicious_powershell_process',
|
|
'Verdächtiger PowerShell-Aufruf im Prozess-Event.',
|
|
'high',
|
|
'Security',
|
|
'4688',
|
|
'msg',
|
|
'contains',
|
|
'powershell',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_powershell_script_block',
|
|
'PowerShell Script Block Logging Event erkannt.',
|
|
'medium',
|
|
'Windows PowerShell,Microsoft-Windows-PowerShell/Operational',
|
|
'4104',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_suspicious_powershell_encodedcommand',
|
|
'PowerShell EncodedCommand erkannt.',
|
|
'high',
|
|
'Windows PowerShell,Microsoft-Windows-PowerShell/Operational,Security',
|
|
'4104,4688',
|
|
'msg',
|
|
'contains',
|
|
'EncodedCommand',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_suspicious_powershell_downloadstring',
|
|
'PowerShell DownloadString erkannt.',
|
|
'high',
|
|
'Windows PowerShell,Microsoft-Windows-PowerShell/Operational,Security',
|
|
'4104,4688',
|
|
'msg',
|
|
'contains',
|
|
'DownloadString',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_suspicious_powershell_iex',
|
|
'PowerShell Invoke-Expression / IEX erkannt.',
|
|
'high',
|
|
'Windows PowerShell,Microsoft-Windows-PowerShell/Operational,Security',
|
|
'4104,4688',
|
|
'msg',
|
|
'contains',
|
|
'Invoke-Expression',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
-- ============================================================
|
|
-- GPO / AD Objektänderungen
|
|
-- Hinweis: 5136/5137/5141 kommen aus Directory Service Changes.
|
|
-- Dafür muss das passende Auditing auf DCs aktiv sein.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_directory_object_modified',
|
|
'AD-Objekt wurde geändert.',
|
|
'medium',
|
|
'Security',
|
|
'5136',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_directory_object_created',
|
|
'AD-Objekt wurde erstellt.',
|
|
'medium',
|
|
'Security',
|
|
'5137',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_directory_object_deleted',
|
|
'AD-Objekt wurde gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'5141',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_gpo_changed',
|
|
'Mögliche GPO-Änderung erkannt.',
|
|
'high',
|
|
'Security',
|
|
'5136',
|
|
'msg',
|
|
'contains',
|
|
'CN=Policies,CN=System',
|
|
1,
|
|
0,
|
|
3600,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_adminsdholder_changed',
|
|
'AdminSDHolder wurde geändert. Sehr sicherheitsrelevant.',
|
|
'high',
|
|
'Security',
|
|
'5136',
|
|
'msg',
|
|
'contains',
|
|
'CN=AdminSDHolder',
|
|
1,
|
|
0,
|
|
86400,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_domain_root_changed',
|
|
'Domain-Root-Objekt wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'5136',
|
|
'msg',
|
|
'contains',
|
|
'DC=',
|
|
1,
|
|
0,
|
|
3600,
|
|
0
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Objektzugriff / ACL
|
|
-- Hinweis: 4662/4670 brauchen passende SACLs.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_object_permissions_changed',
|
|
'Berechtigungen eines Objekts wurden geändert.',
|
|
'high',
|
|
'Security',
|
|
'4670',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_directory_service_object_access',
|
|
'Directory-Service-Objektzugriff erkannt.',
|
|
'medium',
|
|
'Security',
|
|
'4662',
|
|
'',
|
|
'',
|
|
'',
|
|
10,
|
|
300,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Share / SMB / SYSVOL / administrative Zugriffe
|
|
-- Hinweis: Kann sehr laut werden.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_network_share_accessed',
|
|
'Netzwerkfreigabe wurde genutzt.',
|
|
'low',
|
|
'Security',
|
|
'5140',
|
|
'',
|
|
'',
|
|
'',
|
|
50,
|
|
300,
|
|
900,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_network_share_object_checked',
|
|
'Detaillierter Netzwerkfreigabezugriff erkannt.',
|
|
'low',
|
|
'Security',
|
|
'5145',
|
|
'',
|
|
'',
|
|
'',
|
|
100,
|
|
300,
|
|
900,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_sysvol_access_spike',
|
|
'Viele Zugriffe auf SYSVOL erkannt.',
|
|
'low',
|
|
'Security',
|
|
'5140,5145',
|
|
'msg',
|
|
'contains',
|
|
'SYSVOL',
|
|
100,
|
|
300,
|
|
900,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_admin_share_access',
|
|
'Zugriff auf administrative Freigabe erkannt.',
|
|
'medium',
|
|
'Security',
|
|
'5140,5145',
|
|
'msg',
|
|
'contains',
|
|
'ADMIN$',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_c_share_access',
|
|
'Zugriff auf C$ erkannt.',
|
|
'medium',
|
|
'Security',
|
|
'5140,5145',
|
|
'msg',
|
|
'contains',
|
|
'C$',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
-- ============================================================
|
|
-- System / Neustart / Agent-Kontext
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_system_startup',
|
|
'System wurde gestartet.',
|
|
'low',
|
|
'System',
|
|
'6005',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
300,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_system_shutdown',
|
|
'System wurde heruntergefahren.',
|
|
'low',
|
|
'System',
|
|
'6006',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
300,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_planned_shutdown_or_restart',
|
|
'Geplanter Shutdown oder Neustart erkannt.',
|
|
'low',
|
|
'System',
|
|
'1074',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
900,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_unexpected_shutdown',
|
|
'Unerwarteter Shutdown erkannt.',
|
|
'medium',
|
|
'System',
|
|
'6008',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v1_reboot_spike_dynamic',
|
|
'Viele Neustart-/Shutdown-Events in kurzer Zeit.',
|
|
'medium',
|
|
'System',
|
|
'1074,6005,6006,6008',
|
|
'',
|
|
'',
|
|
'',
|
|
3,
|
|
900,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Windows Defender / Security Center
|
|
-- Event IDs können je nach Version/Quelle variieren.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_defender_malware_detected',
|
|
'Microsoft Defender hat Malware erkannt.',
|
|
'high',
|
|
'Microsoft-Windows-Windows Defender/Operational',
|
|
'1116',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_defender_malware_remediated',
|
|
'Microsoft Defender hat Malware bereinigt.',
|
|
'medium',
|
|
'Microsoft-Windows-Windows Defender/Operational',
|
|
'1117',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_defender_action_failed',
|
|
'Microsoft Defender Aktion fehlgeschlagen.',
|
|
'high',
|
|
'Microsoft-Windows-Windows Defender/Operational',
|
|
'1118,1119',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_defender_disabled_or_config_changed',
|
|
'Defender-Konfiguration wurde geändert oder Schutz deaktiviert.',
|
|
'high',
|
|
'Microsoft-Windows-Windows Defender/Operational',
|
|
'5007,5013',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
3600,
|
|
0
|
|
),
|
|
|
|
-- ============================================================
|
|
-- Zertifikatsdienste / AD CS
|
|
-- Nur aktivieren, wenn AD CS vorhanden und Events gesammelt werden.
|
|
-- ============================================================
|
|
|
|
(
|
|
'v1_certificate_request_issued',
|
|
'Zertifikat wurde ausgestellt.',
|
|
'medium',
|
|
'Security',
|
|
'4886,4887',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
1800,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_certificate_template_changed',
|
|
'Zertifikatstemplate wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'4898,4899,4900',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
86400,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v1_cert_services_config_changed',
|
|
'AD-CS-Konfiguration wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'4882,4885,4890,4891,4892',
|
|
'',
|
|
'',
|
|
'',
|
|
1,
|
|
0,
|
|
86400,
|
|
0
|
|
),
|
|
|
|
(
|
|
'v2_security_log_cleared',
|
|
'Security Log wurde gelöscht. Sehr wahrscheinlich Spurenverwischung.',
|
|
'high',
|
|
'Security',
|
|
'1102',
|
|
'', '', '',
|
|
1, 0, 86400,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_audit_policy_changed',
|
|
'Audit Policy wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'4719',
|
|
'', '', '',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_system_audit_policy_changed',
|
|
'System Audit Policy wurde geändert.',
|
|
'high',
|
|
'Security',
|
|
'4902,4904,4905,4906,4907,4908,4912',
|
|
'', '', '',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- PRIVILEGIERTE GRUPPEN (WICHTIGSTER BLOCK!)
|
|
-- ============================================================
|
|
|
|
(
|
|
'v2_privileged_group_member_added',
|
|
'Mitglied wurde zu privilegierter AD-Gruppe hinzugefügt.',
|
|
'high',
|
|
'Security',
|
|
'4728,4732,4756',
|
|
'target_user',
|
|
'in',
|
|
'Domain Admins,Enterprise Admins,Schema Admins,Administrators,DNSAdmins',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_privileged_group_member_removed',
|
|
'Mitglied wurde aus privilegierter AD-Gruppe entfernt.',
|
|
'medium',
|
|
'Security',
|
|
'4729,4733,4757',
|
|
'target_user',
|
|
'in',
|
|
'Domain Admins,Enterprise Admins,Schema Admins,Administrators,DNSAdmins',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_domain_admins_changed',
|
|
'Änderung an Domain Admins.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4735,4737',
|
|
'target_user',
|
|
'eq',
|
|
'Domain Admins',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_enterprise_admins_changed',
|
|
'Änderung an Enterprise Admins.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4735,4737',
|
|
'target_user',
|
|
'eq',
|
|
'Enterprise Admins',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_schema_admins_changed',
|
|
'Änderung an Schema Admins.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4735,4737',
|
|
'target_user',
|
|
'eq',
|
|
'Schema Admins',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_dnsadmins_changed',
|
|
'Änderung an DNSAdmins erkannt.',
|
|
'high',
|
|
'Security',
|
|
'4728,4729,4732,4733,4756,4757',
|
|
'target_user',
|
|
'eq',
|
|
'DNSAdmins',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- USER MANAGEMENT
|
|
-- ============================================================
|
|
|
|
(
|
|
'v2_user_created',
|
|
'Neuer AD-Benutzer wurde erstellt.',
|
|
'medium',
|
|
'Security',
|
|
'4720',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_user_enabled',
|
|
'AD-Benutzer wurde aktiviert.',
|
|
'medium',
|
|
'Security',
|
|
'4722',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_user_deleted',
|
|
'AD-Benutzer wurde gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'4726',
|
|
'', '', '',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_password_reset',
|
|
'Passwort wurde zurückgesetzt.',
|
|
'medium',
|
|
'Security',
|
|
'4724',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- AUTHENTIFIZIERUNG (ANOMALIEN)
|
|
-- ============================================================
|
|
|
|
(
|
|
'v2_account_lockout_spike',
|
|
'Viele Account Lockouts.',
|
|
'medium',
|
|
'Security',
|
|
'4740',
|
|
'', '', '',
|
|
5, 300, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_kerberos_preauth_spike',
|
|
'Viele Kerberos PreAuth Fehler.',
|
|
'high',
|
|
'Security',
|
|
'4771',
|
|
'', '', '',
|
|
20, 300, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_kerberos_service_ticket_spike',
|
|
'Viele Kerberos Service Tickets (Kerberoasting möglich).',
|
|
'medium',
|
|
'Security',
|
|
'4769',
|
|
'', '', '',
|
|
100, 300, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_ntlm_authentication_spike',
|
|
'Viele NTLM Authentifizierungen.',
|
|
'medium',
|
|
'Security',
|
|
'4776',
|
|
'', '', '',
|
|
50, 300, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_logon_failed_spike',
|
|
'Viele fehlgeschlagene Logons.',
|
|
'medium',
|
|
'Security',
|
|
'4625',
|
|
'', '', '',
|
|
25, 300, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_many_explicit_credentials_used',
|
|
'Viele Logons mit expliziten Credentials.',
|
|
'high',
|
|
'Security',
|
|
'4648',
|
|
'', '', '',
|
|
20, 300, 1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- PERSISTENZ / LATERAL MOVEMENT
|
|
-- ============================================================
|
|
|
|
(
|
|
'v2_service_installed_security',
|
|
'Neuer Dienst (Security Log).',
|
|
'high',
|
|
'Security',
|
|
'4697',
|
|
'', '', '',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_service_installed_system',
|
|
'Neuer Dienst (System Log).',
|
|
'high',
|
|
'System',
|
|
'7045',
|
|
'', '', '',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_scheduled_task_created',
|
|
'Scheduled Task erstellt.',
|
|
'high',
|
|
'Security',
|
|
'4698',
|
|
'', '', '',
|
|
1, 0, 3600,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_scheduled_task_updated',
|
|
'Scheduled Task geändert.',
|
|
'medium',
|
|
'Security',
|
|
'4702',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_scheduled_task_deleted',
|
|
'Scheduled Task gelöscht.',
|
|
'medium',
|
|
'Security',
|
|
'4699',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
),
|
|
|
|
(
|
|
'v2_scheduled_task_enabled_disabled',
|
|
'Scheduled Task aktiviert/deaktiviert.',
|
|
'medium',
|
|
'Security',
|
|
'4700,4701',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
),
|
|
|
|
-- ============================================================
|
|
-- SYSTEM EVENTS
|
|
-- ============================================================
|
|
|
|
(
|
|
'v2_unexpected_shutdown',
|
|
'Unerwarteter Shutdown erkannt.',
|
|
'medium',
|
|
'System',
|
|
'6008',
|
|
'', '', '',
|
|
1, 0, 1800,
|
|
1
|
|
)
|
|
ON DUPLICATE KEY UPDATE
|
|
description = VALUES(description),
|
|
severity = VALUES(severity),
|
|
channel = VALUES(channel),
|
|
event_ids = VALUES(event_ids),
|
|
match_field = VALUES(match_field),
|
|
match_operator = VALUES(match_operator),
|
|
match_value = VALUES(match_value),
|
|
threshold_count = VALUES(threshold_count),
|
|
threshold_window_seconds = VALUES(threshold_window_seconds),
|
|
suppress_for_seconds = VALUES(suppress_for_seconds),
|
|
enabled = VALUES(enabled);
|
|
|
|
UPDATE detection_rules
|
|
SET enabled = 0
|
|
WHERE name LIKE 'v1_%';
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Diagnose-Queries nach Restore
|
|
-- ---------------------------------------------------------------------
|
|
-- SELECT TABLE_NAME, PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS
|
|
-- FROM information_schema.PARTITIONS
|
|
-- WHERE TABLE_SCHEMA = DATABASE()
|
|
-- AND TABLE_NAME IN ('event_logs', 'event_logs_raw')
|
|
-- ORDER BY TABLE_NAME, PARTITION_ORDINAL_POSITION;
|
|
--
|
|
-- SHOW INDEX FROM event_logs;
|
|
-- SHOW INDEX FROM event_logs_raw;
|
|
-- SHOW INDEX FROM detections;
|