はじめに

社内SE業務を担当していた頃、管理システムから分析レポートを自動生成するツールの開発を担当する機会がありました。
オリジナルで設計・開発したものになるので紹介させていただきます。

案件概要

常駐先の現場ではLanscopeによる業務・資産管理を実施していました。
管理データの活用について日次で分析レポートを取得したいという要望をいただき、レポートの自動生成ツールを開発することになりました。

要望のあったデータ

• 印刷記録：どのPCからどのような印刷がおこなわれたか（ファイル名／印刷ページ数）
• USB機器の利用記録：USB機器の接続／USBへのファイル移動

目的
情報漏洩事故防止／確認

ツール検討

Lanscopeではデータ出力については専用コンソール／ブラウザにて対応可能ですが、オペレータが介在しないと対応ができないものでした。
自動化を目指す上で、LanscopeのバックグラウンドにあるDBに対して直接アクセスしてデータを取得するツールを開発しました。
出力したデータからレポートを生成するため、Excelにてテンプレートを用意してツールと連動させることで機能を実現しました。

開発内容

データ出力／拡張／仕分け

採用技術：VBS＋SSMS（SQL Server Managemant Studio）

VBSにてツールの全体処理を作成します。
DBに対して直接SQLを実行することで、データ取得を実施します。
取得したデータに対して、分析単位（支店単位）でデータの仕分けを実施し、ファイル分割を行います。
分割の際に、レポート生成を想定してデータの補填や拡張なども実施します。

図．VBSによるデータ出力

レポート生成

採用技術：Excelマクロ（VBA）

ファイル単位に指定データをテンプレートにインポートする処理を実行します。
データインポート後に、保存形式をxlsm（マクロ付きExcelブック）からxlsx（Excelブック）に変更し、マクロの内容がレポートファイルに残らないようにしています。

図．Excelマクロによるテンプレート

全体フロー

ツール実行時の指定として、日付指定および実行日前日「yesterday」が指定できるようにしました。
「yesterday」を設けることで、タスク登録時の引数設定を実行の度に修正することなく、日次での運用が可能になります。
全体としては、データ出力／仕分けを実施したデータを使って、そのままExcelテンプレートによるレポート生成を連続で実行するようにしました。

図．ツール全体フロー

設計のポイント

• 仕分けは本店／支店単位。プリンターのIPアドレスから判別を実施。
• 支店毎にデータを仕分け、それぞれ別ファイルにて中間ファイルを生成。
• 出力データの中に半角カンマが含まれるケース（ファイル名に含まれている）があることが判明。

SQLによるデータ出力時にセパレータを「｜」(パイプ)を指定することで、データの体裁が崩れないように工夫しました。
出力後の加工時に半角カンマを全角カンマに置換することも合わせて実施しました。

• テンプレートはあらかじめピボットテーブル／グラフを定義したシートを用意。

データのインポートおよび更新だけでレポートが仕上がるように設計。
インポート領域は出力データの平均より2倍のレコード数を想定して準備を行い、余力を持たせました。

• バックグラウンドでの自動実行を想定しているのでメッセージ出力はなし。

コマンドのみで実行可能にしました。