SELECT routine_schema from INFORMATION_SCHEMA.routines where routine_name = 'wfwl_inet_pton'
SELECT count(*) FROM webfwlog.services LIMIT 1
SHOW COLUMNS FROM webfwlog.hostnames LIKE 'ip_addr';
SELECT definition FROM webfwlog.reports WHERE code ='basic'
SELECT oob_family FROM ulogd.ulog LIMIT 1;
SELECT ip_saddr_bin FROM ulogd.ulog LIMIT 1;
SHOW COLUMNS FROM ulogd.ulog LIKE 'ip_saddr_bin';
SHOW COLUMNS FROM ulogd.ulog LIKE 'icmp_gateway';
SELECT local_time FROM ulogd.ulog LIMIT 1
SELECT oob_family FROM ulogd.ulog LIMIT 1
SELECT _id FROM ulogd.ulog LIMIT 1
SELECT mac_str FROM ulogd.ulog LIMIT 1
SELECT last_accessed FROM webfwlog.reports
UPDATE webfwlog.reports SET last_accessed = 1781134212 WHERE code = 'basic'
SHOW COLUMNS FROM ulogd.ulog;
CREATE TEMPORARY TABLE webfwlog.allrows AS
SELECT
CAST('ulogd.ulog' AS char(32)) AS sname,
t.ip_saddr_bin AS `ip_saddr`,
t.ip_daddr_bin AS `ip_daddr`,
t.*
FROM ulogd.ulog AS t
WHERE (t.oob_time_sec IS NOT NULL AND t.oob_time_sec>=1780984800)
AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781134212)
AND (t.ip_protocol<>6 OR (t.tcp_syn OR t.tcp_fin OR t.tcp_ack OR t.tcp_rst OR t.tcp_psh OR t.tcp_urg))
SHOW COLUMNS FROM ulogd.ulog;
CREATE TEMPORARY TABLE webfwlog.tmp_output AS
SELECT
count(*) AS `count_t`,
count(*) AS `Count`,
oob_prefix AS `Label`,
CASE WHEN t.ip_protocol=6 THEN 'tcp'
WHEN t.ip_protocol=17 THEN 'udp'
WHEN t.ip_protocol=1 THEN 'icmp'
WHEN t.ip_protocol=58 THEN 'ipv6-icmp'
ELSE t.ip_protocol
END
AS `Proto`,
wfwl_inet_ntop(t.oob_family, ip_saddr_bin) AS `Source IP`,
wfwl_inet_ntop(t.oob_family, ip_daddr_bin) AS `Destination IP`,
CASE WHEN t.ip_protocol=6 THEN tcp_dport
WHEN t.ip_protocol=17 THEN udp_dport
ELSE 65536
END
AS `Dest Port`,
if(t.ip_protocol<>6,'',
if(tcp_syn AND NOT (tcp_urg OR tcp_psh OR tcp_rst OR tcp_ack OR tcp_fin),' SYN ',
concat(
if( tcp_syn, 's', '-'),
if( tcp_ack, 'a', '-'),
if( tcp_fin, 'f', '-'),
if( tcp_rst, 'r', '-'),
if( tcp_psh, 'p', '-'),
if( tcp_urg, 'u', '-'))))
AS `Options`
FROM webfwlog.allrows t
GROUP BY `Label`,
t.ip_protocol,
ip_saddr_bin,
ip_daddr_bin,
`Dest Port`,
`Options`
ORDER BY t.ip_protocol DESC,
`Dest Port` ASC,
ip_saddr_bin ASC
;
SELECT count(*) AS `count` FROM webfwlog.allrows
SELECT sum(`count_t`) AS `count` FROM webfwlog.tmp_output
SELECT count(*) AS `count` FROM webfwlog.tmp_output
CREATE TEMPORARY TABLE webfwlog.output AS
SELECT * FROM webfwlog.tmp_output LIMIT 0, 20;