Linuxのosquery入門｜SQLでエンドポイントを監査するセキュリティ監視の実践ガイド

# 公式GPGキーの取得
curl -L https://pkg.osquery.io/deb/pubkey.gpg | sudo gpg --dearmor -o /usr/share/keyrings/osquery-archive-keyring.gpg

# リポジトリの追加
echo "deb [signed-by=/usr/share/keyrings/osquery-archive-keyring.gpg] https://pkg.osquery.io/deb deb main" \
  | sudo tee /etc/apt/sources.list.d/osquery.list

# インストール
sudo apt update
sudo apt install osquery

# 公式RPMリポジトリの追加
curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
sudo rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery

sudo tee /etc/yum.repos.d/osquery.repo <<EOF
[osquery]
name=osquery
baseurl=https://pkg.osquery.io/rpm
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
EOF

sudo dnf install osquery

# バージョン確認
osqueryi --version

# 対話モードを起動
sudo osqueryi

-- root権限（uid=0）で動作し、標準パス以外にある実行ファイル
SELECT pid, name, path, cmdline, uid, start_time
FROM processes
WHERE uid = 0
  AND path NOT LIKE '/usr/%'
  AND path NOT LIKE '/bin/%'
  AND path NOT LIKE '/sbin/%'
  AND path NOT LIKE '/lib/%'
  AND path != ''
ORDER BY start_time DESC;

-- 待ち受けポートとプロセス名を一覧表示
SELECT l.port, l.address, l.protocol,
       p.pid, p.name, p.path, p.cmdline
FROM listening_ports l
JOIN processes p ON l.pid = p.pid
WHERE l.port != 0
ORDER BY l.port;

-- ESTABLISHEDな外部接続を確認
SELECT p.pid, p.name, p.path,
       c.local_address, c.local_port,
       c.remote_address, c.remote_port, c.state
FROM process_open_sockets c
JOIN processes p ON c.pid = p.pid
WHERE c.state = 'ESTABLISHED'
  AND c.remote_address NOT LIKE '127.%'
  AND c.remote_address NOT LIKE '10.%'
  AND c.remote_address NOT LIKE '192.168.%'
  AND c.remote_address NOT LIKE '172.1%'
ORDER BY p.name;

-- uid=0（root相当）のユーザーを全列挙
SELECT username, uid, gid, description, directory, shell
FROM users
WHERE uid = 0;

-- ログインシェルを持つ通常ユーザー一覧
SELECT username, uid, gid, directory, shell
FROM users
WHERE uid >= 1000
  AND shell NOT LIKE '%nologin%'
  AND shell NOT LIKE '%false%';

-- 過去24時間以内に変更された/etc以下のファイル
SELECT path, mtime, size,
       uid, gid, permissions
FROM file
WHERE path LIKE '/etc/%'
  AND mtime > (strftime('%s', 'now') - 86400)
ORDER BY mtime DESC;

{
  "options": {
    "config_plugin": "filesystem",
    "logger_plugin": "filesystem",
    "logger_path": "/var/log/osquery",
    "schedule_splay_percent": 10,
    "events_expiry": 3600,
    "verbose": false,
    "worker_threads": 2
  },
  "schedule": {
    "process_monitor": {
      "query": "SELECT pid, name, path, cmdline, uid FROM processes WHERE uid = 0 AND path NOT LIKE '/usr/%' AND path NOT LIKE '/bin/%' AND path != '';",
      "interval": 300,
      "description": "Monitor for unexpected root processes"
    },
    "listening_ports": {
      "query": "SELECT l.port, l.address, p.name, p.path FROM listening_ports l JOIN processes p ON l.pid = p.pid WHERE l.port != 0;",
      "interval": 600,
      "description": "Monitor listening network ports"
    },
    "user_accounts": {
      "query": "SELECT username, uid, gid, directory, shell FROM users WHERE uid = 0;",
      "interval": 3600,
      "description": "Monitor root-equivalent accounts"
    },
    "etc_file_changes": {
      "query": "SELECT path, mtime, uid, permissions FROM file WHERE path LIKE '/etc/%' AND mtime > (strftime('%s', 'now') - 3600);",
      "interval": 3600,
      "description": "Monitor changes to /etc files"
    }
  }
}

--config_path=/etc/osquery/osquery.conf
--logger_path=/var/log/osquery
--pidfile=/var/run/osquery.pid
--audit_allow_config=true
--audit_persist=true
--disable_audit=false

# サービスの起動
sudo systemctl start osqueryd
sudo systemctl enable osqueryd

# 状態確認
sudo systemctl status osqueryd

# ログ確認
sudo tail -f /var/log/osquery/osqueryd.results.log | python3 -m json.tool