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

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 = 1781134325 WHERE code = 'current_count'

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>=1781071200)
	AND (t.oob_time_sec IS NOT NULL AND t.oob_time_sec<=1781134325)
	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`,
	'' AS `Src Country`,
	CASE WHEN s_hostnames.hostname IS NULL
	     THEN '-'
	     ELSE s_hostnames.hostname
	END
	AS `Source Host`,
	wfwl_inet_ntop(t.oob_family, ip_saddr_bin) AS `Source IP`,
	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`,
	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 	`Label`,
	t.ip_protocol,
	`Src Country`,
	`Source Host`,
	ip_saddr_bin,
	ip_daddr_bin,
	`Dest Port`,
	`Options` 

ORDER BY 	`Count` DESC,
	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

CREATE TEMPORARY TABLE webfwlog.output AS
	SELECT * FROM webfwlog.tmp_output LIMIT 0, 1000;

DROP TABLE webfwlog.tmp_output;

SELECT * FROM webfwlog.output

Firewall Log Report

Most Popular Recent Logged Entries