Skip to content

External-bound network traffic - IPv4

Auditing servers that are not supposed to make connections to the public internet can be achieved by querying Sophos Linux Sensor'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.

Required Tables

  • connections

Returned Fields

Field Description
ipint
dst_addr		 ipint and dst_addr for the external connection

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
),
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 WHERE regexp_like(dst_addr, '^((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$')),
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)