[AWS QuickSight]ELBのログをURLパスでフィルタする

概要

ELBのアクセスログをQuickSightで分析したいとき、URLのパスでフィルタしたいのはよくある用途かと思います。
ですが、ELBのアクセスログにはフルパスしか存在しないため、ホストやクエリストリングも付与されており、分析がしにくい状況です。
また、QuickSightのフィルタにも正規表現等は存在しないので、文字列操作で無理やりぶっこぬきました。

前提

ELBのアクセスログをQuickSightで分析する方法に関しては下記を参考にさせていただきました。
https://dev.classmethod.jp/cloud/aws/20170226-quicksight-elb/

QuickSightで任意のフィールド（計算フィールド）を追加する方法については下記を参考にしていただければと思います。
https://dev.classmethod.jp/cloud/aws/tips-quicksight-date-type-format/

本編

サービスのURLパスが全て/hoge/で始まる場合

データセット編集画面でフィールドの追加を選択し、Formulaに下記をぶちこむ。

substring(substring({url}, 1, ifelse(locate({url}, '?') = 0, strlen({url}), locate({url}, '?') - 1)), locate({url}, '/hoge/'), strlen(substring({url}, 1, ifelse(locate({url}, '?') = 0, strlen({url}), locate({url}, '?') - 1))) - locate({url}, '/hoge/') + 1)

クエリストリングを切り捨てたのち、ホスト部分を切り捨てています。

co.jpドメインの場合

弊サービスの場合は上記で事足りたのですが、一般的にはログが全て/hoge/で始まってることはないかと思い、ドメインの終端文字列がco.jpで終わるバージョンもつくりました。

substring(substring({url}, 1, ifelse(locate({url}, '?') = 0, strlen({url}), locate({url}, '?') - 1)), locate({url}, 'co.jp') + strlen('co.jp') + ifelse({ssl_protocol} = '-', strlen(':80'), strlen(':443')), strlen(substring({url}, 1, ifelse(locate({url}, '?') = 0, strlen({url}), locate({url}, '?') - 1))) - (locate({url}, 'co.jp') + strlen('co.jp') + ifelse({ssl_protocol} = '-', strlen(':80'), strlen(':443'))) + 1)

http/httpsが混在している場合にも対応しています。

まとめ

こんなんしなくてもできるよ等あったらコメントお願いします。

参考資料

QuickSightの計算フィールドについて