Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

08/23/26 20:11:43 (4 days ago)

Author:

231118

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Advanced Reports =
 
-== Извештај 1: Детален извештај: Top outbound конекции по процес (по tenant/env и период) ==
+Забелешка: SQL-от е напишан за официјалната PostgreSQL '''project''' шема (v04).
+Параметрите (?) се временски граници / tenant / env / прагови.
+За SQLite прототипот важат ситни разлики: resolved = false -> resolved = 0,
+to_char(...) -> strftime(...), а ::numeric cast-овите не се потребни.
+
+== Извештај 1: Top outbound конекции по процес (по tenant/env и период) ==
 
 Извештај кој ги прикажува процесите кои воспоставуваат најмногу излезни мрежни конекции во рамките на одреден tenant и environment, за зададен временски период. Корисен за откривање на сомнителни или необично активни процеси на мрежно ниво.
@@ -15 +20 @@
     nc.remote_address,
     nc.timestamp
-  FROM network_connections nc
+  FROM network_connections_history nc
   WHERE nc.timestamp BETWEEN ? AND ?
 ),
@@ -53 +58 @@
   γ 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)
+      σ timestamp≥T1 ∧ timestamp≤T2 (network_connections_history)
     )
   ⋈ computer_id=id
@@ -82 +87 @@
     computer_id,
     COUNT(*) AS total_alerts,
-    SUM(CASE WHEN resolved = 0 THEN 1 ELSE 0 END) AS unresolved_alerts,
+    SUM(CASE WHEN resolved = false 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,
@@ -116 +121 @@
   γ computer_id;
     COUNT(*)→total_alerts,
-    COUNT(σ resolved=0)→unresolved_alerts,
+    COUNT(σ resolved=false)→unresolved_alerts,
     COUNT(σ severity='critical')→sev_critical,
     COUNT(σ severity='high')→sev_high,
@@ -133 +138 @@
 == Извештај 3: Resource hotspots (CPU/RAM/DISK) по компјутер со прагови ==
 
-Извештај кој ги идентификува компјутерите со критично висока просечна или максимална потрошувачка на CPU, RAM или диск во зadadен период. Корисен за планирање на капацитети и откривање на преоптоварени машини.
+Извештај кој ги идентификува компјутерите со критично висока просечна или максимална потрошувачка на CPU, RAM или диск во зададен период. Корисен за планирање на капацитети и откривање на преоптоварени машини.
 
 === Solution SQL ===
@@ -151 +156 @@
   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,
+    ROUND(AVG(cpu_usage)::numeric, 2) AS avg_cpu,
+    ROUND(MAX(cpu_usage)::numeric, 2) AS max_cpu,
+    ROUND(AVG(ram_usage)::numeric, 2) AS avg_ram,
+    ROUND(MAX(ram_usage)::numeric, 2) AS max_ram,
+    ROUND(AVG(disk_usage)::numeric, 2) AS avg_disk,
+    ROUND(MAX(disk_usage)::numeric, 2) AS max_disk,
     MAX(timestamp) AS last_sample
   FROM filtered
@@ -221 +226 @@
     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,
+    ROUND(AVG(cpu_percent)::numeric, 2) AS avg_cpu,
+    ROUND(MAX(cpu_percent)::numeric, 2) AS max_cpu,
+    ROUND(AVG(memory_mb)::numeric, 2) AS avg_mem_mb,
+    ROUND(MAX(memory_mb)::numeric, 2) AS max_mem_mb,
     COUNT(*) AS samples,
     MAX(timestamp) AS last_seen
@@ -320 +325 @@
 ----
 
-== Извештај 6: Детекција на компјутери со аномално висок број на Sysmon настани во споредба со просекот на environment-от ==
-
-Овој извештај ги открива компјутерите чиј број на Sysmon настани значително го надминува просекот за целиот environment во зададен период. Служи за долгорочно откривање на машини со невообичаено однесување — потенцијални жртви на малициозен софтвер, неправилно конфигурирани апликации или напади.
+== Извештај 6: Детекција на компјутери со аномално висок број Sysmon настани во споредба со просекот на environment-от ==
+
+Овој извештај ги открива компјутерите чиј број на Sysmon настани значително го надминува просекот за целиот environment во зададен период. Служи за долгорочно откривање на машини со невообичаено однесување.
 
 === Solution SQL ===
@@ -340 +345 @@
 env_stats AS (
   SELECT
-    AVG(total_events) AS avg_events,
-    MAX(total_events) AS max_events,
-    MIN(total_events) AS min_events
+    AVG(total_events) AS avg_events
   FROM event_counts
 ),
@@ -359 +362 @@
   c.name AS computer_name,
   c.ip AS computer_ip,
-  c.user AS computer_user,
+  c."user" AS computer_user,
   a.total_events,
   ROUND(a.avg_events, 2) AS env_avg_events,
@@ -393 +396 @@
 ----
 
-== Извештај 7: Корелација помеѓу висока ресурсна потрошувачка и pojava на security alerts (по компјутер и период) ==
-
-Овој извештај ги идентификува компјутерите каде периодите на висока CPU/RAM потрошувачка хронолошки се поклопуваат со зголемен број на безбедносни предупредувања. Тоа може да укаже на малициозни процеси кои истовремено трошат ресурси и предизвикуваат безбедносни настани. Корисен за квартални и годишни безбедносни анализи.
+== Извештај 7: Корелација помеѓу висока ресурсна потрошувачка и појава на security alerts (по компјутер и период) ==
+
+Овој извештај ги идентификува компјутерите каде периодите на висока CPU/RAM потрошувачка хронолошки се поклопуваат со зголемен број безбедносни предупредувања. Корисен за квартални и годишни безбедносни анализи.
 
 === Solution SQL ===
@@ -403 +406 @@
   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
+    to_char(ch.timestamp, 'YYYY-MM-DD"T"HH24') AS hour_bucket,
+    ROUND(AVG(ch.cpu_usage)::numeric, 2)  AS avg_cpu,
+    ROUND(AVG(ch.ram_usage)::numeric, 2)  AS avg_ram
   FROM computer_history ch
   JOIN computers c ON c.id = ch.computer_id
@@ -411 +414 @@
     AND c.env_name = ?
     AND ch.timestamp BETWEEN ? AND ?
-  GROUP BY ch.computer_id, hour_bucket
+  GROUP BY ch.computer_id, to_char(ch.timestamp, 'YYYY-MM-DD"T"HH24')
   HAVING AVG(ch.cpu_usage) >= ? OR AVG(ch.ram_usage) >= ?
 ),
@@ -417 +420 @@
   SELECT
     sa.computer_id,
-    STRFTIME('%Y-%m-%dT%H', sa.timestamp) AS hour_bucket,
+    to_char(sa.timestamp, 'YYYY-MM-DD"T"HH24') 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
@@ -425 +428 @@
     AND c.env_name = ?
     AND sa.timestamp BETWEEN ? AND ?
-  GROUP BY sa.computer_id, hour_bucket
+  GROUP BY sa.computer_id, to_char(sa.timestamp, 'YYYY-MM-DD"T"HH24')
 ),
 correlated AS (
@@ -446 +449 @@
     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
+    ROUND(AVG(avg_cpu)::numeric, 2) AS mean_cpu_during_peaks,
+    ROUND(AVG(avg_ram)::numeric, 2) AS mean_ram_during_peaks
   FROM correlated
   GROUP BY computer_id
@@ -461 +464 @@
   s.total_high_sev_during_peaks,
   ROUND(
-    CAST(s.total_alerts_during_peaks AS REAL) / NULLIF(s.peak_hours, 0),
+    s.total_alerts_during_peaks::numeric / NULLIF(s.peak_hours, 0),
     2
   ) AS avg_alerts_per_peak_hour
@@ -473 +476 @@
 
 {{{
--- Чекор 1: Временски зафати (peak часови) со висока CPU/RAM
+-- Чекор 1: Peak часови со висока CPU/RAM
 RP ← σ avg_cpu≥TCPU ∨ avg_ram≥TRAM (
   γ computer_id, hour_bucket;
@@ -511 +514 @@
 )
 }}}
+
+----
+
+== Извештај 8 (напреден): Достапност (uptime) на мрежни сервиси по компјутер ==
+
+Извештај кој за секој мрежен сервис (порта/протокол) што го изложуваат компјутерите пресметува процент на достапност (uptime %) и просечно време на одговор во зададен период. Ги користи напредните табели network_services и service_availability. Корисен за откривање на нестабилни или недостапни сервиси.
+
+=== Solution SQL ===
+
+{{{
+WITH checks AS (
+  SELECT
+    ns.computer_id,
+    ns.service_name,
+    ns.port,
+    ns.protocol,
+    sa.is_available,
+    sa.response_time_ms
+  FROM network_services ns
+  JOIN service_availability sa ON sa.service_id = ns.id
+  WHERE sa.timestamp BETWEEN ? AND ?
+),
+agg AS (
+  SELECT
+    computer_id,
+    service_name,
+    port,
+    protocol,
+    COUNT(*) AS total_checks,
+    SUM(CASE WHEN is_available THEN 1 ELSE 0 END) AS up_checks,
+    ROUND(100.0 * SUM(CASE WHEN is_available THEN 1 ELSE 0 END) / COUNT(*), 2) AS uptime_pct,
+    ROUND(AVG(response_time_ms)::numeric, 2) AS avg_response_ms
+  FROM checks
+  GROUP BY computer_id, service_name, port, protocol
+)
+SELECT
+  c.id AS computer_id,
+  c.name AS computer_name,
+  a.service_name,
+  a.port,
+  a.protocol,
+  a.total_checks,
+  a.up_checks,
+  a.uptime_pct,
+  a.avg_response_ms
+FROM agg a
+JOIN computers c ON c.id = a.computer_id
+WHERE c.tenant_id = ?
+  AND c.env_name = ?
+ORDER BY a.uptime_pct ASC, a.avg_response_ms DESC;
+}}}
+
+=== Solution Relational Algebra ===
+
+{{{
+π computer_id, computer_name, service_name, port, protocol,
+  total_checks, up_checks, uptime_pct, avg_response_ms (
+
+  γ computer_id, service_name, port, protocol;
+    COUNT(*)→total_checks,
+    COUNT(σ is_available=true)→up_checks,
+    AVG(response_time_ms)→avg_response_ms (
+      network_services ⋈ id=service_id (
+        σ timestamp≥T1 ∧ timestamp≤T2 (service_availability)
+      )
+    )
+  ⋈ computer_id=id
+  σ tenant_id=TID ∧ env_name=ENV (computers)
+)
+
+-- uptime_pct = 100 * up_checks / total_checks  (изведено во проекцијата)
+}}}