・ PowerSheII で業務自動化 社内の全 PC の Wi n d ows ログオン / ログオフ時刻を集計するはなし 1 . Act ⅳ eD ⅳ ectory ユーザーのログオン / ログオフ時刻を記録しよう 情シス担当者が社内の PC を管理する上で、ユーザーのログオン / ログオフ状況を把握すること は重要です。また、社員の勤務状況を把握する上でもログオン / ログオフ情報は有益と言えます ( タ イムカードの代替というより、あくまで裏付けデータではありますが ) 。 そこで今回は、 ・社内の全ユーザーの Windows ログオン / ログオフ時刻を記録 & 集計。 人事担当者などが使う社内報告用の ExceI 書式に時刻情報を埋め込む。 という作業を、全て Powershell による自動処理で行います。 ■ログオン / ログオフスクリプトをポリシー登録する Act ⅳ eDirectly の OU にスクリプトをポリシー登録することで、ユーザーのログオン / ログオフ時 刻をイベントログとして記録できます。スクリプトの登録は通常通りドメインコントローラの「グ ループポリシー管理工デイター」から行います。「ユーザーの構成」→「ポリシー」→「 Windows の設定」→「スクリプト ( ログオン / ログオフ ) 」の「ログオン」または「ログオフ」のプロバティ 画面の「 PowerSheII スクリプト」タブからスクリプトファイルの登録ができます。 ここで問題なのは、ログを各 PC 側に記録するか、サーバ側に記録するかということ。 PC 側にロ グを記録した場合、それらのログを集計する際、テータをサーバに集めなければなりません。一方、 サーバ側にログを記録する場合も、漏れなく口グを記録するためには PC からサーバへのネットワー ク接続が確立している必要があります。据え置きのデスクトップ PC ならともかく、ノート PC やタ プレット PC の場合、起動時に IOO % が社内 LAN に繋がっているわけではありません。 そこで今回は、 PC とサーパ両方にログを記録する方法をとります。両方に記録すれば、 IO()% と はいかないまでも、ほぼ全てのログデータを集められ ( ると信じ ) ます。 グル - プポリシー管埋工デイター カイ JI•(F) 擾作 ( A ) 表示 ( v ) 以 H ) スケ」介師 w を以的プト ー」ログオンログ 1 フスプト [ R 日 M 巧 . RBM. 1 ・キコンビューターの橋成 日 レ二ルー w 朝 ws 師 w ぎ計ログわス刎プト ( ログわ・ログオフス夘プト レ二基本設定 ユーザーの橋成 名前 ) 二ソフめ : アび定 ログオンイバントを記録 . 1 議二 W ⅳ過 0W5 の設定 当スプト ( ログわ / ログわ ) セキュリティの設定 0 朝方ルダーリダイレクト 画和ルーベースの 85 0 二第理用テンオ′ート : ローカル 1 ログオンのプロバティ 名 ログオン ログわ 対応 ) プロ哲の表示 ユーザーログオンス夘プトを言んでいま す . パラメーター レ二る本設定 この (X) では、次「飛てスケ」プトを実行します ( F ) 師 w 計スケガトにま少なくとも W 物 1 、、 57 または W 朝 1 面 ws 「飃「 方れ爆示 ( 斗・・ 匚区コ [ 河コ「一一亟 ) 加が必要です ■■ Windows Server 2012 R2 [ 図 4 川口グオンスクリプトの登録 15

■処理の流れ 今回の Powershell スクリプトで行う一連の処理の流れは以下の通りです。 ① LAN 内の各 PC のローカルフォルダからログの CSV ファイルを収集。サーバ側のイベントログ も CSV 出力します。 ② CSV を結合。各ユーザ 1 日毎の初回ログイン、最終ログオフ時刻を抽出。 ③ユーザーごとの CSV ファイルに分割。 ④ ExceI に変換 ⑤ Excel テンプレートから「勤怠管理表」を生成。④で生成した Excel ファイルの値を参照する 参照式を配置します。 -Section 4 ー社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし ログ集計の流れ テンプレート (xlsx) ログファイル (CSV) サー ⑤生成 ①ファイル収集 ②結合 一時ファイル ( CSV) ③分割 勤怠管理表 (xlsx) 値を参照 ログファイル ( CSV) ④変換 ログファイル ( csv) 参照用ファイル ヨ , ューザー別 (xlsx) [ 図 4 ー 6 ] ログ集計処理の流れ ■「勤怠管理表」テンプレート 今回のスクリプトでは社内資料として使うことを想定した「勤怠管理表」の Excel ファイルを生成 します。このファイルはほかの担当者が適宜、備考欄などを編集するものです。しかし、ログオン / ログオフ時刻欄だけは、ファイルを生成した日以降も自動更新されるようにしなければなりません。 そこで今回は、直接 Excel ファイルに値を格納するのではなく、値格納用の Excel ファイルを別途生 成し、 Excel の参照式により外部参照を行います。外部参照の値は、一旦、全てデータシートに格納し ておけば、あとは通常通りプック内で VLOOKUP 関数などを使うことができます。 18

Gijutsu-san-saku ミ きままに散策、いつでも模索。技術で遊び、未来を感しる情報誌。 専用メガネがないと見えない ! ? 嘛法のモニタ」作り方大解説 iPhone5 と非純正パーツで作る ! 法のスマホ」作り方大解説 【特集】 多重化映像技術、エンターティンメント技術研究の最前線 神奈川工科大学 白井研究室見学レポート PowerShell で業務助化 社内 ? ー噐蜑ン by \(aukiølefiin@ffilij/a

社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし -Section 4 ー 108 109 # 参照先 Exc 引ファイルを生成。 110 funct ion convertTOXl sx ($src, $dst) { # c sedXML の Exc 引ワークプックオブジェクトです。 $wb = new-object ClosedXML. Excel. XLWorkb00k $ws $wb Worksheets Add ("data") $ ⅱ nes = @(Get-Content $src -Encod i ng Defau は $array = @() $lines ー % { $array + = # 配列をシートにペーストします。 1 20 $ws. CeII ( 1. I)TnsertData( $array ) 1 22 # Exc 引ブックを保存します。 1 23 $wb. SaveAs ($dst) 1 24 125 } select -Skip 1 ) —repl ace [ 図 4 ー 18 ] 「ログオン・ログオフ集計 . p 引」参照用 Excel ファイル生成部分 [ 図 4 ー 1 印は加工した CSV を ExceI ファイルにする関数です。新規作成した ClosedXML のワークシー トに値を貼り付けます。 CSV ファイルを一旦、配列として読み込み、カンマで分割しながら 1 行ずつべー ストしています。 ( もっとシンプルな方法がありそうな気もします ) 126 127 128 # 社内資料用の Exc 引ファイルを生成。 129 # 値は convertToXlsx で生成した Excel を外部参照します。 130 # こちらももっと良い方法があるはず ( 131 funct i on epKi ntai F Ⅱ es ($name, $dst, $Di spIayName) 132 ヨ # 引 osedXML の Exc 引ワークプックオブジェクトです。 133 $wb = new-object 例 osedXML. Exce に XLWorkb00k ( $ 幻 Templ ate) : 134 135 # ActiveDirectory の表示名 ( 日本語の氏名 ) をシートで使えるよう、 136 # ワークシート " data " 上に値を格納します。 137 $ws $wb Worksheet ("data") 138 $ws CeIl ( 2 2 ) . VaIue = $D i spl ayName 139 140 # ワークシート " data2 " 上に参照式を設置します。 141 $ws2 $wb Worksheet ("data2") 142 i pcsv ()d i rPersonal Csv + $name + $data ー 143 144 145 146 147 148 149 150 152 153 154 156 158 159 1 60 } . csv") -Encod i ng Defau は + $name 十 $ commo n $data ー % { # 外部 E = 0 引 0 ーイルの値を参照する参照式です : $common 十 $ws2 CeII ($i, 1 ) FormulaAI $common 十 $ws2. CeIl ()i . 2 ) . FormuIaA1 $common 十 $ws2. Ce Ⅱ ( $ i , 3 ) . Formul aA1 . 幻 sx]data + $dirRef + 十 1 # ファイル名 ( 日本語氏名 +. 幻 sx ) で保存します。 $fil eName = $dst + $D i spl ayName + $wb. SaveAs ()f Ⅱ eName) . X ー SX [ 図 4-19 ] 「ログオン・ログオフ集計 . psl 」勤怠管理表 ExceI ファイル生成部分 ー図 4 ー 19 ] は社内資料「勤怠管理表」用の Excel ファイルを生成する関数です。こちらでも ClosedXML を使用します。テンプレートファイルを読み込み、ワークシート「 data 」上にユーザー名 ( 日 25

-Section 4 ー社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし epcsv $Fu llName -Encod i ng UTF8 4 # イベントログに出力する文字列。ログオフスクリプトでは " がログオフしました。 [System. Secur i ty. Pr i nc ipal. Wi ndowsldent i ty] : GetCurrent ( ) $user i nfO 1 # ログインユーザー情報を取得。 2 3 6 9 24 25 十 " がログオンしました。 -EventId 8 -EntryType lnformat ion -ComputerName server01 1 2 Wr i te-EventLog -LogName App licat ion -Source App ⅱ cat ion 11 # ログをサーバに記録 —EventId 8 -EntryType lnformat ion -Message $msg 8 Wr ite-EventLog -LogName App ⅱ cat ion -Source App ⅱ cat ion 7 # ログを PC 本体に記録 $user i nfO. Name 5 $msg "yyyyMMdd-HHmss" 1 6 $j i kan = Get-Date -Format 1 5 $hostname = $Env:COMPUTERNAME —Message $msg 1 7 $d i r ー 'C:%Iog—csvV 19 # CSV 出力用フォルダが無い場合は新規作成 20 i f (' (Test-Path $d i r) ) {md $d i r} 22 # CSV ファイルバス 23 $Fu llName [str i ng] ()d i r + $hostname + # 過去のイベントログを取得し、 CSV 出力 + $j i kan 十 . CSV") 26 Get-EventLog App ⅱ cat i on Event I D ¯eq 今回登録するスクリプトは [ 図 4 図の通りです。 [ 図 4-2 ] 「ログオンイベントを記録 . psl 」 ■イベントログを CSV 出力→加工→ Excel 書式に取り込む カルのフォルダにログを出力した CSV ファイルが溜まってゆきます。 登録完了し、ポリシーが適用されると、徐々にイベントログが記録されてゆきます。同時に、各 PC のロー ClosedXML は GitHub ページからダウンロードできます。 「 CIosed 」というネーミングですが、ちゃんとオープンソース ( MIT ライセンス ) のライプラリです。 でも使用できるという利点があります。ちなみに「 CIosedXML 」は後述の「 OpenXML 」の反対 ( ? ) の かし、 ClosedXML のほうが動作も安定し、高速です。また、 ExceI がインストールされていない環境 lnterop. Excel.Application) を使うという手段もあります ( 筆者は VB script でよく使ってました ) 。し PowersheII から ExceI ファイルを扱う方法としては、 ExceI の COM オプジェクト (Microsoft. Office. PowersheII で ExceI ファイルを扱うために、今回は CIosedXML というライプラリを使用します。 ■ Powershell で Excel ファイルを扱う 集計も自動化し、そのまま社内資料に使える ExceI ファイルを出力できるようにしましよう。 提出する必要があります。かといって、毎回手作業でテータ集計するのは面倒です。せつかくなので、 情報として生かすのであれば、データを集計し、それらしい書式に整えた上で人事部などの担当者へ ただイベントログを集めて眺めているだけでは、情報を活用できているとは言えません。勤怠管理 https: 〃github.com/ClosedXML/ClosedXML/reIeases マ CIosedXML ダウンロード 16

本語氏名 ) を格納。そして、ワークシート「 data2 」上には先程の参照用 Excel の値を参照する関数式 -Section4- 社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし を設置してゆきます。外部 ExceI ファイル内の値は、 ■定時実行で自動運用 イルに対する読み取りアクセス権が必要です ) 。 = ℃ :\hogehoge\[f00.xIsx]bar'!AI のようにフォルダバス、ファイル名、シート名、セルを指定することで参照できます ( 当然、そのファ にお声掛けください ( ^ ^ ) / ので、「こんな業務の自動化をやってみたよ ! 」「もっと良い書き方があるよ ! 」という方はぜひ気軽 個人的にも今後は一層 PowerShe Ⅱを使った業務自動化に地道に取り組んでいきたいと思っています えずは一つの例として頭の隅にでも置いていただければと思います。 イルのみを保存するよう改良が必要です。 Excel の処理の部分も改良する必要がありますので、とりあ ル数が徐々に増えて処理時間が長くなってしまいます。安定的に運用するためには、重複排除したファ 今回のスクリプトは実行する度にログファイルを新規に生成してそのまま溜めてしまうので、ファイ するようにしておけば、ログの集計と ExceI ファイルへの転記を全自動で行うことができます。尤も、 この「ログオン・ログオフ集計 . psl 」をタスクスケジューラに登録し、日中 1 時間に 1 回程度実行 て生じたいかなる損害に対しても、著者は責任を負いかねます。ご了承ください。 本書に掲載しております情報に基づいて行った機器の分解・改造、サンプルコードの実行等によっ ■こ注思 【スクリプト検証環境】 WindowsServer 2012 R2 Windows 10 Pro Creators Update 64bit PowerShell 5.1 奥付 発行日 : 2017 年 10 月 22 日 「 Gijutsu-san-saku 2017 Autamn 」 URL : http: 〃 clear-mint. net : clear-mint 印刷 : グラフィック 26 発行

社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし -Section 4 ー G26 A LastOff 1 Date FirstOn UserName 2 2017 / 10 / 11 a. tanaka 09 : 03 : 03 3 2017 / 10 / 10 a. tanaka 08 : 58 : 42 18 : 01 : 30 4 2017 / 10 / a. tanaka 08 : 57 : 58 18 : 11 : 45 5 2017 / 10 / 05 a. tanaka 08 : 52 : 22 18 : 27 : 30 6 2017 / 10 / a. tanaka 18 : OO : 55 08 : 55 : 08 7 2017 / 10 / 03 a. tanaka 18 : : 07 8 9 第 十 [ 図 4-14]CSV から加工・抽出した情報 抽出・加工後の状態が [ 図 4 ー 14 ] です。各ユーザーの 1 日ごとの初回ログオン時刻と最終ログオフ時 刻の一覧です。 さらにこの情報を業務用の「勤怠管理表」の書式に合わせたのが [ 図 4 ー 1 司の Excel ファイルです。 先述の通り、このファイルには値を保持せず、 [ 図 4 ー 1 引の Excel ファイル上の値を参照するようにし ています。 AH32 I J L 忖 0 P Q 只 S T LI V W X Y Z AB ACAD AE AF AGAHAI △ A B C D E F G H 1 勤怠管理表 「り CO ~ OO 2 0 1 7 年 1 0 月 田中 A 子 PO ロク時剣 開始時則 氏名 眈業時則 眈業時間 開始時刻終了時刻実爾時間残業時間 備考 日 日日日日日日日日 務 ワー一ワリ 4 ・ l-n CD 「ーー 終了時刻 1 8 : 圓 : 55 1 8 : 2 7 : 30 1 8 : 1 1 : 45 囲 : 5 5 : 08 囲 : 5 2 : 2 2 囲 : 5 7 : 5 8 data data 2 10 月 ト イ 塰備完了 [ 図 4 ー 15 ] 「勤怠管理表」 ExceI 23

2 . ログを収集・加工する ここからは、ログ情報を集計し Excel ファイルを作成する実際のスクリプトを解説します。あくま で一例ですので、実際には要件によって変わってくる部分も多いと思いますが、参考にしていただけ ればと思います。 ■ログ集計スクリプト ではさっそく見ていきましよう一図 4- IOIO まずは DLL の読み込み、作業ディレクトリや Excel テン プレートファイルのパスの宣言です。 1 # DLL の読み込み 2 -CRef lect ion. AssembI ⅵ : LoadFiI e ( 3 "C:%Program Fil es (x86)YOpen XML SDKW2. 5 Ⅵ i bVDocumentFormat. 0penXmI. dll") 4 •CRef lect ion. AssembI y] : LoadFiI e ( 5 "C:%Iog_testVCIosedXML. dll " ) 6 7 8 # ファイル出力用フォルダと一時フォルダのパスです。 9 $d i r "C:%log—testV" 10 $dirLog = $dir + 勹0g\ 11 $dirTemp = $dir + 'temp% 1 2 $templ= $d i rTemp + 'templ. csv 1 3 $temp2= $d i rTemp + 'temp2. csv 14 $dirPersonalCsv = $dirTemp + personalCsvV" 15 $dirRef = $dir + "referral%" 1 6 $d i rK i nta i = $d i r + " 幻 sxV 1 8 # ExceI テンプレートファイル 1 9 $xITempIate = $d i r + "templ ateYtemplate-2017. 幻 sx' 20 21 # 各 PC のログ保管フォルダ 22 $d i rLogRemote "c$%l og-csv%" 23 24 # 該当フォルダがない場合は作成します。 26 27 # 前回実行時に生成したファイルを削除します。 28 rm $d i rK i nta i * $d i rRef* $d i rTemp* -Recurse -Force 29 -Section4- 社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし 25 $dirLog, $dirTemp, $dirRef, $dirKintail ? い (Test-Path $ - ) } ー % { mkdir $ ー } [ 図 4-10 ] 「ログオン・ログオフ集計 . psl 」宣言部分 そして、つぎの [ 図 4 ー 11 ] がメイン処理部分です。 20

目次 きままに散策、いつでも模索。技術で遊ひ、未来を感しる情報誌。 2017 Aut amn Gijutsu-san-saku 目次 専用メガネがないと見えない ! ? 「魔法のモニタ」作り方大解説 iPhone5 と非純正バーツで作る ! 「魔法のスマホ」作り方大解説 【特集】 多重化映像技術、エンターティンメント技術研究の最前線 神奈川工科大学 白井研究室見学レポート PowerShelI で業務自動化 社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし ごあいさつ こんにちは。 Yuki lchinomiya( いちのみやゆき ) と申 ゆき@技術書典 3 【い 10 】 します。この本をお手に取っていただきありがとうござ 出来ました。これで周りの目を気にせずネッ います。 トサーフができるよー ! ! 今回は本年 8 月に Twitter へ動画を投稿した際にた くさんの反応を頂きました「魔法のモニタ」の作り方 をご紹介します。動画に使用したノート PC 版に加え、 iPhone 5 を使った「魔法のスマホ」にも挑戦してみま したので、ご興味ある方は参考にしていただければ と思います。 また、第 3 章では、液品を使った「多重化・不可 視化技術」の研究をされている神奈川工科大学の白 井博士の研究室について、見学レポートを掲載させ 16 : 14 - 2017 年 8 月 22 日 ていただきました。白井研究室では、多重化・不可 0 ・・・ま 0 ま 0 59 , 512 件のリツィート 71 , 843 件のし、いね 視化技術だけでなく、広くエンターティンメント技 0 218 59 , 512 (0 71 , 843 ⅲ 術の研究を行われており、中でも、「仮想育児」を 体験できる VR 作品『 Real Baby ー Real Family 』は大変ユニークで、必見です。 また、前回の本に引き続き、 PowerShe Ⅱを使った業務自動化のスクリプトもご紹介します。・・・・・・何 とも、ごった煮感のある内容構成ですが、同人誌ならではのフリースタイル ( ? ) として温かい目で読ん でいただければ幸いです。 2017 年 10 月いちのみやゆき 0 ! ノ 0 19 ( ) ) / 1

工 =IFERROR(VLOOKlJP(TEXT($A9,"yyW/mrn/dd't ),data2!$A:$C,2. FALSE). "") A B C り G H I J K L 忖 0 P Q R S T IJ V W X Y Z AAABACADAEAFAGAHAI A 、 社内の全 PC の Windows ログオン / ログオフ時刻を集計するはなし -Section 4 ー ー 0000000000000000000 ロロ 0 ロロロ 0 ロロロ 00 ロ 0 ロロー D9 諸 1 勤怠管理表 0 こうり血 - - ロ CD 0 ~ OO 9 2 ロ 1 7 年 1 0 月 氏名 號業時刻 眈業時間 開始時剣緒了時刻実励時間業時間 PC ロク時刻 日 0 こ CO ー L-n 1 ー 開始時 終了時 - Ⅱ一 11 0 こつり - 血 - - 「り data2 10 月 data 備完了 [ 図 4 ー刀「勤怠管理表」 Excel テンプレート (VLOOKUP 関数 ) L-n - Ⅱ - Ln 工 0 - Ⅱ - 、 1 9 し - Ⅱ - 00 06 00 0 リ 9 し CO 9 し 00 月 - Ⅱ - 4 ・ 9 し - Ⅱ - 《十つり 00 ′ 0 ム l—n ?-Q - Ⅱ - LSO LO L-n Ln ー 1 00 ^ ハ - 00 ^ ハ - 1 ー 1 よ - Ⅱ - - Ⅱ - - Ⅱ - 9 0 ム 9 し 9 し 0 ム , 1 0 ムつり -4 ・ Ln CD 0 ~ 00 召 照惨 参立。 外 LastOff 1 Date 下 irstOn lJserName 2 2017 / 10 / 11 a. tanaka : 03 : 03 3 2017 / 10 / 10 a. tanaka ー : 58 : 42 18 : 01 : 30 4 2017 / 10 / a. tanaka ℃ 8 : 57 : 58 18 : 11 : 45 5 2017 / 10 / 05 a. tanaka ー : 52 : 22 ユ 8 : 27 : 30 6 加 17 / 10 / a. tanaka : 55 : 08 18 : : 55 7 2017 / 10 / 03 a. tanaka 18 : 図 : 07 8 9 01 1 0 ④ [ 叱こ二コ国 data 十 4 [ 図 4-9 ] 参照用 Excel ブック [ 図 4 ー 8 ] 同一ブック上の別シート 19