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)
)
Last modified
6 hours ago
Last modified on 07/07/26 18:49:42
Note:
See TracWiki
for help on using the wiki.
