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 ='udpports'
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 = 1781517549 WHERE code = 'udpports'
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>=1780912749)
AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781517549)
AND (t.ip_protocol=17)
AND (NOT (t.ip_protocol=17) OR NOT (t.udp_dport BETWEEN 1026 AND 1029))
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`,
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_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`,
CASE WHEN t.ip_protocol=6 THEN
CASE WHEN s3.service IS NULL
THEN '-'
ELSE s3.service
END
WHEN t.ip_protocol=17 THEN
CASE WHEN s4.service IS NULL
THEN '-'
ELSE s4.service
END
ELSE '-'
END
AS `Dest Service`
FROM webfwlog.allrows t
LEFT JOIN webfwlog.services AS s3 ON t.ip_protocol = s3.ip_protocol AND t.tcp_dport = s3.port
LEFT JOIN webfwlog.services AS s4 ON t.ip_protocol = s4.ip_protocol AND t.udp_dport = s4.port
GROUP BY t.ip_protocol,
ip_daddr_bin,
`Dest Port`,
`Dest Service`
ORDER BY `Dest Port` 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;