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

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 = 1781134451 WHERE code = 'trusted'

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<=1781134451)
	AND (t.oob_prefix IS NULL OR NOT (t.oob_prefix REGEXP 'ip_conntrack'))
	AND ((wfwl_inet_contains(t.oob_family, t.ip_saddr_bin, '70.219.0.0/16')) OR (wfwl_inet_contains(t.oob_family, t.ip_saddr_bin, '198.31.0.0/16')) OR (wfwl_inet_contains(t.oob_family, t.ip_saddr_bin, '66.174.6.205/16')))
	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`,
	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=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`,
	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
	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 	`Label`,
	t.ip_protocol,
	ip_saddr_bin,
	`Source Host`,
	ip_daddr_bin,
	`Destination Host`,
	`Dest Port`,
	`Dest Service`,
	`Options` 

ORDER BY 	ip_saddr_bin ASC,
	t.ip_protocol DESC,
	`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

ALTER TABLE webfwlog.tmp_output RENAME TO webfwlog.output;

SELECT * FROM webfwlog.output

Firewall Log Report

Latest Activity

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

DROP TABLE webfwlog.output

DROP TABLE webfwlog.allrows

Generated by webfwlog 1.1.3