= 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)
)
}}}