外部用ネットワークトラフィック - 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)