External-bound Network Traffic - IPv4 - With Allowed Exceptions

Auditing servers that are not supposed to make connections to the public internet can be achieved by querying Capsule8's Investigations connection events. The results of the query will be a list of destination addresses. These destination addresses can be used to ensure that current external connections are known and identified as safe or malicious.

This query allows for an allowlist to be applied.

Required Tables

• connections

Input Fields

The allowlist can be updated by adding IPs to theallowlist field. Each IP should use the format SELECT '<IP ADDRESS>' as addr. The list of select statements should be joined together with a UNION ALL statement.

Returned Fields

Query

WITH 
ipranges as (
  SELECT '10.0.0.0' as low, '10.255.255.255' as high
  UNION ALL
  SELECT '172.16.0.0' as low, '172.31.255.255' as high
  UNION ALL
  SELECT '192.168.0.0' as low, '192.168.255.255' as high
  UNION ALL
  SELECT '127.0.0.0' as low, '127.255.255.255' as high
),
allowlist as (
  SELECT '100.24.246.89' as addr UNION ALL SELECT '104.16.171.99' as addr
),
ipint as (SELECT dst_addr, (
         cast(split_part(dst_addr, '.', 1) AS bigint) * (256 * 256 * 256) + 
         cast(split_part(dst_addr, '.', 2) AS bigint) * (256 * 256 ) + 
         cast(split_part(dst_addr, '.', 3) AS bigint) * (256 ) + 
         cast(split_part(dst_addr, '.', 4) AS bigint)) AS ipint
    FROM connections),
iprangesint as (
  SELECT CONCAT(ipranges.low, ' - ', ipranges.high) as str, 
         (cast(split_part(ipranges.low, '.', 1) AS bigint) * (256 * 256 * 256) + 
          cast(split_part(ipranges.low, '.', 2) AS bigint) * (256 * 256 ) + 
          cast(split_part(ipranges.low, '.', 3) AS bigint) * (256 ) + 
          cast(split_part(ipranges.low, '.', 4) AS bigint)) AS low,
         (cast(split_part(ipranges.high, '.', 1) AS bigint) * (256 * 256 * 256) + 
          cast(split_part(ipranges.high, '.', 2) AS bigint) * (256 * 256 ) + 
          cast(split_part(ipranges.high, '.', 3) AS bigint) * (256 ) + 
          cast(split_part(ipranges.high, '.', 4) AS bigint)) AS high
  FROM ipranges)
SELECT ipint.ipint, ipint.dst_addr
FROM ipint
WHERE NOT EXISTS (SELECT iprangesint.str FROM iprangesint WHERE iprangesint.low <= ipint.ipint AND iprangesint.high >= ipint.ipint) AND dst_addr NOT IN (SELECT addr from allowlist)
ORDER BY dst_addr