Увольнение (Dismissal)

Platform: hackerlab | Category: Forensics | Type: Challenge | Difficulty: Medium | OS: Linux | Author: XESXOR | Date: 2026-01-06 | Status: Solved Techniques: sql_join_queries, group_by_aggregation, access_log_analysis, timestamp_conversion

Summary

Translation: "Recently our company was subjected to physical intrusion. In 1 day, the intruder visited all classrooms using one key card. Find the name, key card number, and time when he entered the building."

Увольнение (Dismissal) — HackerLab

Description

Недавно наша компания подверглась физическому проникновению. За 1 день нарушитель побывал во всех аудиториях с помощью одной ключ-карты. Найдите имя, номер ключ-карты и время, когда он проник в здание.

Translation: "Recently our company was subjected to physical intrusion. In 1 day, the intruder visited all classrooms using one key card. Find the name, key card number, and time when he entered the building."

Flag Format: CODEBY{Name_Surname_CardID_Timestamp} ‍​‌‌​​​​‌​‌‌​​‌‌​​‌‌​​‌‌​​‌‌​​‌​​​​‌‌​​​​​‌‌​​‌‌​​‌‌​​‌​‌​‌‌​​‌​‌‍

Analysis

Step 1: File Analysis

The downloaded archive dismissal.zip contained a single file — an SQLite database:

 bash read-only • copy allowed Copy
$ file sqlite.db
sqlite.db: SQLite 3.x database

Step 2: Database Structure

The database contains 4 tables:

 sql read-only • copy allowed Copy
-- View structure
.schema

-- Count records
SELECT COUNT(*) FROM users;        -- 32 employees
SELECT COUNT(*) FROM visits;       -- entry/exit records
SELECT COUNT(*) FROM rooms_visits; -- room visit records
SELECT COUNT(*) FROM card_keys;    -- card assignments

Step 3: Finding the Intruder

Key condition: the intruder visited ALL 32 rooms in ONE day using ONE card.

 sql read-only • copy allowed Copy
SELECT 
    ck.CARD_ID,
    u.NAME,
    COUNT(DISTINCT rv.ROOM) as rooms_count,
    date(rv.TIME, 'unixepoch') as visit_date
FROM rooms_visits rv
JOIN card_keys ck ON rv.CARD_ID = ck.CARD_ID
JOIN users u ON ck.USER_ID = u.ID
GROUP BY ck.CARD_ID, date(rv.TIME, 'unixepoch')
HAVING COUNT(DISTINCT rv.ROOM) = 32;

‍​‌‌​​​​‌​‌‌​​‌‌​​‌‌​​‌‌​​‌‌​​‌​​​​‌‌​​​​​‌‌​​‌‌​​‌‌​​‌​‌​‌‌​​‌​‌‍

Result:

Intruder: Semyon Adraha, card 9d1274

Step 4: Building Entry Time

Finding Semyon Adraha's building entry time on 2022-09-01:

 sql read-only • copy allowed Copy
-- First find USER_ID
SELECT ID FROM users WHERE NAME = 'Semyon Adraha';
-- Result: 12

-- Building entry time
SELECT v.TIME, datetime(v.TIME, 'unixepoch') as human_time
FROM visits v
WHERE v.USER_ID = 12 
  AND date(v.TIME, 'unixepoch') = '2022-09-01' 
  AND v.IN_BASE = 1;

‍​‌‌​​​​‌​‌‌​​‌‌​​‌‌​​‌‌​​‌‌​​‌​​​​‌‌​​​​​‌‌​​‌‌​​‌‌​​‌​‌​‌‌​​‌​‌‍

Result:

Solution

 sql read-only • copy allowed Copy
-- Full query to find the intruder
WITH intruder AS (
    SELECT 
        ck.CARD_ID,
        ck.USER_ID,
        u.NAME,
        date(rv.TIME, 'unixepoch') as intrusion_date
    FROM rooms_visits rv
    JOIN card_keys ck ON rv.CARD_ID = ck.CARD_ID
    JOIN users u ON ck.USER_ID = u.ID
    GROUP BY ck.CARD_ID, date(rv.TIME, 'unixepoch')
    HAVING COUNT(DISTINCT rv.ROOM) = 32
)
SELECT 
    i.NAME,
    i.CARD_ID,
    v.TIME as entry_timestamp,
    datetime(v.TIME, 'unixepoch') as entry_time
FROM intruder i
JOIN visits v ON v.USER_ID = i.USER_ID
WHERE date(v.TIME, 'unixepoch') = i.intrusion_date
  AND v.IN_BASE = 1;

‍​‌‌​​​​‌​‌‌​​‌‌​​‌‌​​‌‌​​‌‌​​‌​​​​‌‌​​​​​‌‌​​‌‌​​‌‌​​‌​‌​‌‌​​‌​‌‍

Result:

• Name: REDACTED
• Card: 9d1274
• Timestamp: 1662002861

Lessons

This task requires understanding of:

1. Relational database structure — relationships between tables via foreign keys
2. SQL aggregation — GROUP BY + HAVING for finding anomalies
3. Date handling — converting Unix timestamp to human-readable format
4. Access control logic — understanding that IN_BASE=1 means building entry

A typical forensics task on analyzing access control system (ACS) logs. ‍​‌‌​​​​‌​‌‌​​‌‌​​‌‌​​‌‌​​‌‌​​‌​​​​‌‌​​​​​‌‌​​‌‌​​‌‌​​‌​‌​‌‌​​‌​‌‍