Security Alert Management

Objective

Create a comprehensive security alert management system for tracking alerts, managing notifications, and coordinating response workflows across security operations.

Step 1: Create Alert Types Table

Define alert categories.

CREATE TABLE alert_types (
    id INTEGER PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100),
    category VARCHAR(50),
    default_priority VARCHAR(20),
    default_sla_minutes INTEGER,
    auto_escalate BOOLEAN DEFAULT FALSE,
    escalation_minutes INTEGER,
    notification_template TEXT,
    is_active BOOLEAN DEFAULT TRUE
);

Step 2: Create Alert Sources Table

Track alert origins.

CREATE TABLE alert_sources (
    id INTEGER PRIMARY KEY,
    source_id VARCHAR(50) NOT NULL UNIQUE,
    source_name VARCHAR(200),
    source_type VARCHAR(50),
    location VARCHAR(200),
    zone VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    last_alert_time TIMESTAMP,
    total_alerts INTEGER DEFAULT 0
);

Step 3: Create Security Alerts Table

Store alert records.

CREATE TABLE security_alerts (
    id INTEGER PRIMARY KEY,
    alert_uid VARCHAR(100) NOT NULL UNIQUE,
    alert_type_id INTEGER NOT NULL,
    source_id INTEGER NOT NULL,
    alert_time TIMESTAMP NOT NULL,
    priority VARCHAR(20),
    title VARCHAR(255),
    description TEXT,
    alert_data TEXT,
    snapshot IMAGE(JPEG),
    video_clip VIDEO(MP4),
    status VARCHAR(50) DEFAULT 'new',
    assigned_to VARCHAR(100),
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(100),
    acknowledged_at TIMESTAMP,
    resolved BOOLEAN DEFAULT FALSE,
    resolved_by VARCHAR(100),
    resolved_at TIMESTAMP,
    resolution_code VARCHAR(50),
    resolution_notes TEXT,
    false_positive BOOLEAN DEFAULT FALSE,
    sla_due_at TIMESTAMP,
    sla_breached BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (alert_type_id) REFERENCES alert_types(id),
    FOREIGN KEY (source_id) REFERENCES alert_sources(id)
);

Step 4: Create Alert Notifications Table

Track notification delivery.

CREATE TABLE alert_notifications (
    id INTEGER PRIMARY KEY,
    alert_id INTEGER NOT NULL,
    notification_type VARCHAR(50),
    recipient VARCHAR(200),
    recipient_role VARCHAR(100),
    channel VARCHAR(50),
    sent_at TIMESTAMP,
    delivered BOOLEAN DEFAULT FALSE,
    delivered_at TIMESTAMP,
    read_status BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP,
    response_action VARCHAR(100),
    FOREIGN KEY (alert_id) REFERENCES security_alerts(id)
);

Step 5: Create Alert Escalations Table

Track escalation history.

CREATE TABLE alert_escalations (
    id INTEGER PRIMARY KEY,
    alert_id INTEGER NOT NULL,
    escalation_level INTEGER,
    escalated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    escalated_to VARCHAR(100),
    escalation_reason VARCHAR(255),
    previous_assignee VARCHAR(100),
    FOREIGN KEY (alert_id) REFERENCES security_alerts(id)
);

Step 6: Create Alert Comments Table

Store investigation notes.

CREATE TABLE alert_comments (
    id INTEGER PRIMARY KEY,
    alert_id INTEGER NOT NULL,
    comment_text TEXT,
    comment_by VARCHAR(100),
    comment_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_internal BOOLEAN DEFAULT TRUE,
    attachment_type VARCHAR(50),
    attachment_file IMAGE(JPEG),
    FOREIGN KEY (alert_id) REFERENCES security_alerts(id)
);

Step 7: Insert Alert Types

Define alert categories.

INSERT INTO alert_types (id, type_code, type_name, category, default_priority, default_sla_minutes, auto_escalate, escalation_minutes) VALUES
    (1, 'MOTION_RESTRICTED', 'Motion in Restricted Area', 'intrusion', 'high', 5, TRUE, 10),
    (2, 'MOTION_AFTER_HOURS', 'After-Hours Motion', 'intrusion', 'medium', 15, TRUE, 30),
    (3, 'ACCESS_DENIED', 'Access Denied Alert', 'access_control', 'low', 30, FALSE, NULL),
    (4, 'ACCESS_TAILGATING', 'Tailgating Detected', 'access_control', 'high', 5, TRUE, 10),
    (5, 'DOOR_HELD_OPEN', 'Door Held Open', 'access_control', 'medium', 10, TRUE, 20),
    (6, 'CAMERA_OFFLINE', 'Camera Offline', 'system', 'medium', 15, TRUE, 30),
    (7, 'LPR_WATCHLIST', 'Watchlist Vehicle Detected', 'vehicle', 'critical', 2, TRUE, 5),
    (8, 'FACE_WATCHLIST', 'Watchlist Face Detected', 'biometric', 'critical', 2, TRUE, 5),
    (9, 'PANIC_BUTTON', 'Panic Button Activated', 'emergency', 'critical', 1, TRUE, 3),
    (10, 'FIRE_ALARM', 'Fire Alarm Triggered', 'emergency', 'critical', 1, TRUE, 2),
    (11, 'PERIMETER_BREACH', 'Perimeter Breach', 'intrusion', 'critical', 2, TRUE, 5),
    (12, 'LOITERING', 'Loitering Detected', 'behavioral', 'low', 30, FALSE, NULL);

Step 8: Insert Alert Sources

Add alert origins.

INSERT INTO alert_sources (id, source_id, source_name, source_type, location, zone, total_alerts) VALUES
    (1, 'CAM-SERVER-001', 'Server Room Camera', 'camera', 'Building A Floor 2', 'Restricted Zone', 45),
    (2, 'CAM-LOBBY-001', 'Lobby Camera', 'camera', 'Building A Ground', 'Common Area', 128),
    (3, 'CAM-PARK-001', 'Parking Lot Camera', 'camera', 'Parking Lot A', 'Parking Zone', 256),
    (4, 'ACC-MAIN-001', 'Main Entrance Access', 'access_control', 'Building A Ground', 'Entry Zone', 89),
    (5, 'ACC-SERVER-001', 'Server Room Access', 'access_control', 'Building A Floor 2', 'Restricted Zone', 23),
    (6, 'LPR-GATE-001', 'Main Gate LPR', 'lpr', 'Main Gate', 'Perimeter', 312),
    (7, 'FR-MAIN-001', 'Main Entrance FR', 'facial_recognition', 'Building A Ground', 'Entry Zone', 67),
    (8, 'PANIC-LOBBY-001', 'Lobby Panic Button', 'panic', 'Building A Ground', 'Common Area', 3),
    (9, 'FIRE-BLD-A', 'Building A Fire System', 'fire', 'Building A', 'Entire Building', 2),
    (10, 'FENCE-NORTH', 'North Fence Sensor', 'perimeter', 'North Perimeter', 'Perimeter', 18);

Step 9: Insert Security Alerts

Add sample alerts.

INSERT INTO security_alerts (id, alert_uid, alert_type_id, source_id, alert_time, priority, title, description, status, assigned_to, acknowledged, acknowledged_by, acknowledged_at, resolved, resolved_by, resolved_at, resolution_code, resolution_notes, false_positive, sla_due_at, sla_breached) VALUES
    (1, 'ALT-20240115-001', 1, 1, '2024-01-15 02:30:45', 'high', 'Motion in Server Room', 'Motion detected in server room during non-business hours', 'resolved', 'Security Team Lead', TRUE, 'Officer Jones', '2024-01-15 02:32:00', TRUE, 'Officer Jones', '2024-01-15 02:45:00', 'authorized', 'Scheduled maintenance by IT team verified', FALSE, '2024-01-15 02:35:45', FALSE),
    (2, 'ALT-20240115-002', 4, 4, '2024-01-15 08:45:22', 'high', 'Tailgating at Main Entrance', 'Multiple badge reads within 3 seconds at main entrance', 'resolved', 'Security Officer', TRUE, 'Officer Smith', '2024-01-15 08:46:00', TRUE, 'Officer Smith', '2024-01-15 08:55:00', 'genuine', 'Two employees entered together, policy reminder issued', FALSE, '2024-01-15 08:50:22', FALSE),
    (3, 'ALT-20240115-003', 7, 6, '2024-01-15 09:15:30', 'critical', 'Watchlist Vehicle at Gate', 'Vehicle on security watchlist detected at main gate', 'investigating', 'Security Manager', TRUE, 'Security Manager', '2024-01-15 09:16:00', FALSE, NULL, NULL, NULL, NULL, FALSE, '2024-01-15 09:17:30', FALSE),
    (4, 'ALT-20240115-004', 2, 2, '2024-01-15 22:30:00', 'medium', 'After-Hours Motion in Lobby', 'Motion detected in lobby after business hours', 'resolved', 'Night Security', TRUE, 'Officer Davis', '2024-01-15 22:32:00', TRUE, 'Officer Davis', '2024-01-15 22:40:00', 'false_alarm', 'Cleaning crew, verified on schedule', TRUE, '2024-01-15 22:45:00', FALSE),
    (5, 'ALT-20240115-005', 6, 2, '2024-01-15 14:22:18', 'medium', 'Camera Offline', 'Lobby camera lost connection', 'resolved', 'IT Support', TRUE, 'IT Tech', '2024-01-15 14:25:00', TRUE, 'IT Tech', '2024-01-15 14:45:00', 'technical', 'Network cable reconnected', FALSE, '2024-01-15 14:37:18', FALSE),
    (6, 'ALT-20240115-006', 5, 5, '2024-01-15 10:05:00', 'medium', 'Server Room Door Held Open', 'Server room door held open beyond threshold', 'resolved', 'Security Officer', TRUE, 'Officer Jones', '2024-01-15 10:06:00', TRUE, 'Officer Jones', '2024-01-15 10:10:00', 'genuine', 'Equipment delivery, supervised entry logged', FALSE, '2024-01-15 10:15:00', FALSE),
    (7, 'ALT-20240115-007', 12, 3, '2024-01-15 15:45:00', 'low', 'Loitering in Parking Lot', 'Individual loitering near building entrance for 15+ minutes', 'new', NULL, FALSE, NULL, NULL, FALSE, NULL, NULL, NULL, NULL, FALSE, '2024-01-15 16:15:00', FALSE),
    (8, 'ALT-20240115-008', 11, 10, '2024-01-15 03:22:45', 'critical', 'North Fence Breach', 'Perimeter sensor triggered on north fence', 'resolved', 'Response Team', TRUE, 'Team Lead', '2024-01-15 03:23:30', TRUE, 'Team Lead', '2024-01-15 03:35:00', 'false_alarm', 'Wildlife (deer) confirmed on camera', TRUE, '2024-01-15 03:24:45', FALSE);

Step 10: Insert Notifications

Track notification delivery.

INSERT INTO alert_notifications (id, alert_id, notification_type, recipient, recipient_role, channel, sent_at, delivered, delivered_at, read_status, read_at, response_action) VALUES
    (1, 1, 'initial', 'security-team@company.com', 'Security Team', 'email', '2024-01-15 02:30:48', TRUE, '2024-01-15 02:30:50', TRUE, '2024-01-15 02:31:30', 'acknowledged'),
    (2, 1, 'initial', '+1-555-0101', 'On-Duty Officer', 'sms', '2024-01-15 02:30:48', TRUE, '2024-01-15 02:30:49', TRUE, '2024-01-15 02:31:00', 'responded'),
    (3, 3, 'initial', 'security-manager@company.com', 'Security Manager', 'email', '2024-01-15 09:15:32', TRUE, '2024-01-15 09:15:34', TRUE, '2024-01-15 09:15:50', 'acknowledged'),
    (4, 3, 'initial', '+1-555-0100', 'Security Manager', 'sms', '2024-01-15 09:15:32', TRUE, '2024-01-15 09:15:33', TRUE, '2024-01-15 09:15:45', 'responded'),
    (5, 3, 'initial', 'Security Operations', 'SOC', 'push', '2024-01-15 09:15:32', TRUE, '2024-01-15 09:15:33', TRUE, '2024-01-15 09:15:40', 'viewed'),
    (6, 8, 'initial', 'response-team@company.com', 'Response Team', 'email', '2024-01-15 03:22:48', TRUE, '2024-01-15 03:22:50', TRUE, '2024-01-15 03:23:00', 'acknowledged'),
    (7, 8, 'initial', '+1-555-0102', 'Response Team Lead', 'sms', '2024-01-15 03:22:48', TRUE, '2024-01-15 03:22:49', TRUE, '2024-01-15 03:23:10', 'responded');

Step 11: Insert Alert Comments

Add investigation notes.

INSERT INTO alert_comments (id, alert_id, comment_text, comment_by, is_internal) VALUES
    (1, 1, 'Checked camera footage - IT maintenance team present with work order #IT-2024-0115', 'Officer Jones', TRUE),
    (2, 1, 'Verified with IT Manager - scheduled maintenance confirmed', 'Officer Jones', TRUE),
    (3, 3, 'Vehicle matched to banned former contractor. Security manager notified.', 'SOC Analyst', TRUE),
    (4, 3, 'Vehicle did not attempt entry - departed after 2 minutes at gate', 'Security Manager', TRUE),
    (5, 8, 'Response team dispatched immediately. ETA 2 minutes.', 'SOC Analyst', TRUE),
    (6, 8, 'On scene - no human presence detected. Reviewing camera footage.', 'Team Lead', TRUE),
    (7, 8, 'Camera footage shows deer crossing detection zone. False alarm confirmed.', 'Team Lead', TRUE);

Step 12: Get Active Alerts

List current alerts.

SELECT
    sa.alert_uid,
    at.type_name,
    asrc.source_name,
    sa.alert_time,
    sa.priority,
    sa.title,
    sa.status,
    sa.assigned_to,
    sa.acknowledged
FROM security_alerts sa
INNER JOIN alert_types at ON sa.alert_type_id = at.id
INNER JOIN alert_sources asrc ON sa.source_id = asrc.id
WHERE sa.resolved = FALSE
ORDER BY
    CASE sa.priority
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END,
    sa.alert_time DESC;

Step 13: Alert Response Time Analysis

Measure response performance.

SELECT
    at.type_name,
    at.default_sla_minutes as sla_target,
    COUNT(sa.id) as total_alerts,
    AVG(EXTRACT(EPOCH FROM (sa.acknowledged_at - sa.alert_time)) / 60) as avg_ack_minutes,
    COUNT(CASE WHEN sa.sla_breached THEN 1 END) as sla_breaches,
    COUNT(CASE WHEN sa.false_positive THEN 1 END) as false_positives
FROM security_alerts sa
INNER JOIN alert_types at ON sa.alert_type_id = at.id
WHERE sa.acknowledged = TRUE
GROUP BY at.id, at.type_name, at.default_sla_minutes
ORDER BY total_alerts DESC;

Step 14: Alert Volume by Source

Analyze alert patterns.

SELECT
    asrc.source_name,
    asrc.zone,
    asrc.source_type,
    COUNT(sa.id) as alert_count,
    COUNT(CASE WHEN sa.false_positive THEN 1 END) as false_positives,
    CAST(COUNT(CASE WHEN sa.false_positive THEN 1 END) AS DECIMAL) / NULLIF(COUNT(sa.id), 0) * 100 as false_positive_rate
FROM alert_sources asrc
LEFT JOIN security_alerts sa ON asrc.id = sa.source_id
    AND sa.alert_time >= CURRENT_DATE - 30
GROUP BY asrc.id, asrc.source_name, asrc.zone, asrc.source_type
ORDER BY alert_count DESC;

Step 15: Resolution Code Analysis

Review resolution patterns.

SELECT
    sa.resolution_code,
    COUNT(*) as count,
    AVG(EXTRACT(EPOCH FROM (sa.resolved_at - sa.alert_time)) / 60) as avg_resolution_min
FROM security_alerts sa
WHERE sa.resolved = TRUE
  AND sa.alert_time >= CURRENT_DATE - 30
GROUP BY sa.resolution_code
ORDER BY count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS alert_comments;
DROP TABLE IF EXISTS alert_escalations;
DROP TABLE IF EXISTS alert_notifications;
DROP TABLE IF EXISTS security_alerts;
DROP TABLE IF EXISTS alert_sources;
DROP TABLE IF EXISTS alert_types;

Expected Outcomes

• Alert types defined
• Alerts tracked
• Notifications logged
• Response measured
• Patterns analyzed

Priority Levels

Key Concepts Learned

• Alert categorization
• SLA management
• Notification tracking
• Response analysis
• False positive tracking