Mini-SIEM Log Correlation & Alert-Triage Lab for a College Lab Network (Python/MySQL)

LabNet SIEM: Log Correlation & Alert-Triage Lab is a defensive-security project that turns the scattered logs of a small college lab network into one correlated alert queue. Every Linux lab machine already writes logs — SSH and sudo activity in auth.log, web-server requests in access.log, cron jobs in syslog — but nobody reads them until something breaks. This project builds a miniature SIEM (Security Information and Event Management) system out of components the student controls end to end: a lightweight shipper that tails log files on each lab host, a Python parser that normalises every line into a common event schema, a MySQL event store, a correlation engine driven by human-readable YAML rules, and a Flask dashboard where alerts arrive with a severity, a MITRE ATT&CK mapping and an analyst verdict.

All detection happens on your own lab systems only — your VMs, your logs, your rules. The bundle includes a scripted activity generator that produces the log signatures the rules are designed to catch (rapid failed SSH logins, a short probe sequence, repeated 404 requests to the lab web server) so every rule can be demonstrated live in the viva without touching any machine you do not own.

Flask and Chart.js are industry-standard extras layered on top of the Python, MySQL and JavaScript skills the VTU MCA 2022 scheme teaches (22MCA21, 22MCA24, 22MCA31). The result is a project where every design decision — schema, rule DSL, window size, severity scale — is one you can explain and defend.

A college lab with sixty machines, one web server and one firewall emits tens of thousands of log lines every day, yet has no mechanism to notice when something in them matters. Repeated failed SSH logins, an unusual run of sudo commands on a non-teaching machine, or a scanner probing the lab web server all leave the same trace: ordinary lines in files nobody opens. By the time a problem is noticed — a full disk, a defaced intranet page, a lab machine behaving oddly — the supporting evidence is scattered across hosts, and there is no way to answer even basic questions such as which accounts attempted login, from which addresses, and when.

Commercial SIEM platforms solve this for enterprises, but a college cannot deploy one: licences are expensive, and more importantly a closed product teaches a student nothing about how normalisation, correlation and triage actually work. What is needed is a small, fully explainable, rule-driven SIEM that a department can run on its own hardware, that watches only its own systems, and that demonstrates the complete detection loop — collect, normalise, correlate, triage, tune — with every step visible and every rule arguable in a viva.

Abstract

A college lab generates thousands of log lines a day and reads none of them. Security events — repeated failed logins, unusual sudo use, a scanner probing the lab web server — leave evidence in files nobody opens until days later. This project builds a miniature SIEM: a shipper and parser that normalise events from several sources into one schema, a MySQL event store, a rule-driven correlation engine that raises alerts only when related events occur inside a time window, and a triage console where each alert is confirmed or dismissed, the verdict feeding back into rule tuning.

Introduction

A sixty-machine lab plus one web server produces four log streams: Linux auth.log, syslog, the Nginx/Apache access log and the lab firewall's connection log. Individually, each line is noise. Security value appears in correlation: five failed SSH logins from one address followed by a success is a story no single line tells. The project teaches exactly this skill — detection engineering — with nothing beyond the scheme's own courses.

Existing System

• Logs exist on each host but are never collected centrally; debugging means logging into each machine and grepping by hand.
• There is no alerting: a weekend burst of failed SSH logins goes unnoticed until Monday.
• There is no history and no evidence trail — questions like "who tried to log in, from where, when?" are unanswerable.
• Commercial SIEMs (Splunk, QRadar) are powerful but licence-heavy and opaque to a student who must explain every component.
• Limitations: no central store, no correlation, no triage workflow, no measurable false-positive rate.

Proposed System

• Collect: a small Python shipper tails configured log files per host and posts batches to a collector API.
• Normalise: per-source parser rules map every line into one event schema (timestamp, host, source, event_type, actor, src_ip, outcome, raw).
• Store: MySQL tables indexed on (host, ts) and (src_ip, ts), with daily partitions and a retention purge.
• Correlate: YAML rule files express threshold rules (N events of a type in T minutes), sequence rules (failures followed by success) and volume rules (4xx rate per address); each rule carries a severity and a MITRE ATT&CK technique id.
• Triage: a Flask console lists alerts with their context events inline; the analyst marks it True Positive, False Positive or Needs Review and the verdict is stored.
• Tune: a weekly report shows hits and false-positive rate per rule, closing the detection-tuning loop with evidence.

Feasibility Study

• Technical: Python 3, MySQL 8 and Flask are free, documented and light enough for two Linux VMs to demonstrate multi-host collection end to end.
• Economic: zero licence cost; everything runs on the department's own hardware.
• Operational: the admin gets one page each morning instead of six log files; the analyst workflow needs no training.
• Ethical and legal: all monitoring targets systems the department owns; no shared-network traffic is intercepted, and login usernames are the only personal data retained.

In scope

• Collection from Linux lab hosts by file tailing (auth.log, syslog) and from the lab web server's access log — file-based sources only.
• Six to ten correlation rules across credential-attack, probing and misuse categories, each mapped to a MITRE ATT&CK technique.
• A MySQL event store sized for a 180-day retention window, in line with CERT-In's log-retention direction of April 2022.
• A Flask triage dashboard, a weekly tuning report and CSV export of alerts for the department's records.
• A scripted activity generator for the lab's own test VMs to exercise rules safely and repeatably.

Out of scope (v1)

• Live packet capture or any monitoring of hosts the department does not own.
• Windows Event Log collection (kept as a future enhancement; Linux only in version 1).
• Machine-learning anomaly detection — rules are deliberately hand-written and explainable.
• Case management, ticketing integrations and email/SMS notification gateways.

The project follows an iterative-incremental SDLC model, aligned to VTU's two-phase MCA structure: Phase-1 (22MCAL35) covers analysis and design with a thin prototype, and Phase-2 (22MCA44) delivers the increments, testing and final evaluation.

Testing approach: pytest unit tests for every parser pattern and rule operator; integration tests that replay a recorded day of lab logs and assert the expected alert counts; a load test validating query performance at one million events; and a documented false-positive review in which every rule is scored and tuned. Because detection logic is data-driven, the same seeded activity is replayed after every rule change, so tuning results are regression-tested rather than anecdotal.

LabNet SIEM is built as a pipeline of five cooperating stages, each independently runnable and testable:

1. Collection — a lightweight Python shipper on each lab host tails configured log files, remembers byte offsets so nothing is re-sent after a restart, and POSTs batches to the collector with a shared token.
2. Normalisation — the parser maps each line to the common event schema (timestamp, host, source, event_type, actor, src_ip, outcome, raw). Unparsable lines are stored raw with a parse_failed flag rather than dropped, so parser improvements recover old data.
3. Storage — MySQL stores hosts, sources, events, rules, alerts and verdicts; daily partitions and composite indexes keep the correlation queries fast.
4. Correlation — a worker evaluates the YAML rules on rolling windows: threshold (count of an event type per key), sequence (ordered steps within T minutes) and volume (rate per key). Matches become alerts carrying severity and an ATT&CK reference.
5. Presentation — a Flask application serves the alert queue, event context and weekly report; triage verdicts are written back to MySQL.

flowchart LR
  H1["Lab host 1 (auth.log, syslog)"] --> SH1[Shipper]
  H2["Lab host 2 (auth.log)"] --> SH2[Shipper]
  W["Lab web server (access.log)"] --> SH3[Shipper]
  SH1 -->|HTTPS batches| C[Collector API]
  SH2 -->|HTTPS batches| C
  SH3 -->|HTTPS batches| C
  C --> P[Parser and normaliser]
  P --> DB[(MySQL event store)]
  Q[Correlation worker] -->|reads rules| R[rules.yaml]
  DB --> Q
  Q --> A[Alerts]
  DB --> CON[Flask triage console]
  A --> CON
  CON -->|verdicts| DB
  CON --> REP[Weekly tuning report]

ER diagram (core tables)

erDiagram
  HOST ||--o{ SOURCE : "runs"
  SOURCE ||--o{ EVENT : produces
  EVENT }o--|| RULE : "matched by"
  RULE ||--o{ ALERT : triggers
  ALERT ||--o{ TRIAGE_VERDICT : receives
  HOST {
    string id PK
    string hostname UK
    string ip
  }
  SOURCE {
    string id PK
    string host_id FK
    string log_path
    string parser
  }
  EVENT {
    bigint id PK
    datetime ts
    string host_id FK
    string source_id FK
    string event_type
    string actor
    string src_ip
    string outcome
    text raw
  }
  RULE {
    string id PK
    string name
    string rule_type
    int window_seconds
    int threshold
    string severity
    string mitre_id
  }
  ALERT {
    bigint id PK
    string rule_id FK
    datetime raised_at
    string key_value
    string status
  }
  TRIAGE_VERDICT {
    bigint id PK
    bigint alert_id FK
    string analyst
    string verdict
    string comment
  }

The design is deliberately event-sourced: alerts are derived views over stored events, so re-running a corrected rule over old data reproduces history. This property lets the report show before-and-after tuning results on identical data — a strong, honest evidence chain for the viva.

• Windows Event Log support via a small agent reading login-failure and login-success event patterns, widening coverage to the whole lab.
• Anomaly-assisted tuning — a per-account baseline of usual login hours and usual source hosts that ranks rules by deviation, kept simple and explainable.
• Ticketing hook — push confirmed True Positives to the department's helpdesk tool so triage becomes an auditable workflow.
• Elasticsearch backend option behind the same rule DSL, to compare MySQL and search-engine performance at similar volumes.
• Notification — a daily digest email to the lab administrator instead of console-only alerting.
• Docker Compose packaging so another college can adopt the lab in minutes.