如何将SNORT日志导入MySQL（PhpMyAdmin）并按规则调用指定字段？

Got it, let's break this down into two clear parts: first getting Snort to send logs to a MySQL database (which you can manage via phpMyAdmin), then querying the exact data fields you need. We'll use Barnyard2 for the log-to-database step—it's the standard tool to efficiently parse Snort's raw logs and insert them into MySQL.

Step 1: Install Required Tools #

First, make sure you have all dependencies set up. On Debian/Ubuntu-based systems:

sudo apt update && sudo apt install snort barnyard2 libmysqlclient-dev mysql-server

On RHEL/CentOS:

sudo yum install snort barnyard2 mysql-community-server mysql-devel

Step 2: Set Up the MySQL Database & User #

1. Log into your MySQL server:

mysql -u root -p

2. Create a dedicated Snort database, user, and grant necessary permissions:

CREATE DATABASE snort;
CREATE USER 'snortuser'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON snort.* TO 'snortuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

3. Import Snort's default database schema (the path might vary based on your installation—use find / -name create_mysql.sql if you can't locate it):

mysql -u snortuser -p snort < /usr/share/snort/schemas/create_mysql.sql

Step 3: Configure Snort to Generate Unified2 Logs #

Edit your Snort config file (usually /etc/snort/snort.conf):

1. Find the output unified2 line and update it to:

output unified2: filename /var/log/snort/snort.log, limit 128

This tells Snort to write raw, parseable logs to the specified path.
2. Save the file and restart Snort:

sudo systemctl restart snort

Step 4: Configure Barnyard2 to Sync Logs to MySQL #

Edit the Barnyard2 config file (usually /etc/snort/barnyard2.conf):

1. Add or update these lines to connect to your MySQL database:

output database: log, mysql, user=snortuser password=your_secure_password dbname=snort host=localhost

2. Point Barnyard2 to the unified2 logs you set in Snort:

input unified2: filename /var/log/snort/snort.log, delete_on_close

3. Start Barnyard2 and set it to run on boot:

sudo systemctl start barnyard2
sudo systemctl enable barnyard2

Test this by running a quick port scan against your Snort host—you should see new entries pop up in the MySQL database when you check via phpMyAdmin.

Snort stores data across multiple tables, so we'll join them to pull together all the fields you want. You can run this query directly in phpMyAdmin's SQL tab.

Key Tables to Know: #

• event: Stores core event data like timestamps and signature IDs
• iphdr: Holds source/destination IPs (stored as integers, so we'll convert them)
• tcphdr/udphdr: Store TCP and UDP port numbers respectively
• signature: Contains the alert message text

Example SQL Query #

In phpMyAdmin, select the snort database, open the SQL tab, and run this:

SELECT
  e.timestamp AS `Timestamp`,
  INET_NTOA(ip.src_ip) AS `Source IP`,
  INET_NTOA(ip.dst_ip) AS `Destination IP`,
  -- Get source port (handles TCP and UDP)
  CASE
    WHEN ip.protocol = 6 THEN tp.src_port
    WHEN ip.protocol = 17 THEN up.src_port
    ELSE 'N/A'
  END AS `Source Port`,
  -- Get destination port (handles TCP and UDP)
  CASE
    WHEN ip.protocol = 6 THEN tp.dst_port
    WHEN ip.protocol = 17 THEN up.dst_port
    ELSE 'N/A'
  END AS `Destination Port`,
  s.signature AS `Message`
FROM event e
JOIN iphdr ip ON e.cid = ip.cid AND e.sid = ip.sid
LEFT JOIN tcphdr tp ON e.cid = tp.cid AND e.sid = tp.sid
LEFT JOIN udphdr up ON e.cid = up.cid AND e.sid = up.sid
JOIN signature s ON e.signature = s.sig_id
ORDER BY e.timestamp DESC;

Quick Notes: #

• INET_NTOA() converts the integer-formatted IPs from the iphdr table into human-readable strings.
• The CASE statements handle both TCP (protocol 6) and UDP (protocol 17) ports—for ICMP or other protocols, ports will show as "N/A".
• Sorting by timestamp DESC gives you the most recent alerts first.

You can save this query as a view in phpMyAdmin for quick access, or use it in scripts (like PHP or Python) to automate data extraction.

内容的提问来源于stack exchange，提问作者Sam