匹配mysql表中的不同列sport和dport

Question

我有一个表，其中包含要存储的UDP数据包以供分析。我想搜索服务器未请求的数据包。

例如，如果我从源端口106和dst端口32767接收到UDP数据包，但从未从我的IP地址发送数据包

这是表结构。

mysql>show columns from packets;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| stime  | datetime         | YES  |     | NULL    |       | 
| sip    | int(10) unsigned | YES  |     | NULL    |       | 
| dip    | int(10) unsigned | YES  |     | NULL    |       | 
| sport  | smallint(6)      | YES  |     | NULL    |       | 
| dport  | smallint(6)      | YES  |     | NULL    |       | 
| in_out | tinyint(4)       | YES  |     | NULL    |       | 
| id     | smallint(6)      | YES  |     | NULL    |       | 
+--------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> select * from packets limit 5;
+---------------------+------------+------------+-------+-------+--------+-------+
| stime               | sip        | dip        | sport | dport | in_out | id    |
+---------------------+------------+------------+-------+-------+--------+-------+
| 2012-12-03 20:59:45 |  167837953 | 3406507329 |  1029 |    53 |      0 | 32767 | 
| 2012-12-03 20:59:45 | 3406507329 |  167837953 |    53 |  1029 |      1 | 32767 | 
| 2012-12-03 20:59:46 | 3406507329 |  167837953 |    53 |  1029 |      1 | 32767 | 
| 2012-12-03 20:59:46 | 2399874369 |  167837953 |    53 | 32321 |      1 |  3000 | 
| 2012-12-03 21:00:02 | 2064330049 |  167837953 |    53 |  3349 |      1 |  1207 | 
+---------------------+------------+------------+-------+-------+--------+-------+
5 rows in set (0.00 sec)

在上面的示例中，最后两个包是我应该能够通过查询找到的未经请求的包。基本上，如果我得到一个与(source,dest,sourceport,dstport,id)元组不匹配的数据包，我需要列出Mysql表中的所有行。

谢谢Vijay

Answer 1

这应该可以做到：

SELECT *
FROM packets
LEFT JOIN packets_2 ON (
    packets.sip = packets_2.dip
    AND packets.dip = packets_2.sip
    AND packets.sport = packets_2.dport
    AND packets.dport = packets_2.sport
    AND packets.id = packets_2.id
    AND packets.stime <= packets_2.stime -- I think this makes sense
)
WHERE packets_2.id IS NULL