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 ='PSD'
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 = 1781134406 WHERE code = 'PSD'
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>=1780529606)
AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781134406)
AND (t.oob_prefix REGEXP 'PSD')
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`,
MAX(oob_time_sec) AS `Latest`,
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`
FROM webfwlog.allrows t
GROUP BY `Label`,
t.ip_protocol,
ip_saddr_bin,
ip_daddr_bin
ORDER BY `Latest` DESC
;
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;
DROP TABLE webfwlog.tmp_output;
SELECT * FROM webfwlog.output
Firewall Log Report
Firewall Log Report
SELECT
count(*) AS `Count`,
oob_prefix AS `Label`,
MAX(oob_time_sec) AS `Latest`,
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`
FROM table(s) t
WHERE (t.oob_time_sec IS NOT NULL AND t.oob_time_sec>=1780529606)
AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781134406)
AND (t.oob_prefix REGEXP 'PSD')
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))
GROUP BY `Label`,
t.ip_protocol,
ip_saddr_bin,
ip_daddr_bin
ORDER BY `Latest` DESC
LIMIT 0, 20