"BOKU"のITな日常

62歳・文系システムエンジニアの”BOKU”は日々勉強を楽しんでます

イベントログから抽出したログオン・オフ時刻を見やすいように整形するEXCELのVBAマクロなど。

勤務実態調査用にWindowsのイベントログから「ログオン・ログオフ時刻」を拾ったCSVデータを、見やすい形に整形するEXCELVBAのサンプルを作ってみます。

f:id:arakan_no_boku:20191226010813p:plain

 

この記事の前提

 

イベントログからデータを取得して、CSVファイルに保存するまでの方法は、こちらの記事に書いてます。

arakan-pgm-ai.hatenablog.com 

取得した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ファイルが取得済であるところからはじめます。 

 

EXCEL+手作業でもできるけど、若干面倒ではある

 

上記のCSVデータをEXCELで開けば、手作業でも整理できます。

ただ、手間はかかります。

PCを1日に複数回起動・終了したり、徹夜で仕事して、起動した日と終了した日が異なるケースなどを、1日毎に判断して整理しないといけないからです。

今回は、そういう整理を自動的に行い、日付・始業時刻・終業時刻の形にまとめるツールをEXCELVBAで作ろうというのがテーマです。

いちおう。

自分が、サンプル的に作ったEXCELブックは以下においてます。

github.com

手っ取り早くやりたいという方は、上記からダウンロードして、自由に改造して使ってもらえればと思います。

さて。

ここから、このEXCELツールの仕様や、VBAプログラムのポイントなどを説明します。

 

仕様検討

 

まず。

ログから得られる情報から。

  • 始業時間に相当する情報は、「起動 6005」と「ログオン 7001」。
  • 終業時間に相当する情報は、「終了 6006」と「ログオフ 7002」。

このIDの行の日付時刻を拾います。

それ以外のIDは一旦、無視します

なお。

以降では「起動かログオン」のことを「ログオン」、「終了とログオフ」のことを「ログオフ」とまとめて書きます。

さて。

日付・時刻情報を拾った後は、重複しているものを「日付・1日の最初の始業時刻・1日の最終の終業時刻」に整理していきます。

そのときに、考慮しないといけない課題として。

  • 1日に何回もログオン・ログオフを繰り返したり、PCを再起動したりとかするので、ログオン・ログオフのセットがひとつとは限らない。
  • ログオン・ログオフが日をまたいでセットである(ようするに、深夜勤務)場合があって、そんな時は前日の例えば25:00のように時刻を認識しないといけない。

ということがあります。

 

今の状態を意識して処理するようにすればいけそうだな

 

上記の課題に対する対処として、「状態」をもちます。

状態は2つ・・「ログオン中」か「ログオフ中」か・・です。

つまり。

新しい行の「日付・時刻」を見るとき、ひとつ前の行が「ログオン」なのか「ログオフ」なのかを状態として保持し、それによって処理を分岐するという正攻法です。

処理分岐の条件としては。

  • 変更前の「状態」
  • 新しく参照した行が「ログオン」か「ログオフ」かどうか
  • 現在処理中の日付と、新しく参照した日付が同じか否か
  • 既にセットしてある時刻と新しく参照した時刻の大小

といったところでしょう。

それらを表にしてみるとこんな感じです。

f:id:arakan_no_boku:20191227010201p:plain

これを素直にVBAマクロに落とし込めばいいはずです。

 

EXCELブックを用意する

 

EXCELブックには「event」と「result」の2シートを用意します。

  • eventに取得したログのCSVデータを読み込み または 貼り付けます。
  • resultに処理した結果を更新します。

 

eventシート

 

取得したログを、eventシートに貼り付けるのは手作業にします。

CSVなので以下のように各セルに展開するくらいは簡単なはずなので。

f:id:arakan_no_boku:20191227003808p:plain

 

resultシート 

 

整形ボタンを押した結果を表示した状態です。

f:id:arakan_no_boku:20191227010540p:plain

結果表示部分の時間のセル「始業時刻」「終業時刻」「時間」の3列のセルは書式設定で以下のようにして24:00以降の時刻を表示できるようにします。

[hh]:mm

 

そうしないと、例えば上記の「31:40」の部分が「7:40」と表示されてしまいます。

31:40と計算した元になった実際のログがこんな感じ(日跨ぎ勤務)なのに、あきらかに間違いだということになってしまいますから、注意です。

f:id:arakan_no_boku:20191227011031p:plain

 

 

VBAプログラムのポイント

 

さて。

今度は、VBAソースにおけるポイントです。

2つあります。

  • EXCEL VBAで24:00以降の時刻を計算する
  • ログの「2019/8/29 17:40:00」みたいな情報の日付と時刻を分離して扱う

です。

 

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)にわけて計算しているというわけです。

 

ログの「2019/8/29 17:40:00」みたいな情報の日付と時刻を分離して扱う

 

イベントログで出力するのは「日付・時刻」セットです。

そのままVBAのDateに読み込んでも、「日付・時刻」セットは変わりません。

なので。

  • DateValue(datetime)
  • TimeValue(datetime)

のように、日付だけとりだしたり、時刻だけとりだして処理してます。

いちおう、こんな感じです。

 

まとめ

 

イベントログのログオン・ログオフを整形するEXCELツール。

作ってみると、なかなか便利でした。

今のところ、自分の環境では快適に使えてますし、間違いもなさそうです。

 

とはいえ。

GitHubにあげているサンプルは、しょせん、お昼休み休憩2回分くらい(正味1時間ちょっと・・)で作って、テストしただけのものです。

ソースもわかりやすさ優先の力技ですし、とても、あらゆるケースを想定してテストできているとは言えない代物です。

なので・・想定外やバグがあったらごめんなさいです。(笑)。

今回はこんなところで。

ではでは。