SplunkでMySQLのデータをインデックスしてみた

Splunkといえば、ログをはじめとする各種データをインデックスして検索・分析・可視化するソフトウェアとして有名ですね。
「ログみたいなテキストデータしか扱えないんでしょ？」という声もチラホラ聞こえてきそうですが、いやいやそうではありません。
MySQLのようなRDBMSをデータソースとしてSplunkで分析・可視化することもできるんです。

その方法をまとめてみました。

# Splunk DB Connect
DBに入っているデータをSplunkで使うためには **[Splunk DB Connect](https://splunkbase.splunk.com/app/2686/)** というAppを使います。

サポートされているDBは以下
※ DB Connectバージョン3.0.2のものです。最新情報は[こちら](http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Installdatabasedrivers#Supported_databases)参照。

- AWS RDS Aurora
- AWS RedShift
- IBM DB2 for Linux
- Informix
- MemSQL
- Microsoft SQL Server
- MySQL
- Oracle Database
- PostgreSQL
- SAP SQL Anywhere (aka Sybase SA)
- Spark SQL
- Sybase ASE
- Sybase IQ
- Teradata

## 前提条件
JDKが必要です。
Splunkが入っているサーバー・端末に[JDK](http://www.oracle.com/technetwork/java/javase/downloads/index.html)をインストールしましょう。
その他の前提条件は[こちら](http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Prerequisites)参照

## インストール
まずはお手元のSplunkにDB ConnectというAppをインストールします。

1. Splunkにログインして [Appの管理] 画面に進む
<img width="321" alt="Screen Shot 2017-04-08 7.32.18 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/2ea41798-7c01-2db0-3f90-502caf4e3644.png">

2. [ファイルからAppをインストール] からダウンロードしたファイルをアップロードするか、もしくは、 [他のAppを参照] から`DB Connect`を検索してSplunkに直接インストール
<img width="389" alt="Screen Shot 2017-04-08 7.32.53 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/cb94abf4-42c0-1661-4b2d-e184b220dc67.png">

3. Splunkを再起動

## セットアップ
DB Connectの仕組みは下記概念図と考えるとわかりやすいと思います。
<img width="690" alt="Dbx-connection.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/76b37443-f055-1c90-3b1d-11054e2b41c6.png">

- 緑色: Splunkそのもの
- 黄色: DB Connectの機能・仕組み
- 青色: MySQL等RDBMS

今回は図の黄色部分の設定方法を主に解説します。

### 環境設定
1. JAVA_HOMEを設定（DB Connect App > [設定] > [Settings] > [General]）
<img width="551" alt="Screen Shot 2017-04-08 7.45.12 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/9942979c-79c9-159a-ca2f-d1962d767e2c.png">
Windows: `C:\Program Files\Java\jdk1.8.0_121`
OSX: `/Library/Java/JavaVirtualMachines/jdk1.8.0_101.jdk/Contents/Home/jre`
Linux: `/opt/jdk1.8.0_121/jre/`

2. JDBCドライバ配置
[MySQL Download Connector/J](https://dev.mysql.com/downloads/connector/j/)をダウンロード、解凍して`mysql-connector-java-version-bin.jar`ファイルを`$SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers`にコピー
※ $SPLUNK_HOMEはSplunkが使う環境変数です。Linuxの場合、`/opt/splunk`と読み替えてください。

3. JDBCドライバ読み込み
Splunkで、[設定] > [Settings] > [Drivers] に進んで、右側の[再読込]ボタンをクリック
<img width="1269" alt="Screen Shot 2017-04-08 7.55.03 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/aa8b9716-c53c-028b-ea0c-e6b8a90987b4.png">
↑MySQLのドライバが設定されたのがわかりますね

4. アイデンティティ作成
DB接続ユーザーのIDとパスワードをSplunkで設定します。
[設定] > [Database] > [Identities] に進んで、右側の[New Identity]ボタンをクリックしてアイデンティティを作成。
私の場合、MySQLに`splunk`というユーザーを設定したので、下のように設定しました。
<img width="364" alt="Screen Shot 2017-04-08 8.01.54 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/4863342d-48d3-3d39-69ce-d7eb1cdd1388.png">

5. コネクション作成
SplunkからDBへの接続設定を作成します。
[設定] > [Database] > [Connections] に進んで、右側の[New Connection]ボタンをクリックしてコネクションを作成。
_今回は検証環境なので、ローカルのMacにSplunkとMySQLを同居させて、`splunktest`というデータベースを作成しています。
本来はMySQLとSplunkは別々のサーバーにするべきものなので、必ずしもMySQLサーバーにSplunkをインストールしなければいけないというものではありません、あしからず。_

```MySQL
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| splunktest         |
+--------------------+
2 rows in set (0.00 sec)
```
<img width="623" alt="Screen Shot 2017-04-08 8.05.51 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/1e466fd9-83b2-8220-c18f-cab5e5a70b23.png">
↑ `Identyty`は#4で作成したアイデンティティを指定しましょう

### データ連携設定
Splunk DB Connectは Input / Output / Lookup の3通りの使い方があります。
InputはDBのデータをSplunkにインデックスします。
Outputは逆にSplunkで検索したデータをDBに書き込みします。
LookupはSplunkに取り込んだログ等に付加情報を与えるものです。（SQLでいうとJOINのイメージです）

今回はInputを紹介します。

#### Input設定
1. Splunk DB Connect > [Data Lab] > [Inputs]
2. [New Input]ボタンクリック

今回は以下のように設定しました。

##### **Name Input**
名前や説明、コネクションを設定（コネクションは先ほど作成したものを指定しましょう）
<img width="469" alt="Screen Shot 2017-04-08 8.28.46 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/29470109-d341-d9fc-e488-29f9718a3ce7.png">

##### **Choose and Preview Table**: ここ重要
Batch Input か Rising Column を選択します。

**Batch Input**
毎回同じクエリをデータベースに投げます。したがって、どのレコードまで読み込んだのかを考慮せず全データを毎回取得します。
その場合、Splunkでは前回読み込んだデータを残しつつ、新たに読み込んだデータを追加インデックスします。
毎回テーブルの中身が入れ替わるという場合や1回こっきり読み込む場合には良いでしょう。

**Rising Column**
どのレコードまで読み込んだかをSplunk側で記録して、次の読み込みタイミングで続きから読み込めるように設定できます。

今回はデータが都度INSERTされるテーブルをインデックスするので**Rising Column**を指定します。
ちなみに、テーブル名は`splunk_01`で、中身はこんな感じ。

```splunk_01
mysql> select * from splunk_01;
+---------------------+-----------+-----------+
| date                | column_01 | column_02 |
+---------------------+-----------+-----------+
| 2017-04-03 13:01:38 | abc       |       123 |
| 2017-04-03 13:15:52 | def       |       456 |
| 2017-04-03 13:16:01 | ghi       |      7890 |
| 2017-04-03 15:31:49 | kikeyama  |      1234 |
| 2017-04-03 15:42:05 | splunk    |      5678 |
| 2017-04-03 16:07:40 | hoge      |      1111 |
| 2017-04-03 16:15:21 | melon     |      2222 |
| 2017-04-03 16:16:32 | orange    |      3333 |
| 2017-04-03 16:16:45 | grape     |      4444 |
| 2017-04-03 16:30:37 | suica     |      5555 |
+---------------------+-----------+-----------+
10 rows in set (0.00 sec)
```

Splunkのインデックスではタイムスタンプが重要になるので、`date`カラムをつけました。

Splunk設定はコチラ↓
<img width="579" alt="Screen Shot 2017-04-08 8.44.05 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/417dfde9-160f-33a1-0854-f3541e7c0fb7.png">
WHERE句の `date>?` でどこまで読み込んだかのチェックポイントを指定します。
Splunkは `$SPLUNK_HOME/var/lib/splunk/modinputs/server/splunk_app_db_connect ` にチェックポイントを作成します。
この場合、`date`カラムの値をチェックポイントとしていますので、そのチェックポイントより大きな値を読み込むように`WHERE`で指定しています。

カンの良い方なら気づいたかもしれませんが、タイムスタンプをチェックポイントにするのはオススメできません。
頻繁にINSERTされる場合、レコード書き込みのタイミングとSplunkでの読み込みタイミングしだいでインデックスするデータの重複や漏れが起こり得るからです。
また、NTPによる時刻同期のタイミングしだいでも重複や漏れが起こり得ます。
ということで、オススメはタイムスタンプのレコードではなく、シーケンス番号やトランザクション番号のような、重複せずに昇順降順で表現しやすいユニークな値にすると良いでしょう。

##### **Set Parameters**
読み込む行数の制限や実行間隔を設定します。
<img width="526" alt="Screen Shot 2017-04-08 9.01.47 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/377a35f6-d259-4e80-334f-dbd2ae32d588.png">
Splunkでインデックスする際にタイムスタンプ`_time`フィールドに入れるべき値は`date`カラムと指定しています。
また、今回は1分間隔でSQLを投げてインデックスしています。

##### **メタデータ**
ホスト名、ソース、ソースタイプ、インデックスを指定します。Splunkのデータインプットでお馴染みのやつです。
<img width="591" alt="Screen Shot 2017-04-08 9.10.49 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/f3acf362-2e39-a485-bad5-bf1df3ff2e20.png">

ここまで設定したら [保存] ボタンを押して保存します。


# Splunkで検索してみよう
いいですねえ。インデックスされていますねえ。
<img width="1170" alt="Screen Shot 2017-04-08 9.13.32 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/fbf5c6cb-d58a-dda0-bc0c-9c28a54aa133.png">

イベントを開いてみると、テーブルのカラムがそのままフィールドとして設定されていることがわかります。
さらによく見ると、`date`カラムの値が`_time`フィールドに格納されてますね。
<img width="829" alt="Screen Shot 2017-04-08 9.14.23 PM.png" src="https://qiita-image-store.s3.amazonaws.com/0/174837/16ef8ede-18a7-6d9f-1343-eaf1819f79e6.png">


# 最後に
今回はローカルのMacにSplunkとMySQLを入れて簡易的に設定、動作検証したものとなります。
本番環境で使う際は権限やセキュリティ、パフォーマンスといった観点での注意が必要となります。
詳細な設定方法は[Splunk DB Connectのマニュアル](http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/AboutSplunkDBConnect)をご参照ください。

アプリケーションのトランザクションをDBに格納して保存されている場合、Splunkで詳細に分析・可視化して活用することもできますね。
さらにはログ等他のデータソースと相関検索して分析すればアプリケーション分析やビジネス分析に使えるかもしれません。