サーバー情報をSQLで取得するosqueryを使う

# 記事の概要
Facebookによって開発されたサーバー情報取得ツール osquery を使ってみます。
この記事ではLinuxサーバーとWindowsサーバーにosqueryを導入して基本的な動作を確認するところまでを記載します。

# osqueryについて
* [osqueryのトップページ](https://osquery.io/)
* [ドキュメントページ](https://osquery.readthedocs.io/en/stable/)
* [GitHubリポジトリ](https://github.com/osquery/osquery)

# 動作環境
osqueryを導入するLinuxサーバーとWindowsサーバーを1台ずつAWS上に構築します。

| OS種別      | OSバージョン| AMIイメージ名 |
|:-----------|:-----------|:-----------|
| Linux      | Amazon Linux 2 | amzn2-ami-hvm-2.0.20200304.0-x86_64-gp2 |
| Windows    | Windows Server 2019 | Windows_Server-2019-Japanese-Full-Base-2021.12.15 |

私は以下の手順を参考にして Terraform で EC2 インスタンスを構築しました。
[Terraformで、Systems Manager（Session Manager）でアクセスできるようにしたEC2インスタンスを構築する](https://qiita.com/charon/items/58c3ad20232c74a6dc01)

なお、上記の手順で構築するのはLinuxサーバーだけなので、Windowsサーバーのインスタンスも一緒に構築するようにTerraformコードは書き換えて使いましょうｗ

# Linux サーバーで osquery を実行する

まずはLinux（Amazon Linux 2)サーバーにosqueryを導入して実行してみます。

## osqueryのインストール
* [osqueryダウンロードページ](https://osquery.io/downloads/official/5.0.1) を開いて「Alternative Install Options - RPM Linux」を選択するとYumでインストールするコマンドが表示されます。

* Linuxサーバーのシェルに表示されたコマンドを入力してosqueryをインストールします。

```bash
curl -L https://pkg.osquery.io/rpm/GPG | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-osquery
sudo yum-config-manager --add-repo https://pkg.osquery.io/rpm/osquery-s3-rpm.repo
sudo yum-config-manager --enable osquery-s3-rpm-repo
sudo yum install osquery
```

## osqueryi でインタラクティブに実行する

* Linuxサーバーのシェルでosqueryiコマンドを実行し、インタラクティブシェルを起動します。

```bash
sh-4.2$ osqueryi
Using a virtual database. Need help, type '.help'
osquery>
```

* 試しに名前が「python」で始まるインストール済RPMパッケージを検索してみます。

```bash
osquery> select name,version from rpm_packages where name like 'python%';
+-------------------------------------+---------+
| name                                | version |
+-------------------------------------+---------+
| python                              | 2.7.16  |
| python-babel                        | 0.9.6   |
| python-backports                    | 1.0     |
| python-backports-ssl_match_hostname | 3.5.0.1 |
| python-cffi                         | 1.6.0   |
| python-chardet                      | 2.2.1   |
| python-colorama                     | 0.3.2   |
| python-configobj                    | 4.7.2   |
| python-daemon                       | 1.6     |
| python-devel                        | 2.7.16  |
| python-docutils                     | 0.12    |
| python-enum34                       | 1.0.4   |
| python-idna                         | 2.4     |
| python-iniparse                     | 0.4     |
| python-ipaddress                    | 1.0.16  |
| python-jinja2                       | 2.7.2   |
| python-jsonpatch                    | 1.2     |
| python-jsonpointer                  | 1.9     |
| python-jwcrypto                     | 0.4.2   |
| python-kitchen                      | 1.1.1   |
| python-libs                         | 2.7.16  |
| python-lockfile                     | 0.9.1   |
| python-markupsafe                   | 0.11    |
| python-pillow                       | 2.0.0   |
| python-ply                          | 3.4     |
| python-pycparser                    | 2.14    |
| python-pycurl                       | 7.19.0  |
| python-repoze-lru                   | 0.4     |
| python-requests                     | 2.6.0   |
| python-simplejson                   | 3.2.0   |
| python-six                          | 1.9.0   |
| python-urlgrabber                   | 3.10    |
| python-urllib3                      | 1.24.3  |
| python2-botocore                    | 1.13.36 |
| python2-cryptography                | 1.7.2   |
| python2-dateutil                    | 2.6.0   |
| python2-futures                     | 3.0.5   |
| python2-jmespath                    | 0.9.3   |
| python2-jsonschema                  | 2.5.1   |
| python2-oauthlib                    | 2.0.1   |
| python2-pyasn1                      | 0.1.9   |
| python2-rpm                         | 4.11.3  |
| python2-rsa                         | 3.4.1   |
| python2-s3transfer                  | 0.1.12  |
| python2-setuptools                  | 38.4.0  |
+-------------------------------------+---------+
```

* 実行結果をJSON形式で出力することもできます。

```bash
sh-4.2$ osqueryi --json "select name,version from rpm_packages where name like 'python%';"
[
  {"name":"python","version":"2.7.16"},
  {"name":"python-babel","version":"0.9.6"},
  {"name":"python-backports","version":"1.0"},
  {"name":"python-backports-ssl_match_hostname","version":"3.5.0.1"},
  {"name":"python-cffi","version":"1.6.0"},
  {"name":"python-chardet","version":"2.2.1"},
  {"name":"python-colorama","version":"0.3.2"},
  {"name":"python-configobj","version":"4.7.2"},
  {"name":"python-daemon","version":"1.6"},
  {"name":"python-devel","version":"2.7.16"},
  {"name":"python-docutils","version":"0.12"},
  {"name":"python-enum34","version":"1.0.4"},
  {"name":"python-idna","version":"2.4"},
  {"name":"python-iniparse","version":"0.4"},
  {"name":"python-ipaddress","version":"1.0.16"},
  {"name":"python-jinja2","version":"2.7.2"},
  {"name":"python-jsonpatch","version":"1.2"},
  {"name":"python-jsonpointer","version":"1.9"},
  {"name":"python-jwcrypto","version":"0.4.2"},
  {"name":"python-kitchen","version":"1.1.1"},
  {"name":"python-libs","version":"2.7.16"},
  {"name":"python-lockfile","version":"0.9.1"},
  {"name":"python-markupsafe","version":"0.11"},
  {"name":"python-pillow","version":"2.0.0"},
  {"name":"python-ply","version":"3.4"},
  {"name":"python-pycparser","version":"2.14"},
  {"name":"python-pycurl","version":"7.19.0"},
  {"name":"python-repoze-lru","version":"0.4"},
  {"name":"python-requests","version":"2.6.0"},
  {"name":"python-simplejson","version":"3.2.0"},
  {"name":"python-six","version":"1.9.0"},
  {"name":"python-urlgrabber","version":"3.10"},
  {"name":"python-urllib3","version":"1.24.3"},
  {"name":"python2-botocore","version":"1.13.36"},
  {"name":"python2-cryptography","version":"1.7.2"},
  {"name":"python2-dateutil","version":"2.6.0"},
  {"name":"python2-futures","version":"3.0.5"},
  {"name":"python2-jmespath","version":"0.9.3"},
  {"name":"python2-jsonschema","version":"2.5.1"},
  {"name":"python2-oauthlib","version":"2.0.1"},
  {"name":"python2-pyasn1","version":"0.1.9"},
  {"name":"python2-rpm","version":"4.11.3"},
  {"name":"python2-rsa","version":"3.4.1"},
  {"name":"python2-s3transfer","version":"0.1.12"},
  {"name":"python2-setuptools","version":"38.4.0"}
]
```

* 詳しい使い方はosqueryiのマニュアル([Using osqueryi](https://osquery.readthedocs.io/en/stable/introduction/using-osqueryi/))を参照しましょう。

## osqueryd でデーモンとして実行する

インストール時に導入されるosquerydを使ってosqueryをデーモン実行する手順も記載します。

* 設定ファイル(osquery.conf)をサンプルからコピーして作成します。

```bash
sh-4.2$ sudo cp /opt/osquery/share/osquery/osquery.example.conf /etc/osquery/osquery.conf
```

* 作成したosquery.confを編集すればosqueryの設定を変更できますが、ここではデフォルト設定のままとします。

```bash
sh-4.2$ vi /etc/osquery/osquery.conf

※デフォルトで設定されているクエリ（1時間毎に実行）
・・・
  "schedule": {
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600
    }
  },
・・・
```

* osquerydをsystemctlでサービスとして起動すると、osqueryd が実行中であることが確認できます。

```bash
sh-4.2$ sudo systemctl start osqueryd
sh-4.2$ ps auxwf | grep osquery
ssm-user 32668  0.0  0.0 121268   952 pts/0    S+   02:57   0:00          \_ grep osquery
root     32635  0.0  3.2 142340 32568 ?        SNsl 02:56   0:00 /opt/osquery/bin/osqueryd --flagfile /etc/osquery/osquery.flags --config_path /etc/osquery/osquery.conf
root     32640  0.4  3.5 638680 35680 ?        SNl  02:56   0:00  \_ /opt/osquery/bin/osqueryd

```

* デフォルト設定ではログが /var/log/osquery/ に出力されます。なお、1時間毎に情報取得する設定なのでしばらく放置してから確認しましょう。

```bash
sh-4.2$ cd /var/log/osquery/
sh-4.2$ ls -l
total 8
lrwxrwxrwx 1 root root  35 Jan  5 02:56 osqueryd.INFO -> osqueryd.INFO.20220105-025653.32640
-rw-r--r-- 1 root root 801 Jan  5 03:55 osqueryd.INFO.20220105-025653.32640
-rw-r----- 1 root root 461 Jan  5 03:55 osqueryd.results.log
```

* 実行結果は osqueryd.results.log にJSON形式で出力されます。
 
```bash
sh-4.2$ sudo cat osqueryd.results.log
{
  "name": "system_info",
  "hostIdentifier": "ip-10-0-101-83.ap-southeast-1.compute.internal",
  "calendarTime": "Wed Jan  5 03:55:15 2022 UTC",
  "unixTime": 1641354915,
  "epoch": 0,
  "counter": 0,
  "numerics": false,
  "decorations": {
    "host_uuid": "EC21021B-CAE4-CF0C-4074-D21571FE9FFE",
    "username": "runlevel"
  },
  "columns": {
    "cpu_brand": "Intel(R) Xeon(R) CPU E5-2676 v3 @ 2.40GHz",
    "hostname": "ip-10-0-101-83.ap-southeast-1.compute.internal",
    "physical_memory": "1031131136"
  },
  "action": "added"
}
```

# Windows サーバーで osquery を実行する

次に Windows(Windows Server 2019)サーバーにもosqueryを導入して実行してみます。

## osqueryのインストール

* 先に管理者モードで起動した PowerShell でパッケージマネージャーの Chocolatery をインストールします。

```powershell
PS C:\Users\Administrator> Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))
```

* 管理者モードで起動した PowerShell から Chocolatery で osquery をインストールします。

```powershell
PS C:\Users\Administrator> choco install osquery
```

## osqueryi でインタラクティブに実行する

* WindowsサーバーのPowerShellでosqueryiコマンドを実行し、インタラクティブシェルを起動します。

```powershell
PS C:\Users\Administrator> osqueryi
Using a [1mvirtual database[0m. Need help, type '.help'
osquery>
```

* 試しに名前がプログラム一覧を取得してみます。

```powershell
osquery> select name,version from programs;
+--------------------------------------------------------------------+---------------+
| name                                                               | version       |
+--------------------------------------------------------------------+---------------+
| Amazon SSM Agent                                                   | 3.1.338.0     |
| Microsoft Visual C++ 2019 X64 Additional Runtime - 14.28.29913     | 14.28.29913   |
| AWS PV Drivers                                                     | 8.4.0         |
| aws-cfn-bootstrap                                                  | 2.0.6         |
| Microsoft Visual C++ 2019 X64 Minimum Runtime - 14.28.29913        | 14.28.29913   |
| aws-cfn-bootstrap                                                  | 2.0.6         |
| AWS Tools for Windows                                              | 3.15.1494     |
| Amazon SSM Agent                                                   | 3.1.338.0     |
| Microsoft Visual C++ 2015-2019 Redistributable (x64) - 14.28.29913 | 14.28.29913.0 |
+--------------------------------------------------------------------+---------------+
```

## osqueryd でデーモンとして実行する

Linuxサーバーと同様に、インストール時に導入されるosquerydを使ってosqueryをデーモン実行する手順も記載します。

* 設定ファイル osquery.conf は C:\Program Files\osquery\osquery.conf に存在します。ここではデフォルト設定のままとします。

* 管理者モードで起動した PowerShell からヘルパースクリプトを実行します。

```powershell
PS C:\Users\Administrator> cd "C:\Program Files\osquery"
PS C:\Program Files\osquery> .\manage-osqueryd.ps1 -install -startupArgs "C:\Program Files\osquery\osquery.flags"
Installed 'osqueryd' system service.

Status   Name               DisplayName
------   ----               -----------
```

* 管理者モードで起動した PowerShell から osqueryd サービスを開始すると、osqueryd が実行中であることが確認できます。

```powershell
PS C:\Program Files\osquery> Start-Service osqueryd
PS C:\Program Files\osquery> Get-Service | Where-Object { $_.Name -eq "osqueryd" }

Status   Name               DisplayName
------   ----               -----------
Running  osqueryd           osqueryd
```

* デフォルト設定ではログが C:\Program Files\osquery\log に出力されます。なお、1時間毎に情報取得する設定なのでしばらく放置してから確認しましょう。

```powershell
PS C:\Program Files\osquery> dir log

    ディレクトリ: C:\Program Files\osquery\log

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       2022/01/05      7:30              0 osqueryd.INFO.20220105-073035.1684
-a----       2022/01/05      8:18            404 osqueryd.results.log
```

* 実行結果は osqueryd.results.log にJSON形式で出力されます。

```powershell
PS C:\Program Files\osquery> Get-Content .\log\osqueryd.results.log
{
  "name": "system_info",
  "hostIdentifier": "EC2AMAZ-AO2LTMP",
  "calendarTime": "Wed Jan  5 08:18:22 2022 UTC",
  "unixTime": 1641370702,
  "epoch": 0,
  "counter": 0,
  "numerics": false,
  "decorations": {
    "host_uuid": "EC2804D7-9932-5852-F89C-180B8569356C",
    "username": "Administrator"
  },
  "columns": {
    "cpu_brand": "Intel(R) Xeon(R) CPU E5-2676 v3 @ 2.40GHz",
    "hostname": "EC2AMAZ-AO2LTMP",
    "physical_memory": "2147483648"
  },
  "action": "added"
}
```

# まとめ

この記事ではLinuxサーバーとWindowsサーバーにそれぞれ osquery を導入し、インタラクティブ/デーモン実行するまでをやってみました。取得したデータは構造化されており使い慣れたSQLを使って分析できるので、構築後のサーバーを管理するのに役立ちそうです。

osquerydで取得したログはJSON形式で出力されるので、Fluentdなどでログ収集サーバーに集約することで複数サーバーの構成管理にも役立てられそうに思います。この辺はまた別の記事で書きたいと思います。