外部用ネットワークトラフィック - IPv4

Sophos Linux Sensor の調査の接続イベントをクエリすることで、パブリックインターネットへの接続が想定されていないサーバーを監査できます。クエリの結果は、宛先アドレスの一覧です。このような宛先アドレスを使用して、現在の外部接続が既知で、安全または悪意があるとして検出されることを確認できます。

必要なテーブル

connections

戻りフィールド

フィールド	説明
ipint dst_addr	外部接続の ipint および dst_addr

クエリ

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)