やあ、みんな 
だよ
いつもの作者は「の記事もわかりづらいですね」と言われて凹んだので、僕が呼ばれたよ。よろしくね。
今回は以前Splunkのtableの縦横を変換するで書いたことをもう少し優しくかけないかなと思ってね。
Macosxで動かしているので、WindowsやLinuxの人はディレクトリやフォルダを読み替えてね。
今回使うもの
今回は、この起動した時のそのままの画面を使用するよ。
真っ黒い背景にSPL（エス・ピー・エル）を書いていくので、それを**赤枠**の中、`ここにサーチを入力．．．`と書いているところにコピー＆ペーストしてもらって、🔍をクリックすると動くよ
今回使うSPL
index_audit.spl
index=_audit
Audit Splunk activityに書いている_auditを利用していろいろ試してみようと思ったけど
index_internal.spl
index=_internal
やっぱり_internalの方がいろいろと使いやすいね
Statsの列
集計項目が一つ
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 | ... |
statsの結果はbyの引数がそのまま列名になるよ。わかりやすいよね
集計項目が複数
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 | ... |
集計項目はbyの引数の後に追加されるね。
このときは記述した通りの順番になるんだ。
chartの行と列
集計項目が一つ
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 | ... |
chartのbyは
| ー | 第１引数 | 第２引数 |
|---|---|---|
| by | 行名 | 列名 |
と覚えるといいよ。overはわかんないや。 |
statsと違って表示の制限がいろいろとあるので気をつけてね
集計項目が複数
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 | ... |
列が多くなるので、limit=で制限してるよ。
集計項目が複数だと、:で区切られて表示しているね。変えたいときはsep=で指定できるよ。
行を列名とする。
{}をつかう
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 |
trellis表示用に作ったクエリだよ。_ソースタイプ_でトレリス表示してみてね。
eval {fieldA} = fieldBでフィールドの値を元に列名を作れるんだ。
xyseriesをつかう
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 |
行を列名に変換する時は、xyseriesだよね。
行、列名、集計値のフィールドの順番だよ。
4つ以上の引数も使えるけど、わかりづらいよ。
untableを使う
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 |
わかりやすく結果を戻してみたよ。
xyseriesと違って、第２引数と第３引数の名前は任意だよ
transposeを使う
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 |
transposeは単純に行と列を入れ替えるコマンドだよ。
_timeをヘッダーにする時、strftimeを使って文字列にしているのはいい感じでしょう？
まとめ
statsやchartといった集計するコマンドの後、表示の仕方を変えたいなってときは、こんな感じでやってみてね。
列名をそのままで集計しようとすると大変だから、untableを使おうね。
中の人はforeachを使ってそのままやっちゃうみたいだけど真似しないでね。
じゃ、またね〜
リクエスト、待ってま〜す