"BOKU"のITな日常

還暦越えの文系システムエンジニアの”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日毎に判断して整理していかないといけないので。

今回は、そういう整理を自動的に行って、日付・始業時刻・終業時刻の形にまとめるツールをEXCELを使ってやってみようと思います。

前は、自分用にPythonでやりました。

arakan-pgm-ai.hatenablog.com

それの「EXCEL VBAバージョン」って感じです。

 

仕様を考えてみる

 

とはいえ。

PythonVBA(Excel)では言語仕様が全く違うので、同じ手は使えません。

頭の体操もかねて、ゼロベースで考えてみます。

まず。

ログから得られる情報としては。

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

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

正常でない終了のIDは一旦、無視します

なお。

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

あとは、日付・時刻を見て「日付・始業時刻・終業時刻」に整理するわけですが、考慮しないといけない課題として。

  • 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プログラムはポイントだけ

 

今回はソース全文はブログに掲載しません。

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

github.com

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ツール。

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

最近は。

このツールで勤務実績を集計しないといけない状況ではないのですが、Pythonでやるより快適なのは間違いないです(笑)

とはいえ。

お昼休み休憩2回分くらい(正味1時間ちょっと・・)で作って、テストしただけです。

ソースもわかりやすさ優先の力技ですし、バグがあったらごめんなさいです。(笑)。

手作業でやるよりは楽できると思うので、自己責任でお願いします。

今回はこんなところで。

ではでは。