🐨コアラでもわかるSplunkシリーズ　集計結果の列と行を変更する

やあ、みんな　:koala:だよ

:koala:いつもの作者は「:koala:の記事もわかりづらいですね」と言われて凹んだので、僕が呼ばれたよ。よろしくね。 

:koala:今回は以前[Splunkのtableの縦横を変換する](https://qiita.com/toshikawa/items/5bb9315507078cc5c88c)で書いたことをもう少し優しくかけないかなと思ってね。

Macosxで動かしているので、WindowsやLinuxの人はディレクトリやフォルダを読み替えてね。

#今回使うもの
<img width="1392" alt="スクリーンショット 2020-05-11 20.31.59.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/3592e332-a36f-139b-ab44-b1e2c997f313.png">
:koala:今回は、この起動した時のそのままの画面を使用するよ。
真っ黒い背景にSPL（エス・ピー・エル）を書いていくので、それを<font color="red">**赤枠**</font>の中、`ここにサーチを入力．．．`と書いているところにコピー＆ペーストしてもらって、🔍をクリックすると動くよ

#今回使うSPL
```sql:index_audit.spl
index=_audit
```

:koala:[Audit Splunk activity](https://docs.splunk.com/Documentation/Splunk/latest/Security/AuditSplunkactivity)に書いている`_audit`を利用していろいろ試してみようと思ったけど

```sql:index_internal.spl
index=_internal
```
:koala:やっぱり`_internal`の方がいろいろと使いやすいね

#Statsの列
##集計項目が一つ
```sql:stats.spl
index=_internal
| bin _time span=15min
| stats count by _time source sourcetype
```
| _time | source | sourcetype | count |
|:--|:--|:--|--:|
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/btool.log | splunk_btool | 2 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/conf.log | splunkd_conf | 1 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/health.log | splunkd | 276 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/license_usage.log | splunkd | 3 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/metrics.log | splunkd | 3201 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/mongod.log | mongod | 124 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/scheduler.log | scheduler | 3 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/splunkd-utility.log | splunkd | 13 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/splunkd.log | splunkd | ... |

:koala:`stats`の結果は`by`の引数がそのまま列名になるよ。わかりやすいよね
##集計項目が複数
```sql:stats_multi.spl
index=_internal
| bin _time span=15min
| stats count(eval(sourcetype="splunkd")) as sourcetype_splunkd count(eval(sourcetype=scheduler)) as sourcetype_scheduler by _time source sourcetype
```
| _time | source | sourcetype | sourcetype_splunkd | sourcetype_scheduler |
|:--|:--|:--|--:|--:|
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/btool.log | splunk_btool | 0 | 0 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/conf.log | splunkd_conf | 0 | 0 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/health.log | splunkd | 276 | ... |

:koala:集計項目は`by`の引数の後に追加されるね。

:koala:**このときは記述した通りの順番になるんだ。**

#chartの行と列
##集計項目が一つ
```sql:chart_one.spl
index=_internal
| bin _time span=15min
| chart count by _time sourcetype
```
| _time | mlspl-3 | mongod | scheduler | splunk_archiver-2 | splunk_web_access | splunk_web_service | splunkd | splunkd_access | splunkd_stdout | splunkd_ui_access | OTHER |
|:--|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|
| 2020/05/17 17:15:00 | 0 | 124 | 3 | 0 | 25 | 229 | 4092 | 93 | 0 | 483 | 4 |
| 2020/05/17 17:30:00 | 0 | 0 | 0 | 0 | 0 | 0 | 4576 | 0 | 0 | 270 | ... |

:koala:`chart`の`by`は

| ー | 第１引数 | 第２引数 |
|:-:|:-:|:-:|
| by | 行名 | 列名 |
と覚えるといいよ。`over`はわかんないや。

:koala:`stats`と違って表示の制限がいろいろとあるので気をつけてね
##集計項目が複数
```sql:chart_multi.spl
index=_internal
| bin _time span=15min
| chart limit=2 count as Count max(_time) as Max by _time sourcetype
```

| _time | Count: OTHER | Count: splunkd | Count: splunkd_ui_access | Max: OTHER | Max: splunkd | Max: splunkd_ui_access |
|:--|--:|--:|--:|--:|--:|--:|
| 2020/05/17 17:15:00 | 478 | 4092 | 483 | 1589703300 | 1589703300 | 1589703300 |
| 2020/05/17 17:30:00 | 0 | 4576 | 270 |   | 1589704200 | ... |

:koala:列が多くなるので、`limit=`で制限してるよ。

:koala:集計項目が複数だと、`:`で区切られて表示しているね。変えたいときは`sep=`で指定できるよ。

#行を列名とする。
###{}をつかう
```sql:eval.spl
index=_internal sourcetype=splunkd*
| bin _time span=15min
| stats count by _time sourcetype source
| eval {source} = count
| fields - count
```

| _time | sourcetype | source | /Applications/Splunk/var/log/splunk/conf.log | /Applications/Splunk/var/log/splunk/health.log | /Applications/Splunk/var/log/splunk/health.log.1 | /Applications/Splunk/var/log/splunk/license_usage.log | /Applications/Splunk/var/log/splunk/metrics.log | /Applications/Splunk/var/log/splunk/metrics.log.1 | /Applications/Splunk/var/log/splunk/splunkd-utility.log | /Applications/Splunk/var/log/splunk/splunkd.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_stderr.log | /Applications/Splunk/var/log/splunk/splunkd_stdout.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/watchdog/watchdog.log |
|:--|:--|:--|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|
| 2020/05/17 17:15:00 | splunkd | /Applications/Splunk/var/log/splunk/health.log |   | 276 |   |   |   |   |   |   |   |   |   |   |   |
| 2020/05/17 17:15:00 | splunkd | /Applications/Splunk/var/log/splunk/license_usage.log |   |   |   | 3 |   |   |   |   |   |   |   |   |   |
| 2020/05/17 17:15:00 | splunkd | /Applications/Splunk/var/log/splunk/metrics.log |   |   |   |   | 3201 |   |   |   |   |   |   |   |   |

:koala:`trellis`表示用に作ったクエリだよ。_ソースタイプ_でトレリス表示してみてね。

:koala:`eval {fieldA} = fieldB`でフィールドの値を元に列名を作れるんだ。

###xyseriesをつかう
```sql:xyseries.spl
index=_internal sourcetype=splunkd*
| bin _time span=15min
| stats count by _time sourcetype source
| xyseries _time source count
```

| _time | /Applications/Splunk/var/log/splunk/conf.log | /Applications/Splunk/var/log/splunk/health.log | /Applications/Splunk/var/log/splunk/health.log.1 | /Applications/Splunk/var/log/splunk/license_usage.log | /Applications/Splunk/var/log/splunk/metrics.log | /Applications/Splunk/var/log/splunk/metrics.log.1 | /Applications/Splunk/var/log/splunk/splunkd-utility.log | /Applications/Splunk/var/log/splunk/splunkd.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_stderr.log | /Applications/Splunk/var/log/splunk/splunkd_stdout.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/watchdog/watchdog.log |
|:--|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|--:|
| 2020/05/17 17:15:00 | 1 | 276 |   | 3 | 3201 |   | 13 | 597 | 93 | 1 |   | 483 | 2 |
| 2020/05/17 17:30:00 |   | 360 |   |   | 4174 |   |   | 42 |   |   |   | 270 |   |

:koala:行を列名に変換する時は、`xyseries`だよね。

:koala:行、列名、集計値のフィールドの順番だよ。

:koala:4つ以上の引数も使えるけど、わかりづらいよ。

##untableを使う
```sql:untable.spl
index=_internal sourcetype=splunkd*
| bin _time span=15min
| stats count by _time sourcetype source
| xyseries _time source count
| untable _time source count
```
| _time | source | count |
|:--|:--|--:|
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/conf.log | 1 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/health.log | 276 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/license_usage.log | 3 |
| 2020/05/17 17:15:00 | /Applications/Splunk/var/log/splunk/metrics.log | 3201 |

:koala:わかりやすく結果を戻してみたよ。

:koala:`xyseries`と違って、**第２引数と第３引数の名前は任意だよ**

###transposeを使う
```sql:transpose.spl
index=_internal sourcetype=splunkd*
| bin _time span=15min
| stats count by _time sourcetype source
| eval _time=strftime(_time,"%T")
| transpose 0 header_field=_time

```
| column | 17:15:00 | 17:30:00 | 17:45:00 | 18:00:00 | 18:15:00 | 18:30:00 | 18:45:00 | 19:00:00 | 19:15:00 | 19:30:00 | 19:45:00 | 20:00:00 | 20:15:00 | 20:30:00 | 20:45:00 | 21:00:00 | 21:15:00 | 21:30:00 | 21:45:00 | 22:00:00 | 22:30:00 | 23:30:00 | 00:30:00 | 02:00:00 | 02:15:00 | 02:30:00 | 02:45:00 | 03:00:00 | 05:00:00 | 06:30:00 | 06:45:00 | 07:00:00 | 07:15:00 | 07:30:00 | 07:45:00 | 08:00:00 | 08:15:00 | 08:30:00 | 08:45:00 | 09:00:00 | 09:15:00 | 09:30:00 | 09:45:00 | 10:00:00 | 10:15:00 | 10:30:00 | 10:45:00 | 11:00:00 | 11:15:00 | 11:30:00 | 11:45:00 | 12:00:00 | 12:15:00 | 12:30:00 | 12:45:00 | 13:00:00 | 13:15:00 | 13:30:00 | 13:45:00 | 14:00:00 | 14:15:00 | 14:30:00 | 14:45:00 |
|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|:--|
| sourcetype | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_ui_access | splunkd_access | splunkd_access | splunkd_access | splunkd_access | splunkd_access | splunkd_access | splunkd_access | splunkd_access |
| source | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_ui_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log | /Applications/Splunk/var/log/splunk/splunkd_access.log |
| count | 483 | 483 | 483 |

:koala:`transpose`は単純に行と列を入れ替えるコマンドだよ。

:koala:`_time`をヘッダーにする時、`strftime`を使って文字列にしているのはいい感じでしょう？

#まとめ
:koala:`stats`や`chart`といった集計するコマンドの後、表示の仕方を変えたいなってときは、こんな感じでやってみてね。

:koala:列名をそのままで集計しようとすると大変だから、`untable`を使おうね。

:koala:中の人は`foreach`を使ってそのままやっちゃうみたいだけど真似しないでね。

:koala:じゃ、またね〜

:koala:リクエスト、待ってま〜す