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

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 = 1781688699 WHERE code = 'sshd'

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_prefix IS NULL OR NOT (t.oob_prefix REGEXP 'ip_conntrack'))
	AND (t.ip_protocol=6)
	AND (NOT (t.ip_protocol=6) OR (t.tcp_dport=22))
	AND NOT ((wfwl_inet_contains(t.oob_family, t.ip_saddr_bin, '70.219.128.0/17')))
	AND ((t.oob_family=2 AND t.ip_daddr_bin = wfwl_inet_pton(10, '64.81.233.106')))
	AND (t.oob_in REGEXP 'eth0')
	AND (t.ip_protocol<>6 OR (t.tcp_syn AND NOT t.tcp_fin AND NOT t.tcp_ack AND NOT t.tcp_rst AND NOT t.tcp_psh AND NOT t.tcp_urg))

SHOW COLUMNS FROM ulogd.ulog;

CREATE TEMPORARY TABLE webfwlog.tmp_output AS
SELECT
	count(*) AS `count_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`,
	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,
	ip_saddr_bin,
	`Source Host`,
	ip_daddr_bin,
	`Dest Port`,
	`Options` 

ORDER BY 	`Latest` 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

ALTER TABLE webfwlog.tmp_output RENAME TO webfwlog.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