日々の出退勤報告に素早くイベントログのログイン・ログアウト時刻を取得したい！
こんな要望のためにExcel VBAで簡単な「イベントログ取得ツール」をつくりました
今回は、何故そんなことになったのかを詳しく解説していきたいと思います
もちろん、あわせてExcel VBA「イベントログ取得ツール」の解説をしていきます
【この記事でわかること】
・WindowsPCのイベントログをすばやく取得する方法
・なぜイベントログをすばやく取得しなければならなかったのか
・VBAサンプルプログラムの利用、有効活用方法（改変方法）
記事で使用したサンプルファイルはこちらのリンク先からダウンロードできます
職場のWindowsPCはイベントビュアーが無い
毎日の出退勤報告って、なんで手書きなんだろう。
それに、自国確認するの 走召 めんどくさ～い！ 何とかならないかなぁ～
確かに！今のままじゃ面倒ですね～
何とかならないか、ちょっと考えてみますね・・・・
職場の現状を説明します
私の職場は、社員の出退勤管理を「イベントログ取得プログラム」でExcelに書き出して報告するようなっています。月初めに前月分を印刷して提出するんですよ。なんかすごくアナログ(^^;
さらには、日々の報告は「紙の管理簿」に手書きで出退勤時刻を記入して提出報告してるんです。日々上司が提出された帳簿を確認して「確認印」を押しています。究極のアナログ(-_-;)
しかも、その出退勤時刻データは「イベントログ取得プログラム」（下図参照）で印刷するか報告用のメールに添付されたデータを開いて見ないと確認できないんです。（職場のPCは厳重なセキュリティのため、イベントビュアーにアクセスできないようになっているんです。もちろんコマンドプロンプトへのアクセスなんて不可！！）
何とかしてイベントログを取得できないか検討してみました
それじゃあ「イベントログ取得プログラム」を改変しちゃおうかと思ったけど、こちらもパスワード保護がかかっていて手を出せないし、無理やりやるわけにもいかないなぁ！（バレたら処分されちゃうよなぁ）
しかたがない、Excel VBAで自作出来るか試してみよ！！！！
という感じです(^^;
Excel VBAでイベントログにアクセスできるのかどうかが最初の問題です
今までイベントログの取得はやったことなかったので、まずはGoogle先生に訊いてみました。そんなに多くはないけど情報は転がっていました
その中から、すぐ試せそうなコードを探して、アクセス可能かどうか試してみました
職場のPCからExcel VBAでイベントログにアクセスできる？
見つけたサイトはここです
‘WMI Fun !! > WMI Sample (VB) > イベントログの内容を取得する’
‘http://www.wmifun.net/sample/vb6/win32_ntlogevent.html’
※このサイトはすごく勉強になりました。ありがとうございますm(_ _)m
※使わせていただいたコードがこれです
Sample Code
Private Sub Command1_Click()
Dim EveSet As SWbemObjectSet
Dim Eve As SWbemObject
Dim Locator As SWbemLocator
Dim Service As SWbemServices
Dim MesStr As String
Set Locator = New WbemScripting.SWbemLocator
Set Service = Locator.ConnectServer
Set EveSet = Service.ExecQuery _
(“Select * From Win32_NTLogEvent Where Logfile=’System’ ” & _
“And TimeGenerated > ‘2003/06/25’“)
For Each Eve In EveSet
MesStr = MesStr & Eve.EventCode & “: ” & Eve.Message
Next
MsgBox “指定した期間内のイベントログの内容です。” & vbCrLf & _
vbCrLf & MesStr
Set EveSet = Nothing
Set Eve = Nothing
Set Locator = Nothing
Set Service = Nothing
End Sub
恐る恐るテスト実行！
やばい！「‘2003/06/25’」のまま実行したのでフリーズ！
気を取り直して「’2020/11/15’」に変更して再テストを実行しました
おっ！「指定期間内のイベントログの内容」が返ってきました！
結果は、無事アクセスできました(^^♪
イベントログを取り出せることが分かったので、この中から必要な情報を取り出せるようにコードに手を加えればよいということです
Excel VBAでPCの必要なイベントを取得する
コード改変に必要な情報を調べる
必要なのは「EventCode」の番号です
PCの起動時刻と終了時刻、ログイン時刻とログアウト時刻のイベントコードを調べます
って！イベントビュアー使えなんだよね(-_-;)
仕方ないからまたGoogle先生に助けてもらっちゃいました・・・
【調べた必要な情報】
6005＝電源ON（PC起動）、6006＝電源OFF（シャットダウン）
7001＝ログイン、7002＝ログアウト
時刻は 「TimeWritten」イベントがログファイルに書き込まれた時間
コードに手を加えていきます
【修正・変更点①】
サンプルコードでは、TimeGenerated（ソースがイベントを生成した時間を指定します。）が使われていましたが、調べたところイベントビュアーに表示される時刻に使われているのは「TimeWritten（イベントログに書き込んだ時間）」なのでこれに変更しました
【修正・変更点②】
イベントログを取得する段階で「取り出す日時」を指定してデータを取り出していましたが
速度がかなり遅い感じでした。日時指定せず取り出した方がかなり速かったです
取り出した後に「指定した期間」だけ処理するように変更しました。何故ならば、ログデータは日時順に直近のデータから並んでいるため、「指定した期間」まで先頭部分だけを処理すれば良いので、大幅な時間短縮に成功しました！
【日付の処理】
イベントログで取得する時刻はOSによって協定世界時か日本標準時かが異なりますWindows10の場合はUTC（協定世界時）です。次の例のとおりです
・UTC 協定世界時 UTC+0000 の2020年11月18日(水) 22:27 は
・JST 日本標準時 UTC+0900 の2020年11月19日(木) 07:27 と、時差は +9時間 です
これを考慮してコードを書く必要があります（プラスしたりマイナスしたりしています）
【その他】
・「取り出す日付」の指定はセルに入力したデータを使うようにしています
・Application.ScreenUpdating を使って「画面描画」を抑止して高速化しています
シートの準備
次のような簡単なシートを用意してください（よければリンクからサンプルをご利用ください）
Excel VBAでWindowsPCからイベントを取得コード
事前に参照設定が必要です
今回のコードは「Windows Management Instrumentation (WMI)」を利用しています
事前にVBA画面のツールメニュ⇒「参照設定」で「Microsoft WMI Scriptiong V1.2 Library」を選択しておく必要があります
PCの起動・終了／ログイン・ログアウト時刻を抜き出すコード
'【要参照設定】
'Microsoft WMI Scripting V1.2 Library
Sub Command1_Click()
Dim EveSet As SWbemObjectSet '抽出した全イベント
Dim Eve As SWbemObject '抽出イベント
Dim Locator As SWbemLocator 'クラスオブジェクト
Dim Service As SWbemServices 'WMIサービスオブジェクト
Dim MesStr As String 'イベントコード保存用
Set Locator = New WbemScripting.SWbemLocator
Set Service = Locator.ConnectServer
Dim strCmd As String '検索条件文字列用
Dim tgDate As String '検索年月日指定用
ActiveSheet.Columns(1).Clear 'セルデータ消去
tgDate = Range("H2").Value 'セルで抽出日付指定
'UTC日時に変換(JST=UTC+9hなので-9）
tgDate = DateAdd("h", -9, tgDate)
tgDate = Format(tgDate, "yyyymmddhhmmss")
'変数に引数部分の文字列を代入
'Eventcode：6005=PC起動,6006=PC終了.7001=LOGIN,7002=LOGOUT
strCmd = "Select * From Win32_NTLogEvent Where " & _
"Logfile = 'System' " & _
"And (Eventcode = '6005' Or Eventcode = '6006'" & _
"Or Eventcode = '7001' Or Eventcode = '7002')"
Set EveSet = Service.ExecQuery(strCmd) 'イベントログ取得！
Dim i As Long
Dim strDate As String
Dim utcDate As Date 'UTC日時
Dim jstDate As Date 'JST日時
Application.ScreenUpdating = False '画面描画を停止
For Each Eve In EveSet
i = i + 1 'インクリメント
MesStr = Eve.EventCode 'イベントコード取り出し
'時刻表示「yyyymmddhhmmss.000000-000」の後ろをカット
strDate = Left(Eve.TimeWritten, 14)
If strDate >= tgDate Then
'日付を整形
utcDate = CDate(Mid(strDate, 1, 4) & "/" & _
Mid(strDate, 5, 2) & "/" & _
Mid(strDate, 7, 2) & " " & _
Mid(strDate, 9, 2) & ":" & _
Mid(strDate, 11, 2) & ":" & _
Mid(strDate, 13, 2))
'TC日時をJSTに変換(JST=UTC+9h）UTC標準時間に9時間加算
jstDate = DateAdd("h", 9, utcDate)
'セルに書き込む
Cells(i, 1).Value = MesStr & " " & _
Format(jstDate, "yyyy/mm/dd hh:mm:ss")
Else: Exit For
End If
Next
Application.ScreenUpdating = True '画面描画再開
Set EveSet = Nothing
Set Eve = Nothing
Set Locator = Nothing
Set Service = Nothing
End Sub・「18～21行目」でセルに入力されている年月日を変数に代入し、UTC日時に変換しています（マイナス9時間）
・「25～28行目」でイベントを抽出する条件部分の設定を書いています。Systemログの、Eventcodeが ‘6005’（PC起動）、’6006’（PC終了）、’7001’（ログイン）、’7002’（ログアウト）のいずれかの場合にログを取り出す設定です
・「29行目」で条件にあったSystemイベントログを「EveSet」に書き出しています
・「30～33行目」は取り出したデータの処理に使う変数をここで宣言しています
・「35行目」で「EveSet」取得データ群から個別データを「Eve」に取り出しています
・「37行目」は「Eve」のイベントコードを取得
・「39行目」で「TimeWritten」を取り出していらない部分をカットしています
・「44行目」は指定年月日に該当するかどうか比較してします
・「42～47行目」で日付を整形
・「49行目」UTF日時に9時間プラスしてJST日時に変換しています
・「51行目」でイベントコードと年月日をセルに書き込んでいます
・「56行目」で画面描画再開
・「57～60行目」でセットしたオブジェクトを解放しています
こんな感じです！いつになく細かく解説してみました(^^ゞ
Worksheetイベントに呼び出しコードを入れると
Private Sub Workbook_Open()
Call Command1_Click
End Sub
・上の例では「Workbook_Open」イベントに呼び出しコードを記述しています
・こうしておけば、Excelを起動しただけでイベントログを勝手に取得してくれます
・年月日指定を変更したときにはコマンドボタンから実行すればOKです
・必要に応じて、違うイベントプロシージャーからの呼び出しに変更してください
まとめ（おわりに）
まとめと感想など
【Excel VBAでWindowsのイベントを取得する】から学べたこと
① PCの環境によってはセキュリーティー強化のためSystemに手動アクセスできない
② そんな時は、VBAでアクセスできるかもしれません（今回は成功しました）
③ Webで得られる情報は最大限活用しましょう（結果として時短になる）
④ 「WMI」についていろいろ勉強できました。（今後もっと活用してみたいです）
こんな感じです(^^)
【イベントログ】について
PCを動かしていれば必ずなんらかのイベントが発生しています
対応するログファイル名は次のとおりです
・「System」システムログ（今回取得したのはこれです）
・「Application」アプリケーションログ
・「Security」セキュリティログ
それぞれ、各種のイベントを記録していますので、今回のコードを改変してみたら取得・活用できるのではないでしょうか
是非試してみてください(^^)/
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
是非！サンプルファイルをダウンロード出来ますのでそのまま使ってみてください(^^)/
【今後の記事内容はどうしようかなぁ・・・】
・高速化した「VlookUp関数」などのVBAでの活用法を再検討してみたいと思います
・その他「小ネタいろいろ」などなど・・・・・
・今後これらのどれかについて記事にしていきたいと思います。ご期待ください(^^)/
