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

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 = 1781134593 WHERE code = 'last_24_hours'

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>=1781048193)
	AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781134593)
	AND (t.oob_prefix IS NULL OR NOT (t.oob_prefix REGEXP 'ip_conntrack'))
	AND (NOT (t.ip_protocol=6) OR NOT (t.tcp_sport=3128))
	AND (NOT (t.ip_protocol=6) OR NOT (t.tcp_dport=23 OR t.tcp_dport=25 OR t.tcp_dport=135 OR t.tcp_dport=139 OR t.tcp_dport=80 OR t.tcp_dport BETWEEN 1025 AND 1029 OR t.tcp_dport BETWEEN 1433 AND 1434 OR t.tcp_dport=25 OR t.tcp_dport=4899 OR t.tcp_dport=9898 OR t.tcp_dport=5554))
	AND (NOT (t.ip_protocol=17) OR NOT (t.udp_dport=123 OR t.udp_dport BETWEEN 1025 AND 1035 OR t.udp_dport=370 OR t.udp_dport=1434))
	AND (NOT (t.ip_protocol=1) OR NOT (t.icmp_type=8))
	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`,
	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`,
	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 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


GROUP BY 	`oob_family_t`,
	`Label`,
	t.ip_protocol,
	ip_saddr_bin,
	`Source Host`,
	ip_daddr_bin,
	`Dest Port`,
	`Ty`,
	`Co`,
	`Options` 

ORDER BY 	t.ip_protocol DESC,
	`Dest Port` ASC,
	ip_saddr_bin ASC,
	`Options` ASC,
	oob_prefix ASC,
	`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, 100;

DROP TABLE webfwlog.tmp_output;

SELECT * FROM webfwlog.output

Firewall Log Report

Latest Activity by Service

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

DROP TABLE webfwlog.output

DROP TABLE webfwlog.allrows

Generated by webfwlog 1.1.3