Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

07/07/26 18:49:42 (6 hours ago)

Author:

231118

Comment:

--

AdvancedReports

@@ +1 @@
+= Advanced Reports =
+
+== Извештај 1: Детален извештај: Top outbound конекции по процес (по tenant/env и период) ==
+
+Извештај кој ги прикажува процесите кои воспоставуваат најмногу излезни мрежни конекции во рамките на одреден tenant и environment, за зададен временски период. Корисен за откривање на сомнителни или необично активни процеси на мрежно ниво.
+
+=== Solution SQL ===
+
+{{{
+WITH filtered AS (
+  SELECT
+    nc.computer_id,
+    nc.process_name,
+    nc.pid,
+    nc.remote_address,
+    nc.timestamp
+  FROM network_connections nc
+  WHERE nc.timestamp BETWEEN ? AND ?
+),
+agg AS (
+  SELECT
+    computer_id,
+    process_name,
+    pid,
+    COUNT(*) AS total_connections,
+    COUNT(DISTINCT remote_address) AS unique_remotes,
+    MAX(timestamp) AS last_seen_connection
+  FROM filtered
+  GROUP BY computer_id, process_name, pid
+)
+SELECT
+  c.tenant_id,
+  c.env_name,
+  c.id AS computer_id,
+  c.name AS computer_name,
+  a.process_name,
+  a.pid,
+  a.total_connections,
+  a.unique_remotes,
+  a.last_seen_connection
+FROM agg a
+JOIN computers c ON c.id = a.computer_id
+WHERE c.tenant_id = ?
+  AND c.env_name = ?
+ORDER BY a.total_connections DESC
+LIMIT ?;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+π tenant_id, env_name, computer_id, computer_name, process_name, pid, total_connections, unique_remotes, last_seen_connection (
+  γ computer_id, process_name, pid;
+    COUNT(*)→total_connections, COUNT(DISTINCT remote_address)→unique_remotes, MAX(timestamp)→last_seen_connection (
+      σ timestamp≥T1 ∧ timestamp≤T2 (network_connections)
+    )
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+}}}
+
+----
+
+== Извештај 2: Нерешени security alerts + распределба по severity (по компјутер) ==
+
+Извештај кој за секој компјутер во рамките на tenant/env ги прикажува вкупниот број на безбедносни предупредувања, бројот на нерешени, и нивната распределба по тежина (critical, high, medium, low). Служи за брзо идентификување на најзагрозените машини.
+
+=== Solution SQL ===
+
+{{{
+WITH filtered AS (
+  SELECT
+    sa.computer_id,
+    sa.severity,
+    sa.resolved,
+    sa.timestamp
+  FROM security_alerts sa
+  WHERE sa.timestamp BETWEEN ? AND ?
+),
+agg AS (
+  SELECT
+    computer_id,
+    COUNT(*) AS total_alerts,
+    SUM(CASE WHEN resolved = 0 THEN 1 ELSE 0 END) AS unresolved_alerts,
+    SUM(CASE WHEN LOWER(severity) = 'critical' THEN 1 ELSE 0 END) AS sev_critical,
+    SUM(CASE WHEN LOWER(severity) = 'high' THEN 1 ELSE 0 END) AS sev_high,
+    SUM(CASE WHEN LOWER(severity) = 'medium' THEN 1 ELSE 0 END) AS sev_medium,
+    SUM(CASE WHEN LOWER(severity) = 'low' THEN 1 ELSE 0 END) AS sev_low,
+    MAX(timestamp) AS last_alert_time
+  FROM filtered
+  GROUP BY computer_id
+)
+SELECT
+  c.id AS computer_id,
+  c.name AS computer_name,
+  a.total_alerts,
+  a.unresolved_alerts,
+  a.sev_critical,
+  a.sev_high,
+  a.sev_medium,
+  a.sev_low,
+  a.last_alert_time
+FROM agg a
+JOIN computers c ON c.id = a.computer_id
+WHERE c.tenant_id = ?
+  AND c.env_name = ?
+ORDER BY a.unresolved_alerts DESC, a.sev_critical DESC, a.last_alert_time DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+π computer_id, computer_name, total_alerts, unresolved_alerts,
+  sev_critical, sev_high, sev_medium, sev_low, last_alert_time (
+
+  γ computer_id;
+    COUNT(*)→total_alerts,
+    COUNT(σ resolved=0)→unresolved_alerts,
+    COUNT(σ severity='critical')→sev_critical,
+    COUNT(σ severity='high')→sev_high,
+    COUNT(σ severity='medium')→sev_medium,
+    COUNT(σ severity='low')→sev_low,
+    MAX(timestamp)→last_alert_time (
+      σ timestamp≥T1 ∧ timestamp≤T2 (security_alerts)
+    )
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+}}}
+
+----
+
+== Извештај 3: Resource hotspots (CPU/RAM/DISK) по компјутер со прагови ==
+
+Извештај кој ги идентификува компјутерите со критично висока просечна или максимална потрошувачка на CPU, RAM или диск во зadadен период. Корисен за планирање на капацитети и откривање на преоптоварени машини.
+
+=== Solution SQL ===
+
+{{{
+WITH filtered AS (
+  SELECT
+    ch.computer_id,
+    ch.cpu_usage,
+    ch.ram_usage,
+    ch.disk_usage,
+    ch.timestamp
+  FROM computer_history ch
+  WHERE ch.timestamp BETWEEN ? AND ?
+),
+stats AS (
+  SELECT
+    computer_id,
+    ROUND(AVG(cpu_usage), 2) AS avg_cpu,
+    ROUND(MAX(cpu_usage), 2) AS max_cpu,
+    ROUND(AVG(ram_usage), 2) AS avg_ram,
+    ROUND(MAX(ram_usage), 2) AS max_ram,
+    ROUND(AVG(disk_usage), 2) AS avg_disk,
+    ROUND(MAX(disk_usage), 2) AS max_disk,
+    MAX(timestamp) AS last_sample
+  FROM filtered
+  GROUP BY computer_id
+)
+SELECT
+  c.id AS computer_id,
+  c.name AS computer_name,
+  s.avg_cpu, s.max_cpu,
+  s.avg_ram, s.max_ram,
+  s.avg_disk, s.max_disk,
+  s.last_sample
+FROM stats s
+JOIN computers c ON c.id = s.computer_id
+WHERE c.tenant_id = ?
+  AND c.env_name = ?
+  AND (s.max_cpu >= ? OR s.max_ram >= ? OR s.max_disk >= ?)
+ORDER BY (s.max_cpu + s.max_ram + s.max_disk) DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+π computer_id, computer_name, avg_cpu, max_cpu, avg_ram, max_ram, avg_disk, max_disk, last_sample (
+
+  σ max_cpu≥TCPU ∨ max_ram≥TRAM ∨ max_disk≥TDISK (
+
+    γ computer_id;
+      AVG(cpu_usage)→avg_cpu, MAX(cpu_usage)→max_cpu,
+      AVG(ram_usage)→avg_ram, MAX(ram_usage)→max_ram,
+      AVG(disk_usage)→avg_disk, MAX(disk_usage)→max_disk,
+      MAX(timestamp)→last_sample (
+        σ timestamp≥T1 ∧ timestamp≤T2 (computer_history)
+      )
+  )
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+}}}
+
+----
+
+== Извештај 4: Top процеси по просечен CPU / MEM (history) ==
+
+Извештај кој ги прикажува процесите со највисока просечна потрошувачка на процесор или меморија во историски период, групирани по компјутер и корисник. Корисен за откривање на ресурсно интензивни апликации на долг рок.
+
+=== Solution SQL ===
+
+{{{
+WITH filtered AS (
+  SELECT
+    ph.computer_id,
+    ph.name AS process_name,
+    ph.username,
+    ph.cpu_percent,
+    ph.memory_mb,
+    ph.timestamp
+  FROM computer_processes_history ph
+  WHERE ph.timestamp BETWEEN ? AND ?
+),
+proc_stats AS (
+  SELECT
+    computer_id,
+    process_name,
+    username,
+    ROUND(AVG(cpu_percent), 2) AS avg_cpu,
+    ROUND(MAX(cpu_percent), 2) AS max_cpu,
+    ROUND(AVG(memory_mb), 2) AS avg_mem_mb,
+    ROUND(MAX(memory_mb), 2) AS max_mem_mb,
+    COUNT(*) AS samples,
+    MAX(timestamp) AS last_seen
+  FROM filtered
+  GROUP BY computer_id, process_name, username
+)
+SELECT
+  c.id AS computer_id,
+  c.name AS computer_name,
+  p.process_name,
+  p.username,
+  p.avg_cpu, p.max_cpu,
+  p.avg_mem_mb, p.max_mem_mb,
+  p.samples,
+  p.last_seen
+FROM proc_stats p
+JOIN computers c ON c.id = p.computer_id
+WHERE c.tenant_id = ?
+  AND c.env_name = ?
+ORDER BY p.avg_cpu DESC
+LIMIT ?;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+π computer_id, computer_name, process_name, username,
+  avg_cpu, max_cpu, avg_mem_mb, max_mem_mb, samples, last_seen (
+
+  γ computer_id, process_name, username;
+    AVG(cpu_percent)→avg_cpu, MAX(cpu_percent)→max_cpu,
+    AVG(memory_mb)→avg_mem_mb, MAX(memory_mb)→max_mem_mb,
+    COUNT(*)→samples, MAX(timestamp)→last_seen (
+      σ timestamp≥T1 ∧ timestamp≤T2 (computer_processes_history)
+    )
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+}}}
+
+----
+
+== Извештај 5: Sysmon активности (counts по event_type и компјутер) ==
+
+Извештај кој ги брои Sysmon настаните по тип и компјутер во одреден период. Корисен за следење на системски активности и откривање на аномалии во однесувањето на машините.
+
+=== Solution SQL ===
+
+{{{
+WITH filtered AS (
+  SELECT
+    se.computer_id,
+    se.event_type,
+    se.timestamp
+  FROM sysmon_events se
+  WHERE se.timestamp BETWEEN ? AND ?
+),
+agg AS (
+  SELECT
+    computer_id,
+    event_type,
+    COUNT(*) AS event_count,
+    MAX(timestamp) AS last_event
+  FROM filtered
+  GROUP BY computer_id, event_type
+)
+SELECT
+  c.id AS computer_id,
+  c.name AS computer_name,
+  a.event_type,
+  a.event_count,
+  a.last_event
+FROM agg a
+JOIN computers c ON c.id = a.computer_id
+WHERE c.tenant_id = ?
+  AND c.env_name = ?
+ORDER BY a.event_count DESC
+LIMIT ?;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+π computer_id, computer_name, event_type, event_count, last_event (
+
+  γ computer_id, event_type;
+    COUNT(*)→event_count, MAX(timestamp)→last_event (
+      σ timestamp≥T1 ∧ timestamp≤T2 (sysmon_events)
+    )
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+}}}
+
+----
+
+== Извештај 6: Детекција на компјутери со аномално висок број на Sysmon настани во споредба со просекот на environment-от ==
+
+Овој извештај ги открива компјутерите чиј број на Sysmon настани значително го надминува просекот за целиот environment во зададен период. Служи за долгорочно откривање на машини со невообичаено однесување — потенцијални жртви на малициозен софтвер, неправилно конфигурирани апликации или напади.
+
+=== Solution SQL ===
+
+{{{
+WITH event_counts AS (
+  SELECT
+    se.computer_id,
+    COUNT(*) AS total_events
+  FROM sysmon_events se
+  JOIN computers c ON c.id = se.computer_id
+  WHERE c.tenant_id = ?
+    AND c.env_name = ?
+    AND se.timestamp BETWEEN ? AND ?
+  GROUP BY se.computer_id
+),
+env_stats AS (
+  SELECT
+    AVG(total_events) AS avg_events,
+    MAX(total_events) AS max_events,
+    MIN(total_events) AS min_events
+  FROM event_counts
+),
+anomalies AS (
+  SELECT
+    ec.computer_id,
+    ec.total_events,
+    es.avg_events,
+    ROUND((ec.total_events - es.avg_events) / NULLIF(es.avg_events, 0) * 100, 2) AS pct_above_avg
+  FROM event_counts ec
+  CROSS JOIN env_stats es
+  WHERE ec.total_events > es.avg_events * 1.5
+)
+SELECT
+  c.id AS computer_id,
+  c.name AS computer_name,
+  c.ip AS computer_ip,
+  c.user AS computer_user,
+  a.total_events,
+  ROUND(a.avg_events, 2) AS env_avg_events,
+  a.pct_above_avg AS percent_above_average
+FROM anomalies a
+JOIN computers c ON c.id = a.computer_id
+ORDER BY a.pct_above_avg DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+-- Чекор 1: Броење на настани по компјутер
+EC ← γ computer_id; COUNT(*)→total_events (
+  σ timestamp≥T1 ∧ timestamp≤T2 (sysmon_events)
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+
+-- Чекор 2: Статистики за целиот environment
+ES ← γ ; AVG(total_events)→avg_events (EC)
+
+-- Чекор 3: Крос производ и филтрирање на аномалии
+ANOMALIES ← σ total_events > avg_events * 1.5 (EC × ES)
+
+-- Чекор 4: Финална проекција со податоци за компјутерот
+π computer_id, computer_name, computer_ip, computer_user,
+  total_events, avg_events, pct_above_avg (
+  ANOMALIES ⋈ computer_id=id (computers)
+)
+}}}
+
+----
+
+== Извештај 7: Корелација помеѓу висока ресурсна потрошувачка и pojava на security alerts (по компјутер и период) ==
+
+Овој извештај ги идентификува компјутерите каде периодите на висока CPU/RAM потрошувачка хронолошки се поклопуваат со зголемен број на безбедносни предупредувања. Тоа може да укаже на малициозни процеси кои истовремено трошат ресурси и предизвикуваат безбедносни настани. Корисен за квартални и годишни безбедносни анализи.
+
+=== Solution SQL ===
+
+{{{
+WITH resource_peaks AS (
+  SELECT
+    ch.computer_id,
+    STRFTIME('%Y-%m-%dT%H', ch.timestamp) AS hour_bucket,
+    ROUND(AVG(ch.cpu_usage), 2)  AS avg_cpu,
+    ROUND(AVG(ch.ram_usage), 2)  AS avg_ram
+  FROM computer_history ch
+  JOIN computers c ON c.id = ch.computer_id
+  WHERE c.tenant_id = ?
+    AND c.env_name = ?
+    AND ch.timestamp BETWEEN ? AND ?
+  GROUP BY ch.computer_id, hour_bucket
+  HAVING AVG(ch.cpu_usage) >= ? OR AVG(ch.ram_usage) >= ?
+),
+alert_counts AS (
+  SELECT
+    sa.computer_id,
+    STRFTIME('%Y-%m-%dT%H', sa.timestamp) AS hour_bucket,
+    COUNT(*)                              AS alerts_in_hour,
+    SUM(CASE WHEN LOWER(sa.severity) IN ('critical','high') THEN 1 ELSE 0 END) AS high_sev_alerts
+  FROM security_alerts sa
+  JOIN computers c ON c.id = sa.computer_id
+  WHERE c.tenant_id = ?
+    AND c.env_name = ?
+    AND sa.timestamp BETWEEN ? AND ?
+  GROUP BY sa.computer_id, hour_bucket
+),
+correlated AS (
+  SELECT
+    rp.computer_id,
+    rp.hour_bucket,
+    rp.avg_cpu,
+    rp.avg_ram,
+    COALESCE(ac.alerts_in_hour, 0)  AS alerts_in_hour,
+    COALESCE(ac.high_sev_alerts, 0) AS high_sev_alerts
+  FROM resource_peaks rp
+  LEFT JOIN alert_counts ac
+         ON ac.computer_id = rp.computer_id
+        AND ac.hour_bucket  = rp.hour_bucket
+),
+summary AS (
+  SELECT
+    computer_id,
+    COUNT(*)                        AS peak_hours,
+    SUM(alerts_in_hour)             AS total_alerts_during_peaks,
+    SUM(high_sev_alerts)            AS total_high_sev_during_peaks,
+    ROUND(AVG(avg_cpu), 2)          AS mean_cpu_during_peaks,
+    ROUND(AVG(avg_ram), 2)          AS mean_ram_during_peaks
+  FROM correlated
+  GROUP BY computer_id
+)
+SELECT
+  c.id   AS computer_id,
+  c.name AS computer_name,
+  c.ip   AS computer_ip,
+  s.peak_hours,
+  s.mean_cpu_during_peaks,
+  s.mean_ram_during_peaks,
+  s.total_alerts_during_peaks,
+  s.total_high_sev_during_peaks,
+  ROUND(
+    CAST(s.total_alerts_during_peaks AS REAL) / NULLIF(s.peak_hours, 0),
+    2
+  ) AS avg_alerts_per_peak_hour
+FROM summary s
+JOIN computers c ON c.id = s.computer_id
+ORDER BY s.total_high_sev_during_peaks DESC,
+         s.total_alerts_during_peaks DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+-- Чекор 1: Временски зафати (peak часови) со висока CPU/RAM
+RP ← σ avg_cpu≥TCPU ∨ avg_ram≥TRAM (
+  γ computer_id, hour_bucket;
+    AVG(cpu_usage)→avg_cpu, AVG(ram_usage)→avg_ram (
+      σ timestamp≥T1 ∧ timestamp≤T2 (computer_history)
+      ⋈ computer_id=id
+      σ tenant_id=TID ∧ env_name=ENV (computers)
+    )
+)
+
+-- Чекор 2: Алерти агрегирани по час
+AC ← γ computer_id, hour_bucket;
+       COUNT(*)→alerts_in_hour,
+       COUNT(σ severity∈{'critical','high'})→high_sev_alerts (
+  σ timestamp≥T1 ∧ timestamp≤T2 (security_alerts)
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+
+-- Чекор 3: Left outer join на peak часови со алерти
+CORR ← RP ⟕ (computer_id=computer_id ∧ hour_bucket=hour_bucket) AC
+
+-- Чекор 4: Сумаризација по компјутер
+SUM ← γ computer_id;
+        COUNT(*)→peak_hours,
+        SUM(alerts_in_hour)→total_alerts_during_peaks,
+        SUM(high_sev_alerts)→total_high_sev_during_peaks,
+        AVG(avg_cpu)→mean_cpu_during_peaks,
+        AVG(avg_ram)→mean_ram_during_peaks (CORR)
+
+-- Чекор 5: Финална проекција
+π computer_id, computer_name, computer_ip, peak_hours,
+  mean_cpu_during_peaks, mean_ram_during_peaks,
+  total_alerts_during_peaks, total_high_sev_during_peaks,
+  avg_alerts_per_peak_hour (
+  SUM ⋈ computer_id=id (computers)
+)
+}}}