Skip to content

External-bound network traffic - IPv6

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
expanded_address
dst_addr		 The ip address and ip int for the external connection.
process_uuid The process id of the process making the request.

Query

WITH 
ipv6 AS 
    (SELECT dst_addr, dst_port, process_uuid FROM connections
    WHERE NOT regexp_like(dst_addr, '((25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|1{0,1}[0-9]){0,1}[0-9])')),
ipv6expanded AS 
    (SELECT dst_addr,
         dst_port,
         process_uuid,
         colons,
        CASE
        WHEN expansions=0 THEN
        dst_addr
        WHEN expansions=1 THEN
        CASE direction
        WHEN 1 THEN
        REPLACE(dst_addr, '::', CONCAT(':', array_join(transform(sequence(1, 7-(colons-(ABS(direction)+expansions))), x-> 0), ':')))
        WHEN -1 THEN
        REPLACE(dst_addr, '::', CONCAT(array_join(transform(sequence(1, 7-(colons-(ABS(direction)+expansions))), x-> 0), ':'), ':'))
        WHEN 0 THEN
        REPLACE(dst_addr, '::', CONCAT(':', array_join(transform(sequence(1, 7-(colons-(ABS(direction)+expansions))), x-> 0), ':'), ':'))
        END END AS expanded_address, dst_port
    FROM 
        (SELECT LENGTH(dst_addr) - LENGTH(REPLACE(dst_addr,
         ':', '')) AS colons, LENGTH(dst_addr) - LENGTH(REPLACE(dst_addr, '::', '?')) AS expansions,
            CASE
            WHEN dst_addr LIKE '::%' THEN
            -1
            WHEN dst_addr LIKE '%::' THEN
            1
            ELSE 0
            END AS direction, dst_addr, dst_port, process_uuid
        FROM ipv6)
        ORDER BY  dst_addr),
ipint as (SELECT  cast(FROM_BASE(split_part(expanded_address, ':', 1), 16) AS DECIMAL(38, 0)) * cast(POWER(256, 6) as DECIMAL(38, 0)) +
     cast(FROM_BASE(split_part(expanded_address, ':', 2), 16) AS DECIMAL(38, 0)) * cast(POWER(256, 4) as DECIMAL(38, 0)) +
     cast(FROM_BASE(split_part(expanded_address, ':', 3), 16) AS DECIMAL(38, 0)) * cast(POWER(256, 2) as DECIMAL(38, 0)) +
     cast(FROM_BASE(split_part(expanded_address, ':', 4), 16) AS DECIMAL(38, 0)) as upper
    , expanded_address, dst_addr, process_uuid
    FROM ipv6expanded)
SELECT expanded_address, dst_addr, process_uuid from ipint
WHERE
    -- local fd00::
    (upper < DECIMAL '18230571291595767808' OR
    -- fdff:ffff...ffff
    upper > DECIMAL '18302628885633695743') AND (
    -- link local fe80:0:0:0
    upper < DECIMAL '18338657682652659712' OR
    -- febf:ffff:ffff:ffff
    upper > DECIMAL '18356672081162141695' ) AND 
    -- ::1
    expanded_address != '0:0:0:0:0:0:0:1'