勤務実態調査用にWindowsのイベントログから「ログオン・ログオフ時刻」を拾ったCSVデータを整形するEXCELのVBAのサンプルについてです。
目次
- イベントログからの情報取得方法は別記事で
- ログ加工の留意点とサンプルブックの置き場所
- ツールの仕様：処理の流れとポイント説明
- ツールの仕様：EXCELブックのシート構成 ・書式設定
- VBAプログラムのポイント
- 最後にちょっとだけ補足と言い訳（笑）
イベントログからの情報取得方法は別記事で
イベントログからデータを取得して、CSVファイルに保存するまでの方法は、こちらの記事に書いてます。
取得したCSVファイルはこんな感じになります。
#TYPE Selected.System.Diagnostics.Eventing.Reader.EventLogRecord
"TimeCreated","Id","Message"
"2018/01/12 12:42:52","7001","カスタマー エクスペリエンス向上プログラムのユーザー ログオン通知"
"2018/01/12 9:19:16","7002","カスタマー エクスペリエンス向上プログラムのユーザー ログオフ通知"
"2018/01/12 9:14:33","7001","カスタマー エクスペリエンス向上プログラムのユーザー ログオン通知"
"2018/01/12 9:13:26","7002","カスタマー エクスペリエンス向上プログラムのユーザー ログオフ通知"
"2018/01/12 8:16:04","7001","カスタマー エクスペリエンス向上プログラムのユーザー ログオン通知"
"2018/01/12 1:02:41","7002","カスタマー エクスペリエンス向上プログラムのユーザー ログオフ通知"
"2018/01/11 19:09:42","7001","カスタマー エクスペリエンス向上プログラムのユーザー ログオン通知"
"2018/01/11 0:33:50","7002","カスタマー エクスペリエンス向上プログラムのユーザー ログオフ通知"
"2018/01/10 19:22:06","7001","カスタマー エクスペリエンス向上プログラムのユーザー ログオン通知"
"2018/01/10 0:33:46","7002","カスタマー エクスペリエンス向上プログラムのユーザー ログオフ通知"
"2018/01/09 19:29:29","7001","カスタマー エクスペリエンス向上プログラムのユーザー ログオン通知"
これは Windows10で取得したCSVファイルです。
今回は、このようなCSVファイルが取得済であるところからはじめます。
ログ加工の留意点とサンプルブックの置き場所
ログを取得しただけでは、PCを１日に複数回起動・終了したり、徹夜で仕事して、起動した日と終了した日が異なるケースなどが混在していますので、、1日毎に判断して整理する必要があります。
そんなデータを整理して、日付・始業時刻・終業時刻の形にまとめるツールをEXCELのVBAで作ってみました。
EXCELブックは以下においてます。
上記からダウンロードして、自由に改造して使ってもらえればと思います。
ソースを読む際の参考として、このEXCELツールの仕様や、VBAプログラムのポイントなどについて書いておきます。
ツールの仕様：処理の流れとポイント説明
ログから
- 始業時間に相当する情報は、「起動 6005」と「ログオン 7001」。
- 終業時間に相当する情報は、「終了 6006」と「ログオフ 7002」。
このIDの行の日付時刻を拾います。
それ以外のIDは一旦、無視します
（以降では「起動かログオン」のことを「ログオン」、「終了とログオフ」のことを「ログオフ」とまとめて書きます。）
次に、日付・時刻情報を拾った後は、重複しているものを「日付・1日の最初の始業時刻・1日の最終の終業時刻」に整理しています。
そのときに、
- １日に何回もログオン・ログオフを繰り返したり、PCを再起動したりとかするので、ログオン・ログオフのセットがひとつとは限らない。
- ログオン・ログオフが日をまたいでセットである（ようするに、深夜勤務）場合があって、そんな時は前日の例えば25:00のように時刻を認識しないといけない。
ということは考慮しなければいけないという課題があります。
上記の課題に対して、「状態」をもつことで対処しています。
状態は２つ・・「ログオン中」か「ログオフ中」か・・です。
つまり。
新しい行の「日付・時刻」を見るとき、ひとつ前の行が「ログオン」なのか「ログオフ」なのかを状態として保持し、それによって処理を分岐するという正攻法です。
処理分岐の条件としては。
- 変更前の「状態」
- 新しく参照した行が「ログオン」か「ログオフ」かどうか
- 現在処理中の日付と、新しく参照した日付が同じか否か
- 既にセットしてある時刻と新しく参照した時刻の大小
といったところでしょう。
それらを表にしてみるとこんな感じです。
これを素直にVBAマクロに落とし込んでます。
ツールの仕様：EXCELブックのシート構成 ・書式設定
EXCELブックには「event」と「result」の２シートがあります。
eventシートには取得したログのCSVデータを読み込み または 貼り付けます。
resultシートは処理した結果を更新します。
取得したログを、eventシートに貼り付けた例です。
resultシートに結果を表示した例です。
結果表示部分の時間のセル「始業時刻」「終業時刻」「時間」の3列のセルは書式設定で以下のようにして24:00以降の時刻を表示できるようにしています。
[hh]:mm
そうしないと、例えば上記の「31:40」の部分が「7:40」と表示されてしまうからです。
31:40と計算した元になった実際のログがこんな感じ（日跨ぎ勤務）なのに、あきらかに間違いだということになってしまいますから、注意です。
VBAプログラムのポイント
VBAソースにおけるポイントは、EXCEL VBAで24:00以降の時刻を計算する処理が必要なことです。
今回はこんな感じでやってます。
(Hour(datetime) + (24 * diff)) & ":" & Minute(datetime)
この「datetime」には、eventシートA列の「日付・時刻」（Date）がはいってます。
あろ「diff」には、比較する日付の差（同じ日なら0、翌日なら1・・）がはいります。
これは、8/30にログインして、9/2にログオフみたいな・・(;^_^A・・無茶苦茶な日跨ぎ勤務でも一応計算はできるようにするための考慮です。
VBAのDateを使って計算すると、23:59:59を超える時刻をあつかえないので、時間（Hour）と分（Minute）にわけて計算しているというわけです。
最後にちょっとだけ補足と言い訳（笑）
イベントログのログオン・ログオフを整形するEXCELツール。
作ってみると、なかなか便利でした。
今のところ、自分の環境では快適に使えてますし、間違いもなさそうです。
とはいえ。
GitHubにあげているサンプルは、しょせん、お昼休み休憩2回分くらい（正味1時間ちょっと・・）で作ったものです。
ソースもわかりやすさ優先の力技ですし、とても、あらゆるケースを想定してテストできているとは言えない代物です。
なので・・想定外やバグがあったらごめんなさいです。（笑）。
今回はこんなところで。
ではでは。
