[netfilter-cvslog] r7513 - branches/ulog/ulogd2/doc

[pablo at netfilter.org]

Author: pablo at netfilter.org
Date: 2008-04-27 09:54:05 +0200 (Sun, 27 Apr 2008)
New Revision: 7513

Modified:
   branches/ulog/ulogd2/doc/mysql-ulogd2.sql
Log:
Introduce function to convert binary data to printable strings.
Update view_tcp_quad and view_udp_quad.

Signed-off-by: Pierre Chifflier <chifflier at inl.fr>


Modified: branches/ulog/ulogd2/doc/mysql-ulogd2.sql
===================================================================
--- branches/ulog/ulogd2/doc/mysql-ulogd2.sql	2008-04-27 07:52:49 UTC (rev 7512)
+++ branches/ulog/ulogd2/doc/mysql-ulogd2.sql	2008-04-27 07:54:05 UTC (rev 7513)
@@ -220,13 +220,48 @@
 
 
 -- shortcuts
+DROP FUNCTION IF EXISTS BIN_TO_IPV6;
+delimiter $$
+CREATE FUNCTION BIN_TO_IPV6(
+		_in binary(16)
+                ) RETURNS varchar(64)
+SQL SECURITY INVOKER
+DETERMINISTIC
+COMMENT 'Convert binary ip to printable string'
+BEGIN
+	-- IPv4 address in IPv6 form
+	IF HEX(SUBSTRING(_in, 1, 12)) = '00000000000000000000FFFF' THEN
+		RETURN CONCAT(
+			'::ffff:',
+			ASCII(SUBSTRING(_in, 13, 1)), '.',
+			ASCII(SUBSTRING(_in, 14, 1)), '.',
+			ASCII(SUBSTRING(_in, 15, 1)), '.',
+			ASCII(SUBSTRING(_in, 16, 1))
+		);
+	END IF;
+	-- return the full IPv6 form
+	RETURN LOWER(CONCAT(
+		HEX(SUBSTRING(_in,  1, 2)), ':',
+		HEX(SUBSTRING(_in,  3, 2)), ':',
+		HEX(SUBSTRING(_in,  5, 2)), ':',
+		HEX(SUBSTRING(_in,  7, 2)), ':',
+		HEX(SUBSTRING(_in,  9, 2)), ':',
+		HEX(SUBSTRING(_in, 11, 2)), ':',
+		HEX(SUBSTRING(_in, 13, 2)), ':',
+		HEX(SUBSTRING(_in, 15, 2))
+	));
+END
+$$
+delimiter ;
+
+
 DROP VIEW IF EXISTS `view_tcp_quad`;
 CREATE SQL SECURITY INVOKER VIEW `view_tcp_quad` AS
-        SELECT ulog2._id,ulog2.ip_saddr,tcp.tcp_sport,ulog2.ip_daddr,tcp.tcp_dport FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id;
+	SELECT ulog2._id,BIN_TO_IPV6(ulog2.ip_saddr) AS ip_saddr_str,tcp.tcp_sport,BIN_TO_IPV6(ulog2.ip_daddr) AS ip_daddr_str,tcp.tcp_dport FROM ulog2 INNER JOIN tcp ON ulog2._id = tcp._tcp_id;
 
 DROP VIEW IF EXISTS `view_udp_quad`;
 CREATE SQL SECURITY INVOKER VIEW `view_udp_quad` AS
-        SELECT ulog2._id,ulog2.ip_saddr,udp.udp_sport,ulog2.ip_daddr,udp.udp_dport FROM ulog2 INNER JOIN udp ON ulog2._id = udp._udp_id;
+	SELECT ulog2._id,BIN_TO_IPV6(ulog2.ip_saddr) AS ip_saddr_str,udp.udp_sport,BIN_TO_IPV6(ulog2.ip_daddr) AS ip_daddr_str,udp.udp_dport FROM ulog2 INNER JOIN udp ON ulog2._id = udp._udp_id;