| Version 2 (modified by , 4 days ago) ( diff ) |
|---|
Advanced Reports
Забелешка: SQL-от е напишан за официјалната PostgreSQL project шема (v04). Параметрите (?) се временски граници / tenant / env / прагови. За SQLite прототипот важат ситни разлики: resolved = false -> resolved = 0, to_char(...) -> strftime(...), а ::numeric cast-овите не се потребни.
Извештај 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_history 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_history)
)
⋈ 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 = 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,
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=false)→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 или диск во зададен период. Корисен за планирање на капацитети и откривање на преоптоварени машини.
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)::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
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)::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
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
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: Корелација помеѓу висока ресурсна потрошувачка и појава на security alerts (по компјутер и период)
Овој извештај ги идентификува компјутерите каде периодите на висока CPU/RAM потрошувачка хронолошки се поклопуваат со зголемен број безбедносни предупредувања. Корисен за квартални и годишни безбедносни анализи.
Solution SQL
WITH resource_peaks AS (
SELECT
ch.computer_id,
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
WHERE c.tenant_id = ?
AND c.env_name = ?
AND ch.timestamp BETWEEN ? AND ?
GROUP BY ch.computer_id, to_char(ch.timestamp, 'YYYY-MM-DD"T"HH24')
HAVING AVG(ch.cpu_usage) >= ? OR AVG(ch.ram_usage) >= ?
),
alert_counts AS (
SELECT
sa.computer_id,
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
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, to_char(sa.timestamp, 'YYYY-MM-DD"T"HH24')
),
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)::numeric, 2) AS mean_cpu_during_peaks,
ROUND(AVG(avg_ram)::numeric, 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(
s.total_alerts_during_peaks::numeric / 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)
)
Извештај 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 (изведено во проекцијата)
Note:
See TracWiki
for help on using the wiki.
