何をするのか
PowerShellでCSVのログファイルを読み込んで、加工して、出力したい。
- 不要な列を消したい
- 新たに列を加えたい
- カンマ区切り(CSV)でなくタブ区切り(TSV)にしたい
- ヘッダ行無しで出力したい
実際に書いてみると、あれっこれどうするんだ？と詰まって検索に走った個所がいくつもあったので、後学のためまとめなおす。
前提
環境
Windows 10 1903
WindowsServer 2016
PowerShell 5
事情話
とあるログを加工してDB投入する用ファイルにするスクリプトがある。前任者から引き継いだ当時は、知識がなくて内容を読めないまま使い方だけ引き継いで使っていた。
いつまでも自力でメンテできない工程を残したままなのもまずかろうと、書かれてる内容を調べてどうにか把握。自分の環境で扱いやすいPowerShellで書き直すことにした。
Logstashで書いてDB投入までまとめちゃっても？とも思ったが、DB投入のスクリプトが別途あるので、今回は当該スクリプトがやっている箇所を置き換えるだけにした。
元ログはどんなか
元ログ
ファイルを入れたり出したりするあるシステムのユーザー操作ログ。
いろんな操作区分のログが100万行くらいある。
整然としてて、今見ると加工しなくてもこのまま次に渡したら？という気もする。
何で加工しなければならなかったかの事情は不明だが、当時はそれが必要だったんだろう。
#ヘッダー行無し
#日付時刻,区分,ID,システム名,ファイルサイズ,クライアントIP,ファイルネーム,サーバー名
"2020/10/01 16:10:57","ダウンロード","katou","system","477285","192.168.10.122","/katou/20201001161057/filename.docx","server1"
"2020/10/11 16:02:12","ダウンロード","katou","system","691827","192.168.10.122","/katou/20201011160212/filename.jpg","server1"
"2020/10/23 15:48:33","アップロード","takahashi","system","387521","192.168.10.101","/takahashi/20201023154833/filename.png","server1"
"2020/10/25 09:26:35","削除","sasaki","system","546178","192.168.10.138","/sasaki/20201025092635/filename.doc","server1"
"2020/10/26 16:17:58","ダウンロード","kinoshita","system","265213","192.168.10.114","/kinoshita/20201026161758/filename.xlsx","server1"
"2020/10/28 12:04:27","アップロード","takahashi","system","735834","192.168.10.101","/takahashi/20201028120427/filename.pptx","server1"
"2020/10/29 12:29:05","更新","katou","system","724337","192.168.10.122","/katou/20201029122905/filename.pptx","server1"
"2020/10/31 11:20:18","ダウンロード","kinoshita","system","648783","192.168.10.114","/kinoshita/20201031112018/filename.jpg","server1"
"2020/10/31 13:25:14","更新","takahashi","system","77063","192.168.10.101","/takahashi/20201031132514/filename.doc","server1"
"2020/10/31 17:22:11","削除","suzuki","system","697425","192.168.10.109","/suzuki/20201031172211/filename.xlsx","server1"
ログ加工スクリプトの大まかな流れ
ログを読み込みCSVとしてパース。
↓
"更新"、"削除"の行は捨てる。必要な行だけ次処理へ。
↓
常に"system"としか入ってないシステム名カラムはいらないので除去。
↓
ファイル名から拡張子を取り出して、末尾に新しいカラムとして追加。
↓
タブ区切りテキストにする。
↓
ファイルへ出力。ヘッダー行は無し。
100万行が65万行くらいに減る。
"system"は除去するのに"server1"は残すのなんでだとか、そもそも除去しなくてもDB投入時に無視すりゃいいんじゃとか、出力ファイルがTSVでなければならない理由はなんだとか、ほじくり返しても無益が見えてることはひとまず思考ストップして、出力が同じになるようにどう書いたらいいか考えてみる。
どう書くか
ログを読み込みCSVとしてパース(とカラム追加)
Import-Csv。ログにはファイル名が含まれていて、データ部分に半角カンマが含まれている可能性は大いにある。Import-Csvはそういうカンマが入っていても大丈夫。データがダブルクォーテーションにくくられていたりいなかったりしても問題なし。こういうめんどくさいことは言語に任せたい。
$InputFile = "log.log"
$CsvHeader = @("timestamp","order","id","system","size","clientip","filename","server","extension")
Import-Csv $InputFile -Encoding Default -Header $CsvHeader
#この後パイプで繋げて処理
ヘッダー行が無いCSVなので、パラメータでヘッダー名を指定する必要がある。
(順番後先になるが)このヘッダー指定でカラム追加もやってしまう。ヘッダー指定の箇所で元のデータ(8つ目まである)にない9つ目のカラム"extension"を指定してやると、読み込み時に何も入ってないプロパティを追加してくれる。
項目の並び順は維持される
読み込んでオブジェクトになった後、プロパティの並び順て維持されるのか？と疑問がわいたので検索した。ハッシュテーブルとは違い、並び順は維持されるもの…らしい。
PSCustomObject について知りたかったことのすべて
不要カラム削除
入力からパイプで繋げて、Select-Object * -ExcludePropertyでプロパティ名を指定して除外できる。下の記事を参考にした。
powershell - ヘッダー名でCSV列を削除する - Stack Overrun
#インポート後systemカラムを除去
$CsvHeader = @(
"timestamp","order","id","system","size","clientip","filename","server","extension"
)
Import-Csv $InputFile -Encoding Default -Header $CsvHeader |
Select-Object * -ExcludeProperty system
#この後ForEach-Objectに繋げる
行末にカラム追加
追加自体はCSV読み込み時に行ってあり、空っぽプロパティが既にある。
入れたいデータはfilenameカラムデータの拡張子部分。パス名やファイル名から拡張子部分だけ取り出す方法は下の記事を参考にした。この方法では拡張子は.extとピリオド付きで出てくるため、1文字除去しておく。
パスの文字列から拡張子やファイル名を取り出す方法
#こんな形で名前が入っている
$Filename = "/1234/20201020161723/filename.doc"
#拡張子だけ取得 先頭ピリオドは除去
$Extension = ([System.IO.Path]::GetExtension($Filename)).Remove(0,1)
#"doc"が入っている
$Extension
タブ区切りテキストにしファイルへ出力
Export-Csv -Delimiter "`t"、ConvertTo-Csv -Delimiter "`t"でタブ区切りテキストにして出力できる。
素直に考えればExport-Csvでファイルに書けばいいのだが、過去の出力ログはヘッダー行無しなので、整合性をとるためヘッダー行無しで出力する必要がある。
残念ながらどちらのコマンドレットも必ずヘッダー行が付くため、1行目をスキップして出力する方法をとった。下記事で紹介されているSelect-Object -Skip 1を使う方法をとる。
【PowerShell】CSVの出力時にヘッダ行を削除する方法について
#$Segmentはカンマ区切り・ヘッダー行ありで入ってる
#タブ区切り・へッダー行無しでファイル出力したい
$Segment |
ConvertTo-Csv -NoTypeInformation -Delimiter "`t" |
Select-Object -Skip 1 |
Out-File -LiteralPath $OutputFile -Encoding utf8 -Append
出来たスクリプト
#ヘッダー行無しTSV出力
function Out-TextSegment( $Segment ){
$Segment |
ConvertTo-Csv -NoTypeInformation -Delimiter "`t" |
Select-Object -Skip 1 |
Out-File -LiteralPath $OutputFile -Encoding utf8 -Append
}
#入出力ファイル
$InputFile = "log.log"
$OutputFile = "log_after.tsv"
New-Item $OutputFile -Force > $null
#出力用配列の制御用
$OutputArray = New-Object System.Collections.ArrayList
$LoopCount = 0
#入力ファイルをCSVインポート、加工して、出力ファイルへ保存
$CsvHeader = @(
"timestamp","order","id","system","size","clientip","filename","server","extension"
)
Import-Csv $InputFile -Encoding Default -Header $CsvHeader |
Select-Object * -ExcludeProperty system |
ForEach-Object{
if( ($_.order -eq "ダウンロード") -or ($_.order -eq "アップロード") ){
#拡張子カラム追加
$_.extension = ([System.IO.Path]::GetExtension($_.filename)).Remove(0,1)
$OutputArray.Add($_) > $Null
$LoopCount++
}
if( $LoopCount -eq 30000 ){
#ファイル出力し出力用配列のカウントリセット
Out-TextSegment $OutputArray
$OutputArray = New-Object System.Collections.ArrayList
$LoopCount = 0
}
}
Out-TextSegment $OutputArray
1行ずつ出力すると時間がかかるのである程度まとめてから書き出す。
うちの環境では3万行毎に出力すると、1行ずつの場合と比べ7～8倍くらい早かった。
じゃあ100万行まとめたら最速なのかというとそんなことはなく、$OutputArrayが大きくなりすぎると逆に時間がかかるようだ。
出力結果は旧スクリプトの出力と一致していたのを確認できた。これで完了。
次の課題
書いてて何度も「元のログから直接Elasticsearchへ投入するの書けば？それ書くかLogstashで書こうぜ？」て思った。次にまとまった時間があったら書き換えたい。