Changes between Initial Version and Version 1 of AdvancedDatabaseDevelopment

Timestamp:

07/07/26 18:50:47 (6 hours ago)

Author:

231118

Comment:

--

AdvancedDatabaseDevelopment

@@ +1 @@
+= Advanced Database Development =
+
+== Автоматска детекција и пријавување на безбедносни аномалии ==
+
+Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција. Ова вклучува ситуации кога одреден компјутер генерира голем број Sysmon настани во краток период, или кога ресурсната потрошувачка (CPU, RAM) ги надминува критичните прагови. Без автоматска детекција, администраторите мора рачно да ги прегледуваат логовите, што е неефикасно и бавно.
+
+=== Implementation ===
+
+==== Triggers ====
+
+Тригер за автоматско предупредување при висока искористеност на процесорот.
+Кога во `computer_history` се вметнува нов запис со CPU > 90%, автоматски се креира запис во `security_alerts`:
+
+{{{
+CREATE OR REPLACE FUNCTION create_cpu_alert()
+RETURNS TRIGGER AS
+$$
+BEGIN
+  IF NEW.cpu_usage > 90 THEN
+    INSERT INTO security_alerts(
+      id, computer_id, alert_type, severity,
+      description, timestamp, resolved
+    )
+    VALUES (
+      gen_random_uuid()::text,
+      NEW.computer_id::text,
+      'High CPU Usage',
+      'HIGH',
+      'CPU usage exceeded 90%',
+      NOW()::text,
+      'false'
+    );
+  END IF;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER cpu_alert_trigger
+AFTER INSERT ON computer_history
+FOR EACH ROW
+EXECUTE FUNCTION create_cpu_alert();
+}}}
+
+Тригер за автоматско предупредување при висока искористеност на RAM меморијата.
+Кога во `computer_history` се вметнува нов запис со RAM > 90%, автоматски се креира запис во `security_alerts`:
+
+{{{
+CREATE OR REPLACE FUNCTION create_ram_alert()
+RETURNS TRIGGER AS
+$$
+BEGIN
+  IF NEW.ram_usage > 90 THEN
+    INSERT INTO security_alerts(
+      id, computer_id, alert_type, severity,
+      description, timestamp, resolved
+    )
+    VALUES (
+      gen_random_uuid()::text,
+      NEW.computer_id::text,
+      'High RAM Usage',
+      'HIGH',
+      'RAM usage exceeded 90%',
+      NOW()::text,
+      'false'
+    );
+  END IF;
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER ram_alert_trigger
+AFTER INSERT ON computer_history
+FOR EACH ROW
+EXECUTE FUNCTION create_ram_alert();
+}}}
+
+==== Stored Procedures/Functions ====
+
+Процедура за детекција на сомнителна активност врз основа на Sysmon настани.
+Ако еден компјутер генерирал повеќе од 50 Sysmon настани во последниот час, автоматски се внесува безбедносно предупредување. Оваа процедура може да се извршува периодично (на пр. преку `pg_cron`):
+
+{{{
+CREATE OR REPLACE PROCEDURE detect_security_anomalies()
+LANGUAGE plpgsql AS
+$$
+BEGIN
+  INSERT INTO security_alerts(
+    id, computer_id, alert_type, severity,
+    description, timestamp, resolved
+  )
+  SELECT
+    gen_random_uuid()::text,
+    computer_id::text,
+    'Suspicious Activity',
+    'HIGH',
+    'More than 50 Sysmon events in the last hour',
+    NOW()::text,
+    'false'
+  FROM sysmon_events
+  WHERE CAST(timestamp AS timestamp) > NOW() - INTERVAL '1 hour'
+  GROUP BY computer_id
+  HAVING COUNT(*) > 50;
+END;
+$$;
+}}}
+
+==== Views ====
+
+Поглед за безбедносен преглед на системот.
+Обезбедува брз преглед на вкупниот број Sysmon настани по компјутер, корисен за администраторите при секојдневен мониторинг:
+
+{{{
+CREATE VIEW security_summary_view AS
+SELECT
+  c.name,
+  COUNT(s.id) AS total_events
+FROM computers c
+LEFT JOIN sysmon_events s ON c.id = s.computer_id
+GROUP BY c.name;
+}}}
+
+----
+
+== Автоматско одржување и чистење на историски податоци ==
+
+Со текот на времето, табелите со историски податоци (`sysmon_events`, `computer_history`) акумулираат голем број записи кои ги успоруваат пребарувањата и ја зголемуваат потрошувачката на дисковен простор. Потребен е механизам за автоматско бришење на застарени записи без рачна интервенција, со цел одржување на перформансите на системот на долг рок.
+
+=== Implementation ===
+
+==== Stored Procedures/Functions ====
+
+Процедура за автоматско отстранување на стари Sysmon логови (постари од 90 дена):
+
+{{{
+CREATE OR REPLACE PROCEDURE cleanup_old_sysmon_events()
+LANGUAGE plpgsql AS
+$$
+BEGIN
+  DELETE FROM sysmon_events
+  WHERE CAST(timestamp AS timestamp) < NOW() - INTERVAL '90 days';
+END;
+$$;
+}}}
+
+Процедура за автоматско отстранување на стари записи од `computer_history` (постари од 180 дена):
+
+{{{
+CREATE OR REPLACE PROCEDURE cleanup_old_computer_history()
+LANGUAGE plpgsql AS
+$$
+BEGIN
+  DELETE FROM computer_history
+  WHERE CAST(timestamp AS timestamp) < NOW() - INTERVAL '180 days';
+END;
+$$;
+}}}
+
+Двете процедури може да се закажат за периодично извршување преку `pg_cron`:
+
+{{{
+-- Извршување секој ден во полноќ
+SELECT cron.schedule('0 0 * * *', $$CALL cleanup_old_sysmon_events()$$);
+SELECT cron.schedule('0 1 * * *', $$CALL cleanup_old_computer_history()$$);
+}}}
+
+----
+
+== Аналитички погледи за брз пристап до системски информации ==
+
+Администраторите и апликацијата честопати бараат консолидирани информации за состојбата на компјутерите и нивната активност. Директното пресметување на овие информации при секое барање е бавно кога базата содржи голем број записи. Погледите и материјализираните погледи овозможуваат побрз пристап до вакви статистики.
+
+=== Implementation ===
+
+==== Views ====
+
+Детален поглед на компјутерите.
+Консолидиран приказ на основните информации за сите компјутери во системот, без потреба од повторување на JOIN логиката во апликацијата:
+
+{{{
+CREATE VIEW computer_details_view AS
+SELECT
+  name,
+  "user",
+  ip,
+  os,
+  env_name,
+  last_seen
+FROM computers;
+}}}
+
+Поглед за активни (online) компјутери.
+Прикажува само компјутери кои биле активни во последните 5 минути, корисен за real-time dashboard:
+
+{{{
+CREATE VIEW active_computers_view AS
+SELECT
+  c.id,
+  c.name,
+  c.ip,
+  c.env_name,
+  c.last_seen
+FROM computers c
+WHERE CAST(c.last_seen AS timestamp) >= NOW() - INTERVAL '5 minutes';
+}}}
+
+==== Materialized Views ====
+
+Материјализиран поглед за најактивни компјутери.
+За разлика од обичните погледи, резултатите се физички зачувани, што овозможува значително побрзо извршување на аналитички пребарувања врз голем број историски записи:
+
+{{{
+CREATE MATERIALIZED VIEW most_active_computers AS
+SELECT
+  c.id,
+  c.name,
+  COUNT(h.id) AS total_logs
+FROM computers c
+JOIN computer_history h ON c.id = h.computer_id
+GROUP BY c.id, c.name;
+}}}
+
+Рачно освежување на материјализираниот поглед (може да се закаже периодично):
+
+{{{
+REFRESH MATERIALIZED VIEW most_active_computers;
+}}}
+
+----
+
+== Статистички извештаи по околини (environments) ==
+
+Администраторите треба да имаат увид во тоа колку компјутери се регистрирани во секоја околина, со цел следење на растот на системот и планирање на капацитети. Оваа информација е корисна за квартални извештаи и при носење одлуки за скалирање.
+
+=== Implementation ===
+
+==== Stored Procedures/Functions ====
+
+Процедура за генерирање статистика по околини.
+Ја пресметува и ја испечатува (RAISE NOTICE) распределбата на компјутери по секоја околина:
+
+{{{
+CREATE OR REPLACE PROCEDURE environment_statistics()
+LANGUAGE plpgsql AS
+$$
+DECLARE
+  r RECORD;
+BEGIN
+  FOR r IN (
+    SELECT env_name, COUNT(*) AS total
+    FROM computers
+    GROUP BY env_name
+  )
+  LOOP
+    RAISE NOTICE 'Environment: %, Computers: %', r.env_name, r.total;
+  END LOOP;
+END;
+$$;
+}}}
+
+Функција која враќа статистика по околини во форма на табела (погодна за употреба во апликацијата):
+
+{{{
+CREATE OR REPLACE FUNCTION get_environment_statistics()
+RETURNS TABLE(env_name TEXT, total_computers BIGINT)
+LANGUAGE plpgsql AS
+$$
+BEGIN
+  RETURN QUERY
+  SELECT
+    c.env_name,
+    COUNT(*)::BIGINT AS total_computers
+  FROM computers c
+  GROUP BY c.env_name
+  ORDER BY total_computers DESC;
+END;
+$$;
+}}}
+
+Повикување на функцијата:
+
+{{{
+SELECT * FROM get_environment_statistics();
+}}}