Splunkのtableの縦横を変換する

# Dataの準備
```splunk:SPL
| makeresults count=4
| eval process1=random() % 100.000000
| eval process2=random() % 100.000000
| eval process3=random() % 100.000000
| eval total = process1 + process2 + process3
| fields - _time
```
各プロセスの経過時刻をepochタイムっぽく作成
![qiita1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/fbee6d57-2efe-11b5-05b9-169ad9cb8873.jpeg)

# 縦横変換
```splunk:SPL
| eval temp=1
| untable temp process time
```
ダミーのフィールド（ここではtemp）を作成して、`untable`で縦横変換
![qiita2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/74335c1b-1286-b71b-027c-d8765ca25956.jpeg)

これは、後`stats`とかでまとめる時に使う

```splunk:SPL
| stats min(time) avg(time) max(time) by process
```

![qiita3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/2744e1f5-5301-77d4-7897-561909d54245.jpeg)

#transpose
[Splunk Docs](https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Transpose)

```Splunk:SPL
| makeresults count=4
| eval process1=random() % 100.000000
| eval process2=random() % 100.000000
| eval process3=random() % 100.000000
| eval total = process1 + process2 + process3
| fields - _time
| transpose
```

こちらのコマンドもある。結果は

| column | row 1 | row 2 | row 3 | row 4 |
|:--|--:|--:|--:|--:|
| process1 | 22.0000000 | 79.0000000 | 34.0000000 | 44.0000000 |
| process2 | 22.0000000 | 61.0000000 | 33.0000000 | 78.0000000 |
| process3 | 93.0000000 | 4.00000000 | 84.0000000 | 54.0000000 |
| total | 137.0000000 | 144.0000000 | 151.0000000 | 176.0000000 |

デフォルトだとフィールド名は`row \d`になるのが気になるといえば気になる。 なお、デフォルトは５行しか変換しないので、大きな行だと`transpose 0`と指定が必要（*Splunkの検索って結構表示制限があるので、結果が出ない時は制限を疑う必要があるな・・・*:sweat:）

オプションの`header_field=<field>`でフィールド名を指定できる。
また、`| rename "row *" as row_*` で一括変更可能

`eval`で計算が可能なのでこの形も使いやすい。

#応用

```Splunk:SPL
| tstats count(_indextime) as count where index=_internal by _time span=1h source
| xyseries _time source count
```
一応`metadata`バージョンも

```Splunk:SPL
| metadata type=sources index=_internal
| table lastTime source totalCount
| rename lastTime as _time
| xyseries _time source totalCount
```
ログがキチンと入っているのかの確認用。`tstats`を使っているので早い。
このように`timechart`コマンド等で表示される横の時間を固定して

| _time | source | count |
|:--|:--|--:|
| 2019/11/01 20:00 | /Applications/Splunk/var/log/splunk/health.log | 720 |

を

|_time|	/Applications/Splunk/var/log/splunk/health.log|...|
|:-:|--:|--:|
2019/11/01 20:00|720|| 

こう変換している。

#####2019/11/18 追記
`xyseries`を使用しなくても、`chart`でいける。

```Splunk:SPL
| tstats count(_indextime) as count where index=_internal by _time span=1h source
| chart sum(count) by _time source
```
**by**の後に２つのフィールド、特にこの場合の*_time*は複数の*source*にまたがっているのがキモ。
わかりやすい方で

-----

ダッシュボード向けとして

```Splunk:SPL
index=_internal 
| stats count by source
| eval tmp="past 15 min"
| xyseries tmp source count
```

![qiita1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/130729/16ee6539-972a-b6c0-39f6-f0d6e0e36aec.png)
トレリスを使って*X-Axis*の表示をオフにしてみるとこんな感じでできる。

\:sweat: `chart`の_over_と_by_がいつもこんがらがるので

```Splunk:SPL
index=_internal 
| chart limit=0 count over sourcetype by source
| transpose 0 header_field=sourcetype column_name="source"
```

`over`が**行**、`by`が**列**。**行列**と覚えて、`transpose`できれいに変換

#表の縦横変換
https://community.splunk.com/t5/Splunk-Search/Search-Result-Sorting-as-per-values-in-a-field/m-p/534049/highlight/true#M150932
ここで、自分でも結構よくできたと思う

```javascript:make_table.spl
| makeresults
| eval _raw="Name,WW,Name2,Result,Type,Value
Abc,50.5,Prod,Pass,A,1280
Xyz,47.2,Prod,Pass,Dr,Sound
Abc,51.3,Test,Fail,,
Def,8.2,Test,Fail,Td,Wifi
Def,44.2,Prod2,Pass,Gf,Printer
Xyz,6.2,Test1,Fail,Fr,Audio
Abc,451,Prod1,Pass,Cs,Audio"
| multikv forceheader=1
| table Name,WW,Name2,Result,Type,Value
| fillnull Name,WW,Name2,Result,Type,Value value="NULL"
| sort Name WW
| streamstats dc(Name) as session
| streamstats count by session
| eval session=session.":".count
| xyseries session Name,WW,Name2,Result,Type,Value
| rename Name2:* as *:2:Name2,WW:* as *:1:WW, Result:* as *:3:Result, Type:* as *:4:Type, Value:* as *:5:Value
| eval session=mvindex(split(session,":"),-1)
| untable session fieldname value
| xyseries session fieldname value
```

|Name|	WW|	Name2|	Result|	Type|	Value|
|:-:|:-:|:-:|:-:|:-:|:-:|
Abc|	50.5|	Prod|	Pass|	A|	1280
Xyz|	47.2|	Prod|	Pass|	Dr|	Sound
Abc|	51.3|	Test|	Fail|	| 	 
Def|	8.2|	Test|	Fail|	Td|	Wifi
Def|	44.2|	Prod2|	Pass|	Gf|	Printer
Xyz|	6.2|	Test1|	Fail|	Fr|	Audio
Abc|	451|	Prod1|	Pass|	Cs|	Audio

を

session|	 Abc:1:WW|	 Abc:2:Name2|	 Abc:3:Result|	 Abc:4:Type|	 Abc:5:Value|	 Def:1:WW|	 Def:2:Name2|	 Def:3:Result|	 Def:4:Type|	 Def:5:Value|	 Xyz:1:WW|	 Xyz:2:Name2|	 Xyz:3:Result|	 Xyz:4:Type|	 Xyz:5:Value
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
1|	50.5|	Prod|	Pass|	A|	1280|	8.2|	Test|	Fail|	Td|	Wifi|	6.2|	Test1|	Fail|	Fr|	Audio|
2|	51.3|	Test|	Fail|	NULL|	NULL|	44.2|	Prod2|	Pass|	Gf|	Printer|	47.2|	Prod|	Pass|	Dr|	Sound|
3|	451|	Prod1|	Pass|	Cs|	Audio|	 	 	 	 	 	 	 	 	 	 

にしている。

##解説

- Splunkは同じフィールド名を複数できないので、どうしよっかな〜と思っていたら、`chart`や`xyseries`で出てくる`XX:YY`がふと降りてきた。
- ２つの`streamstats`はいつもの**session**作り。
- `xyseries`のあと`rename`をしているのがここの肝。
- `xyseries`だと引数の３番目以降は全部値になってくれる。それを`rename`で反転すると同時に途中に数字をいれて整列させている。（１０以上だったらABCにしていたと思う）
- `untable`と`xyseries`は是非一回毎結果を見てもらいたいと思います。`xyseries`で第１引数がまとめられるのを使ったテクニックです。

久しぶりによくできたクエリーだと思います。


#まとめ
```Splunk:SPL
index=_internal 
| chart limit=0 count over sourcetype by source
`comment("ここで結果を確認")`
| transpose 0 header_field=sourcetype column_name="source"
`comment("ここで結果を確認")`
| untable source header count
`comment("ここで結果を確認")`
| xyseries source header count
```

`chart`、`stats`、`table`の結果を`transpose`、`untable`、`xyseries`を使って自在に変換できるとより**Happy Splunk!**を実現できると思います。



 - 2019/11/2　transposeの説明と応用例を追加　
 - 2019/11/18　xyseriesがchartで代用可能を追加
 - 2020/12/25　表の縦横変換を追加