Recent Outbound Host-Port Connections

Checking recent outgoing traffic can be aggregated from the querying Capsule8's connection events. The query will return recent outbound hosts and port combinations that have been seen in the last 24 hours.

Required Tables

• connections

Returned Fields

Query

SELECT
	CONCAT(dst_addr, ':', cast(dst_port AS varchar)) AS hostport,
	COUNT(1) as occurrences, 
	FROM_UNIXTIME(AVG(unix_nano_timestamp)/1e9) as dt
FROM connections b
WHERE
	(SELECT COUNT(1)
	FROM connections a
	WHERE a.dst_addr = b.dst_addr
	AND a.dst_port = b.dst_port
	AND a.unix_nano_timestamp < TO_UNIXTIME(NOW()) * 1e9 - 60*60*24*1e9) = 0
GROUP BY  dst_addr, dst_port