PowerBIを使用してSquidのaccess.logを可視化する

今回はPowerBIを使用して、Squidのaccess.logを可視化してみましたという記事です。

事の発端は、なんだか最近プロキシを経由させているOutlook(OWA)の通信がなんだか遅いという声から、応答時間を可視化してみようと考えたのですが、ElasticSerch & Kibanaはあんまり可視化に向いてない。。かと言って、Excelでaccess.log開いて分析していくのもなかなか骨です。そこで、先月勉強会に参加させていただいたPowerBIを使用して日ごとの応答時間を可視化して見てみることにしました。

ちなみにPowerBIとこの記事に冠しているのですが、PowerBIの具体的な操作方法とかは載せていません・・・ごめんなさい・・

まず、Squidのログデフォルトのログフォーマットでは応答時間がaccess.logに記録されないので、logformatを変更するところから開始します。

１．LogFormatに応答時間を記録可能なように%trを追加する。

SquidのLogFormatはデフォルトの状態では、応答時間が取得できないので、応答時間が取得可能なようにLogFormatを変更。ついでにOutlook関連のログがaccess.logとは別のログに出力されるように変更します。

———squid.confの最後のほう——–

#下のがデフォルトのログ　squidかcommonに設定されていると思います。これはコメントアウトします。

access_log /var/log/squid/access.log common　←コメントアウト

↓追加

logformat logch %>a %ui %un [%tl] “%rm %ru HTTP/%rv” %>Hs %<st “%{Referer}>h” “%{User-Agent}>h” %Ss:%Sh %tr

access_log /var/log/squid/access_combined.log logch

※%trが応答時間を出力するフォーマット

#ついでに、Outlook関連のログを通常のaccess.logとは別のログに出すようにします。

#Outlookへのアクセスを別ログに出力1

acl acl_outlook url_regex ^outlook.office.com:443

access_log /var/log/squid/access_outlook.log logch acl_outlook

#Outlookへのアクセスを別ログに出力2

#他にもOutlookのURLはあるので同じ手順で入れていきます。

acl acl_outlook2 url_regex ^r4.res.office365.com:443

access_log /var/log/squid/access_outlook2.log logch acl_outlook2

———編集ここまで——–

Configのチェック

squid -k check

で、問題がなければサービスリロードして終了

service squid reload

これで、access_outlook2.logと、access_outlook.logに、Outlookのログがレスポンスタイムとともに記録されるようになりました。

次はこのログをPowerBIに取り込んで可視化していきます。

ここで、古いログは、gz形式で圧縮されると思いますが、これは解凍してから取り込んでください。PowerBIで、データソースをフォルダからに指定した場合、中にgzファイルが含まれていても特になんの警告も出ずに読み込まれますが、文字化けします。

で、PowerBIで数日分のProxy Serverの応答時間推移を可視化したものが下図です。

access.logの日付の部分を日付と時刻に分割して、分秒の部分は削除しています。凡例に、日付を入れて、軸に時刻、値に応答時間の平均を入れていきます。フィルターで日付は、確認したい対象の日だけに絞りましょう。

ちなみに、平均の値を出したい場合には、事前にクエリの編集にて、応答時間の列を”文字列”から、”数値”にしてあげる必要があります。この作業をしないと、合計や最小値、カウントなども表示できません。ちなみに、データラベルをONにしてあげると、こんなふうに値毎にラベルがつきます。

で、この図からわかったのが、以下点です。

・なぜか応答時間が毎日右肩あがりに上がってる・・・

・朝と夕方にOutlook(OWA)が重いって言われているけど、応答時間の推移ではその相関性は見つからない。

実際には、もっと色々powerBIで分析をしてみているのですが、データはこんな感じで。

で、応答時間を可視化した結果、新しい課題が見つかったわけですが、この応答時間が毎日右肩あがりに推移していく件への対処は次回の記事に続きます。。。