SELECT version() AS version

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 ='latest_logged_packets'

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 = 1781356014 WHERE code = 'latest_logged_packets'

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>=1781330400)
	AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781356014)
	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`,
	t.oob_family AS `oob_family_t`,
	count(*) AS `Count`,
	MAX(oob_time_sec) AS `Latest`,
	oob_prefix AS `Label`,
	oob_in AS `In If`,
	oob_out AS `Out If`,
	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`,
	oob_family AS `Family`,
	CASE WHEN s_hostnames.hostname IS NULL
	     THEN '-'
	     ELSE s_hostnames.hostname
	END
	AS `Source Host`,
	wfwl_inet_ntop(t.oob_family, ip_daddr_bin) AS `Destination IP`,
	CASE WHEN d_hostnames.hostname IS NULL
	     THEN '-'
	     ELSE d_hostnames.hostname
	END
	AS `Destination Host`,
	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=1  THEN icmp_type
	     WHEN t.ip_protocol=58 THEN icmpv6_type
	     ELSE NULL
	END
	AS `Ty`,
	CASE WHEN t.ip_protocol=1  THEN icmp_code
	     WHEN t.ip_protocol=58 THEN icmpv6_code
	     ELSE NULL
	END
	AS `Co`,
	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
	LEFT JOIN webfwlog.hostnames AS s_hostnames ON t.`ip_saddr` = s_hostnames.ip_addr
	LEFT JOIN webfwlog.hostnames AS d_hostnames ON t.`ip_daddr` = d_hostnames.ip_addr


GROUP BY 	`oob_family_t`,
	`Label`,
	`In If`,
	`Out If`,
	t.ip_protocol,
	ip_saddr_bin,
	`Family`,
	`Source Host`,
	ip_daddr_bin,
	`Destination Host`,
	`Dest Port`,
	`Ty`,
	`Co`,
	`Options` 

ORDER BY 	`Latest` DESC,
	ip_saddr_bin ASC,
	ip_daddr_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, 100;

DROP TABLE webfwlog.tmp_output;

SELECT * FROM webfwlog.output

Firewall Log Report

Latest Activity

Query time was 0 Seconds.
Report time was 0 Seconds.
Total time was 0 Seconds.

DROP TABLE webfwlog.output

DROP TABLE webfwlog.allrows

Generated by webfwlog 1.1.3