Powershellスクリプトで操作/作業ログを記録する②

Python

はじめに

前回作成したスクリプトを２週間ほど運用して、解消したいことが出てきました。

  • 解消したいこと
    1. レポートの作成時間を短縮させたい
      スクリプト終了時のレポート作成処理に約4～5分ほど時間がかかっていました。Powershellでの集計処理に時間を要していることが判りました。
    2. 作業履歴を残すCSVファイルの数と容量を削減したい
      1日の作業時間が8～10時間の場合、CSVファイルのサイズは約8～9MBでした。日毎にCSVファイルを作成しているので、スクリプトを動かすたびにCSVファイルが増えていき、管理の手間が増えてしまいます。
  • 解消するために採用した方法
    作業履歴をSQLiteのDBファイルに保存することで、解消することを目指しました。

    • 集計処理をSQLiteのクエリで行うことで、レポート作成時間は約4秒になりました。
    • 作業履歴をSQLiteのDBのファイル1つにまとめることができて管理の手間を削減することができました。また、DBファイルを用いることで容量は1～2割ほど削減できました。

スクリプトの概要

スクリプトでできることは前回と違いはありません。
前回との違いは下記となります。

各時間帯のアプリケーションの使用割合の開始時間と終了時間を変更しました。前回までは、ログ取得開始時間とログ取得終了時間としていましたが、今回から時間帯は0時から23時までの固定に変更しました。

前回(バージョン1.0.0)と今回(バージョン1.1.0)の比較

スクリプトの実行環境

スクリプトの動作を確認した環境は下記となります。

Windows10 22H2(OSビルド:19045.3086)
Powershellのバージョン 5.1.19041.3031
SQLiteのバージョン 3.42.0

関数の説明

スクリプト内で用意した関数について下記に記載しました。
前回(バージョン1.0.0)から廃止・更新した関数があります。また今回(バージョン1.1.0)から作業履歴をDBに記録する「record_log」関数を作成しました。

開始行 関数名 関数の概要
88 check_exec_env スクリプトの実行環境をチェックする関数です。ログの保存先フォルダとレポートの保存先フォルダが存在するかどうかを確認します。
107 check_script_execution スクリプトの実行状況を確認する関数です。実行フラグファイルが存在するかどうかを確認し、存在する場合は作業履歴取得スクリプトが既に実行中であることを通知します。
128 get_info_active_window アクティブなウィンドウの情報を取得する関数です。現在アクティブなウィンドウのプロセス名とウィンドウ名を取得し、配列で返します。
159 termination_process スクリプト終了時の処理を行う関数です。終了ログを記録し、乱数フラグファイルを削除します。また、作業履歴からレポートを作成するための関数を呼び出します。
189 terminate_script スクリプトを終了する関数です。実行フラグファイルを削除し、ログに終了メッセージを書き込んでスクリプトを終了します。
205 create_pie_bar_chart_tables アプリケーションの使用割合の円グラフ、使用時間の棒グラフ、ウィンドウごとの表示時間の表を作成する関数です。当日の各プロセスとウィンドウの数を取得し、レポートのテンプレートに反映させます。
273 create_stacked_chart 時間あたりのアプリケーション使用率の100%積み上げグラフを作成する関数です。
346 create_usage_time_per_app アプリごとの使用時間の表を作成する関数です。
377 create_info_environment 作業履歴取得の環境情報の表を作成する関数です。
396 logging ログメッセージをテキストログファイルに出力する関数です。
405 record_log 操作ログを指定した秒間隔で取得し、DBに記録する関数です。

作成したSQLiteのスキーマとクエリ

今までSQLiteを使用したことはなく、求めているデータを抽出するクエリの作成は試行錯誤の連続でした。備忘録も兼ねて、作成したスキーマとクエリの抽出結果のサンプルをまとめました。

DBにテーブルを作成するクエリ

クエリの記載箇所：65行目

$query = @"
CREATE TABLE IF NOT EXISTS ExecutionLogs (
    LogDate TEXT,
    LogTime TEXT,
    ProcessName TEXT,
    WindowTitle TEXT
);
"@

実行結果(.schemaでDBのスキーマ情報を表示)

>
> sqlite3.exe .\log\history.db
sqlite>
sqlite> .schema
CREATE TABLE ExecutionLogs (
    LogDate TEXT,
    LogTime TEXT,
    ProcessName TEXT,
    WindowTitle TEXT
);
sqlite>

当日の各プロセスの数を抽出するクエリ

クエリの記載箇所：216行

$query_processName = @"
select ProcessName, count( ProcessName ) 
from ExecutionLogs 
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end' 
group by ProcessName 
order by count( ProcessName ) asc;
"@

実行結果（プロセス名|表示秒数）

mspaint|1
SearchApp|8
7zG|23
ApplicationFrameHost|63
powershell|66
notepad++|180
Code|205
explorer|285
POWERPNT|909
ONENOTE|1010
msedge|8881

当日のWindowTitleの数を抽出するクエリ

クエリの記載箇所：241行

$query_WindowTitle = @"
select ProcessName, WindowTitle, count( WindowTitle )
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end'
group by WindowTitle
order by count( WindowTitle ) desc;
"@

実行結果（プロセス名|ウィンドウ名|表示秒数）
補足：実行結果のウィンドウ名はマスクをしています。

msedge|AAAAAAAAAAAAAAAAAAA|1241
ONENOTE|BBBBBBBBBBBBBBBBBBBB|848
msedge|CCCCCCCCCCCCCCCCCCC|845
POWERPNT|DDDDDDDDDDDDDDDDDDDD|839
msedge|EEEEEEEEEEEEEEEEEEE|448
msedge|FFFFFFFFFFFFFFFFFFF|398
msedge|GGGGGGGGGGGGGGGGGGG|391
powershelHHHHHHHHHHHHHHHHHHHH|335
msedge|IIIIIIIIIIIIIIIIIII|334
msedge|JJJJJJJJJJJJJJJJJJJ|299
msedge|KKKKKKKKKKKKKKKKKKK|275

0～23時の各時間帯における各プロセスの表示時間を抽出するクエリ

クエリの記載箇所：280行

$hourlyColumns = 0..23 | ForEach-Object {
	$hour = "{0:D2}" -f $_
	if ($_ -eq 23) {
		"	SUM(CASE WHEN substr(LogTime, 1, 2) = '$hour' THEN 1 ELSE 0 END) AS '$hour 時台'"
	} else {
		"	SUM(CASE WHEN substr(LogTime, 1, 2) = '$hour' THEN 1 ELSE 0 END) AS '$hour 時台',`r`n"
	}
}

$columns = $($hourlyColumns -join "")

# クエリの設定
$query_hourlyColumns = @"
select
	ProcessName,
$columns
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end'
group by ProcessName;
"@

補足：下記の箇所で、各時間帯の各プロセスの表示時間を抽出するクエリを作成しています。

hourlyColumns = 0..23 | ForEach-Object {
	$hour = "{0:D2}" -f $_
	if ($_ -eq 23) {
		"	SUM(CASE WHEN substr(LogTime, 1, 2) = '$hour' THEN 1 ELSE 0 END) AS '$hour 時台'"
	} else {
		"	SUM(CASE WHEN substr(LogTime, 1, 2) = '$hour' THEN 1 ELSE 0 END) AS '$hour 時台',`r`n"
	}
}

上記の実行結果
$query_hourlycolumnsの$columnsの内容は下記となります。

SUM(CASE WHEN substr(LogTime, 1, 2) = '00' THEN 1 ELSE 0 END) AS '00 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '01' THEN 1 ELSE 0 END) AS '01 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '02' THEN 1 ELSE 0 END) AS '02 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '03' THEN 1 ELSE 0 END) AS '03 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '04' THEN 1 ELSE 0 END) AS '04 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '05' THEN 1 ELSE 0 END) AS '05 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '06' THEN 1 ELSE 0 END) AS '06 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '07' THEN 1 ELSE 0 END) AS '07 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '08' THEN 1 ELSE 0 END) AS '08 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '09' THEN 1 ELSE 0 END) AS '09 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '10' THEN 1 ELSE 0 END) AS '10 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '11' THEN 1 ELSE 0 END) AS '11 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '12' THEN 1 ELSE 0 END) AS '12 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '13' THEN 1 ELSE 0 END) AS '13 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '14' THEN 1 ELSE 0 END) AS '14 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '15' THEN 1 ELSE 0 END) AS '15 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '16' THEN 1 ELSE 0 END) AS '16 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '17' THEN 1 ELSE 0 END) AS '17 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '18' THEN 1 ELSE 0 END) AS '18 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '19' THEN 1 ELSE 0 END) AS '19 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '20' THEN 1 ELSE 0 END) AS '20 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '21' THEN 1 ELSE 0 END) AS '21 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '22' THEN 1 ELSE 0 END) AS '22 時台',
SUM(CASE WHEN substr(LogTime, 1, 2) = '23' THEN 1 ELSE 0 END) AS '23 時台'

実行結果（プロセス名|0～23時の各時間帯の表示秒数）

7zG|0|0|0|0|0|0|0|0|0|0|0|23|0|0|0|0|0|0|0|0|0|0|0|0
Code|0|0|0|0|0|0|0|0|0|0|190|1|0|3|10|1|0|0|0|0|0|0|0|0
ONENOTE|0|0|0|0|0|0|0|0|0|0|0|957|0|49|4|0|0|0|0|0|0|0|0|0
POWERPNT|0|0|0|0|0|0|0|0|0|0|291|541|0|1|76|0|0|0|0|0|0|0|0|0
SearchApp|0|0|0|0|0|0|0|0|0|0|3|2|0|3|0|0|0|0|0|0|0|0|0|0
explorer|0|0|0|0|0|0|0|0|0|0|20|62|0|108|70|25|0|0|0|0|0|0|0|0
msedge|0|0|0|0|0|0|0|0|0|0|63|1885|3524|3347|62|0|0|0|0|0|0|0|0|0
mspaint|0|0|0|0|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|0|0|0|0|0
notepad++|0|0|0|0|0|0|0|0|0|0|177|1|0|2|0|0|0|0|0|0|0|0|0|0
powershell|0|0|0|0|0|0|0|0|0|0|4|0|0|0|30|32|0|0|0|0|0|0|0|0

当日のユニークなプロセス数を抽出するクエリ

クエリの記載箇所：320行

$query_num_apps = @"
select count( distinct ProcessName )
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end';
"@

実行結果（数値）

10

当日の各プロセスの数を抽出して60で割ることで分単位での表示時間を出力するクエリ

クエリの記載箇所：350行

$query_date_time = @"
select ProcessName, round( cast(count( ProcessName ) as real)/60, 1)
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end'
group by ProcessName
order by count( ProcessName ) desc;
"@

実行結果（プロセス名|表示分数）

msedge|148.0
ONENOTE|16.8
POWERPNT|15.2
explorer|4.8
Code|3.4
notepad++|3.0
powershell|1.1
7zG|0.4
SearchApp|0.1
mspaint|0.0

補足：表示分数の少数第2位以下は切り捨て

作業履歴を記録するクエリ

クエリの記載箇所：408行

$query = @"
INSERT INTO ExecutionLogs (LogDate, LogTime, ProcessName, WindowTitle)
VALUES ('$logDate', '$logTime', '$processName', '$windowTitle');
"@

実行結果（日付|時刻|プロセス名|ウィンドウ名）
補足：実行結果の一部で、msedgeとexplorerのウィンドウ名は一部マスクをしています。

2023-06-27|21:47:56|msedge|AAAAAAAAAAAAAAAAAAA
2023-06-27|21:47:57|msedge|AAAAAAAAAAAAAAAAAAA
2023-06-27|21:47:58|explorer|file:///C:/Users/XXXXX/Google%20ドライブ/get_operation_log
2023-06-27|21:47:59|explorer|file:///C:/Users/XXXXX/Google%20ドライブ/get_operation_log

作成したスクリプトとレポート用テンプレートHTML

作成したスクリプトは下記となります。

<# 操作ログ(クティブになっているウィンドウのプロセス名とアクティブ名)を指定した秒間隔で取得し、 ログに出力する スクリプトの終了時に取得した操作ログでhtml形式のレポートを作成する 実行方法: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -WindowStyle Hidden -File get_operation_log.ps1 ログのフォーマット: yyyy/mm/dd, HH:MM:SS, プロセス名, ウィンドウ名 ログの出力先: $log_folderで設定 ログのサイズ: 1時間で約800KB 8時間で約7MB レポート作成先: $report_folder で設定 Version: 1.1.0 Last Updated: 2023/6/25 #>

#---------#
# 変数定義 #
#---------#

# logフォルダのパス
$log_folder = "./log/"

#スクリプトの実行履歴を記録するファイル
$exec_logfile = $log_folder + "exec_log.txt"

#実行フラグのファイル
$exec_flag_file = $log_folder + "exec_flag.txt"

# カラーリスト
$color_list = "#D3D3D3","#7B68EF", "#87CFEB","#40E0D0","#3CB371","#ADFF2F","#BA55D3","#CD5C5C","#FF82EE","#FFA07A","#FFA500","#FFD700","#FFF8DC","#FFEBCD","#00008B", "#191970","#483D8B", "#4B0082","#0000CD #7868EE", "#4169E1","#6495ED"

# 作業履歴情報のファイル名から日付を抽出する
$report_date = Get-Date -UFormat "%Y%m%d"

# レポートを保存するフォルダのパス
$report_folder = "./report/"

# レポートを作成するためのテンプレートと作成後のファイルを指定する
$template_report = $report_folder + "template_report.html"
$report = $report_folder + $report_date + "_report.html"

# 作業履歴を取得する間隔、単位はミリ秒
$interval = 980

# scriptを停止する時刻
$time_stop_script = "19:00"

## 開始と終了の対を判定する数値
$flag_random = Get-Random

#実行フラグのファイル
$exec_flag_random = $log_folder + "exec_random.txt"

#-----------#
# DBの初期化 #
#-----------#

# SQLite.dllの読み込み 
Add-Type -Path "./sqlite-netFx40-static-binary-x64-2010-1.0.118.0/System.Data.SQLite.dll"

# SQLiteデータベースに接続する
$databasePath = "./log/history.db"
$connectionString = "Data Source=$databasePath;Version=3;"
$connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
$connection.Open()

# テーブルを作成するクエリを用意する
$query = @"
CREATE TABLE IF NOT EXISTS ExecutionLogs (
    LogDate TEXT,
    LogTime TEXT,
    ProcessName TEXT,
    WindowTitle TEXT
);
"@

# クエリの実行
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.ExecuteNonQuery()


#---------#
# 関数定義 #
#---------#

# スクリプトの実行環境のチェック
# 引数:無し
# 戻値:無し
function check_exec_env(){
	# ログを保存するフォルダの確認
	if(Test-Path $log_folder){
		logging("ログを保存するフォルダは存在しています。")
	}else{
		logging("ログを保存するフォルダが存在していません。ログフォルダを作成します。")
		New-Item $log_folder -ItemType Directory
	}
	# レポートを保存するフォルダの確認
	if(Test-Path $report_folder){
		logging("レポートを保存するフォルダは存在しています。")
	}else{
		logging("レポートを保存するフォルダが存在していません。reportフォルダを作成してtemplate_report.htmlを配置してください。")
	}
}

# スクリプト実行状況を確認する関数
# 引数:無し
# 戻値:無し
function check_script_execution(){
	# アセンブリを読み込む
	Add-Type -AssemblyName System.Windows.Forms
	#実行フラグの有無を確認する
	if(Test-Path $exec_flag_file){
		logging("実行フラグが存在しています。作業履歴を取得中です。")
		$result = [System.Windows.Forms.MessageBox]::Show("作業履歴取得スクリプトは稼働しています。`r`n`r`n作業履歴取得スクリプトを終了しますか?","作業履歴取得スクリプトの動作確認", "YesNo", "Warning")
		if( $result -eq "Yes" ){
			#スクリプトの終了処理を実行する
			termination_process
		}
	}else{
		logging("作業履歴の取得を開始します。")
		Write-Output $pid | Out-File $exec_flag_file -NoNewline
		Write-Output $flag_random | Out-File $exec_flag_random -NoNewline
	}
}

# アクティブウィンドウの情報を取得する
# 引数:無し
# 戻値:アクティブウィンドウの情報(配列(0:取得日付,1:取得時刻,2:プロセス名,3:ウィンドウタイトル名)
function get_info_active_window(){
	# アクティブのウィンドウハンドルを取得する 
	$hwnd = [Win32.Utils]::GetForegroundWindow()

	# 取得したウィンドウハンドルのプロセスIDを取得する
	$null = [Win32.Utils]::GetWindowThreadProcessId($hwnd, [ref] $myPid)

	# 取得したプロセスID の Name、MainWindowTitle を取得する
	$var = Get-Process | Where-Object ID -eq $myPid | Select-Object Name, MainWindowTitle

	# comオブジェクトを作成する
	$app = New-Object -com shell.application

	# アクティブウィンドウがexplorerだった場合、LocationURLをMainWindowTitle に入れる 
	if($var[0].Name -eq "explorer"){
		$var[0].MainWindowTitle = $app. Windows() | Where-Object {$_.fullname -match "explorer.exe"} | ForEach-Object {$_.LocationURL}
	}

	# アクティブウィンドウの情報を生成する 
	$active_date = Get-Date -UFormat "%Y-%m-%d"
	$active_time = Get-Date -UFormat "%H:%M:%S"

	# 情報を配列に格納する
	$result = @($active_date, $active_time, $var[0].Name, $var[0].MainWindowTitle)

	return $result
}

#スクリプト終了時の処理を行う関数
# 引数:無し
# 戻値:無し
function termination_process(){
	# 終了ログを記録する
	$read_random = Get-Content $exec_flag_random -Raw
	$logDate = Get-Date -UFormat "%Y-%m-%d"
	$logTime = Get-Date -UFormat "%H:%M:%S"
	record_log $logDate $logTime "end" $read_random
	
	# 乱数フラグを削除する
	Remove-Item $exec_flag_random

	## 作業履歴からレポートを作成する
	# 作業履歴取得の環境情報の表を作成する
	create_info_environment

	# アプリケーションの使用割合(円グラフ)と使用時間(棒グラフ)、ウィンドウごとの表示時間(表)を作成する
	create_pie_bar_chart_tables

	# 時間あたりのアプリケーション使用率 (100%積み上げグラフ)を作成する 
	create_stacked_chart

	# アプリごとの使用時間の表を作成する
	create_usage_time_per_app

	# スクリプトを終了する
	terminate_script
}

#スクリプトを終了する関数
# 引数:無し
# 戻値:無し
function terminate_script(){
	#終了させるプロセスIDを取得する
	$read_pid = Get-Content $exec_flag_file -Raw

	# 実行フラグを削除する
	Remove-Item $exec_flag_file

	#ログに終了メッセージを書き込んでスクリプトを終了する
	logging("スクリプトを終了します。 ")
	Stop-Process -Id $read_pid	# 稼働しているスクリプトを終了する
	Stop-Process -Id $pid				# 自身のPIDに対しても終了処理を実行する
}

#アプリケーションの使用割合 (円グラフ)と使用時間(棒グラフ)、ウィンドウごとの表示時間(表)を作成する関数
# 引数:無し
# 戻値:無し
function create_pie_bar_chart_tables(){
	logging ("アプリケーションの使用割合(円グラフ)と使用時間(棒グラフ)、 ウィンドウごとの表示時間(表)の作成を開始します。")

	#円グラフのカラーリスト
	$colors=""

	#Nameの統計情報を保存する変数
	$statistics_N_Name = ""
	$statistics_N_Count = ""
	
	# 当日の各プロセスの数を抽出するクエリ
	$query_processName = @"
select ProcessName, count( ProcessName ) 
from ExecutionLogs 
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end' 
group by ProcessName 
order by count( ProcessName ) asc;
"@

	# クエリの実行
	$command = $connection.CreateCommand()
	$command.CommandText = $query_processName
	$result = $command.ExecuteReader()
	
	$i = 0
	# レポートのテンプレートに反映する際に使用する、当日の各プロセス数の変数を作成する
	while ($result.Read()) {
		$processName = $result.GetString(0)
		$count = $result.GetInt32(1)
		$statistics_N_Name = '"' + $processName + '"' + "," + $statistics_N_Name 
		$statistics_N_Count = [string]$count + "," + $statistics_N_Count
		$colors = '"' + $color_list[$i] + '"' + "," + $colors
		$i += 1
	}

	# 当日のWindowTitleの数を抽出するクエリ
	$query_WindowTitle = @"
select ProcessName, WindowTitle, count( WindowTitle )
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end'
group by WindowTitle
order by count( WindowTitle ) desc;
"@

	# クエリの実行
	$command = $connection.CreateCommand()
	$command.CommandText = $query_WindowTitle
	$result = $command.ExecuteReader()

	# レポートのテンプレートに反映する際に使用する、当日のWindowTitle数のhtmlタグを作成する
	while ($result.Read()) {
		$processName = $result.GetString(0)
		$WindowTitle = $result.GetString(1)
		$count = $result.GetInt32(2)
		$tables_active += " <tr><td>" + $processName + "</td>" + "<td>" + $WindowTitle + "</td>" + "<td>" + $count + "</td>" + "</tr>" + "`r`n"
	}

	# レポートのテンプレートに値を反映する。 反映するのは日付、 ラベル、 データ、カラー、表
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "yyyymmdd",$report_date } | Set-Content -Encoding UTF8 $report
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "Name",$statistics_N_Name } | Set-Content -Encoding UTF8 $report
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "Count",$statistics_N_Count } | Set-Content -Encoding UTF8 $report
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "Colors",$colors } | Set-Content -Encoding UTF8 $report 
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "tables_active",$tables_active } | Set-Content -Encoding UTF8 $report
}

# 時間あたりのアプリケーション使用率(100% 積み上げグラフ)を作成する関数
# 引数: 無し
# 戻値: 無し
function create_stacked_chart(){ 
	logging("時間あたりのアプリケーション使用率 (100%積み上げグラフ)の作成を開始します。")

	# レポートのテンプレートに反映する際に使用する、ラベルの情報を設定する
	$labels = '        labels: ["00 時","01 時","02 時","03 時","04 時","05 時","06 時","07 時","08 時","09 時","10 時","11 時","12 時","13 時","14 時","15 時","16 時","17 時","18 時","19 時","20 時","21 時","22 時","23 時"],'

	# 0～23時の各時間帯における各プロセスの表示時間を抽出するクエリ
	$hourlyColumns = 0..23 | ForEach-Object {
		$hour = "{0:D2}" -f $_
		if ($_ -eq 23) {
			"	SUM(CASE WHEN substr(LogTime, 1, 2) = '$hour' THEN 1 ELSE 0 END) AS '$hour 時台'"
		} else {
			"	SUM(CASE WHEN substr(LogTime, 1, 2) = '$hour' THEN 1 ELSE 0 END) AS '$hour 時台',`r`n"
		}
	}

	$columns = $($hourlyColumns -join "")

	# クエリの設定
	$query_hourlyColumns = @"
select
	ProcessName,
$columns
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end'
group by ProcessName;
"@

	# クエリの実行
	$command = $connection.CreateCommand()
	$command.CommandText = $query_hourlyColumns
	$result = $command.ExecuteReader()

	# レポートのテンプレートに反映する際に使用する、プロセス名とプロセス数のhtmlタグを作成する
	$process = ""
	$datasets = "        datasets: [`r`n"

	$i = 0
	while ($result.Read()) {
		$hourlyCounts = 0..23 | ForEach-Object { $hour = "{0:D2}" -f $_ ; $result[$("{0} 時台" -f $hour)] }
		$process += "        process[" + [string]$i + "] = ["+ $($hourlyCounts -join ',') + "];`r`n"
		$datasets += "        {`r`n" + "          label:""" + $result["ProcessName"] + """,`r`n" + "          data: ratio[" + [string]$i + "],`r`n" + "          backgroundColor:[""" + $color_list[$i] + """],`r`n" + "        },`r`n"
		$i += 1
	}
	$datasets += "        ],"

	# 当日のユニークなプロセス数を抽出するクエリ
	$query_num_apps = @"
select count( distinct ProcessName )
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end';
"@

	# クエリの実行
	$command = $connection.CreateCommand()
	$command.CommandText = $query_num_apps
	$result = $command.ExecuteReader()

	# データの取得
	while ($result.Read()) {
		$num_apps = $result.GetInt32(0)
	}

	#テンプレートに値を反映する。 反映するのは各アプリの時間あたりの使用数、時間、
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "_PROCESS_",$process } | Set-Content -Encoding UTF8 $report 
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "_NUM_APPS_",$num_apps } | Set-Content -Encoding UTF8 $report 
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "_LABELS_",$labels } | Set-Content -Encoding UTF8 $report 
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "_DATASETS_",$datasets } | Set-Content -Encoding UTF8 $report
}

# アプリごとの利用時間の表を作成する関数
# 引数:無し
# 戻値:無し
function create_usage_time_per_app(){
	logging ("アプリケーションごとの利用時間表の作成を開始します。")

	# 当日の各プロセスの数を抽出して60で割ることで分単位での表示時間を出力するクエリ
	$query_date_time = @"
select ProcessName, round( cast(count( ProcessName ) as real)/60, 1)
from ExecutionLogs
where LogDate = Date('now', 'localtime') and not ProcessName = 'start' and not ProcessName = 'end'
group by ProcessName
order by count( ProcessName ) desc;
"@

	# クエリの実行
	$command = $connection.CreateCommand()
	$command.CommandText = $query_date_time
	$result = $command.ExecuteReader()

	# データの取得
	while ($result.Read()) {
		$processName = $result.GetString(0)
		$count = $result.GetFloat(1)
		$tables_apps += "      <tr><td>" + $processName + "</td>" + "<td>" + [string]$count + "</td>"+"</tr>" + "`r`n"
	}
	
	#テンプレートに値を反映する。 反映するのは各アプリの使用時間
	(Get-Content -Encoding UTF8 $report) | ForEach-Object { $_ -replace "tables_apps", $tables_apps } | Set-Content -Encoding UTF8 $report
}

#作業履歴取得の環境情報の表を作成する関数
# 引数:無し
# 戻値:無し
function create_info_environment(){
	logging("作業履歴取得の環境情報の表の作成を開始します。")
	# ユーザー名、ドメイン名、コンピューター名、OS名、 OSバージョンを取得する
	$username = $env:username
	$domain = $env:USERDOMAIN
	$computername = $env:COMPUTERNAME
	$osname = (Get-ComputerInfo).osname
	$osversion = (Get-ComputerInfo).osversion
			
	#テンプレートに値を反映する。 反映するのは作業履歴の取得環境の情報
	$tables_info = "      <tr><td>" + $username + "</td><td>" + $domain + "</td><td>" + $computername + "</td><td>" + $osname + "</td><td>" + $osversion + "</td></tr>"
			
	#テンプレートに値を反映する。 反映するのは各アプリの使用時間
	(Get-Content -Encoding UTF8 $template_report) | ForEach-Object {$_ -replace "tables_info",$tables_info } | Set-Content -Encoding UTF8 $report
}

# メッセージをログに記録する関数
# 引数:メッセージ(str)
# 戻値:無し
function logging($message){
	$active_time = Get-Date -UFormat "%Y-%m-%d %H:%M:%S"
	$log_message = $active_time + " " + $message
	Write-Output $log_message | Out-File $exec_logfile -Append
}

# ログを記録する関数
# 引数:日付(str),時刻(str),プロセス名(str),メッセージ(str)
# 戻値:無し
function record_log([string]$logDate, [string]$logTime,[string]$processName, [string]$message){
	# 作業履歴を記録する
	$windowTitle = $message
	$query = @"
	INSERT INTO ExecutionLogs (LogDate, LogTime, ProcessName, WindowTitle)
	VALUES ('$logDate', '$logTime', '$processName', '$windowTitle');
"@

	$command.CommandText = $query
	$command.ExecuteNonQuery()
}


#----------#
# main処理 #
#----------#

# スクリプトの実行環境を確認する
check_exec_env

# スクリプトの実行状況を確認する 
check_script_execution

# 開始ログを記録する
$logDate = Get-Date -UFormat "%Y-%m-%d"
$logTime = Get-Date -UFormat "%H:%M:%S"
record_log $logDate $logTime "start" $flag_random

# アクティブウィンドウ、プロセス名を取得するためにuser32.dllを読み込むための構文
$code = @'
	[DllImport("user32.dll")]
	public static extern IntPtr GetForegroundWindow();
	[DllImport("user32.dll")]
	public static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out int ProcessId);
'@

# Win32の読み込みと変数 ($myPid)の初期化
Add-Type $code -Name Utils -Namespace Win32
$myPid = [IntPtr]::Zero;

# 無限ループ 
while($true){
	$result = get_info_active_window

	if ( Test-Path $exec_flag_file ){
		# アクティブウィンドウの情報を記録する
		$logDate = $result[0]
		$logTime = $result[1]
		$processName = $result[2]
		$message = $result[3]
		record_log $logDate $logTime $processName $message
	}

	# 指定時刻になったらスクリプトを終了する 
	$now = Get-Date -UFormat "%H:%M"
	if( $now -eq $time_stop_script ){
		termination_process
	}

	#980ミリ秒 (0.98秒) 待つ 
	Start-Sleep -Milliseconds $interval
}

# DBとの接続を終える
$connection.Close()

作成したレポート用テンプレートHTMLは下記となります。

<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />
    <title>yyyymmdd_作業履歴</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@3.7.1"></script>
    <script src="https://unpkg.com/chart.js-plugin-labels-dv/dist/chartjs-plugin-labels.min.js"></script>
    <script>
      window.onload = function () {
      let context_1 = document.querySelector("#pie_chart").getContext('2d')
      new Chart(context_1, {
      type: 'pie',
      data: {
        labels: [Name], 
		datasets: [{
			backgroundColor: [Colors],
			data: [Count]
        }]
      }, // data
      options: {
        responsive: false,
        plugins: {
          labels: {
            render: 'percentage',
            fontColor: 'gray',
            fontSize: 15
          }, // labels
        }, // plugins
      } // options
      });
      let context_2 = document.querySelector("#bar_chart").getContext('2d')
      new Chart(context_2, {
        type: 'bar',
        data: {
          labels: [Name],
          datasets: [{
            backgroundColor: [Colors],
            data: [Count]
          }] //datasets
        }, // data
        options: {
          plugins: {
            legend: {
              display: false,
            }, // legend
          }, //plugins
          responsive: false,
          scales: {
            y: {
              title: {
                display: false,
                text: '単位[秒数]'
              }, // title
              ticks: {
                callback: function(value){
                  return value.toString().replace(/\B(?=(\d{3})+(?!\d))/g,',')+'秒'; // カンマと秒を付与
                },
              }, // ticks
            }, // y
          } // scales
        } // options
      });
      let process = [];
_PROCESS_
      let total = [];
      for(let i=0; i<=23; i++){   // _TIMES_ → 23
        total[i] = 0;
        for( let j=0; j<_NUM_APPS_; j++){
        total[i] += process[j][i];
        }
      }

      let ratio =[];
      for(let i=0; i<_NUM_APPS_; i++){
        ratio[i] = [];
        for(let j=0; j<=23; j++){   // _TIMES_ -> 23
          ratio[i][j] = process[i][j] / total[j];
        }
      }

      let context_3 = document.querySelector("#stacked_chart").getContext('2d')
      new Chart(context_3, {
        type: 'bar',
        data: {
_LABELS_
_DATASETS_
        },
        options: {
          scales: {
            x: {
              stacked: true,
            }, // x
            y: {
              stacked: true,
              max: 1.0
            } // y
          }, // scales
          responsive: false
        } // options
      })
    }
  </script>
  </head>
  <body>
    <h2>yyyymmdd_作業履歴</h2>
    <hr>
    <h3>作業履歴の取得環境</h3>
    <table border="1">
    <tr><th>ユーザー名</th><th>ドメイン名</th><th>コンピューター名</th><th>OS名</th><th>OSバージョン</th></tr>
tables_info 
    </table>
    <hr>
    <h3>アプリケーションの使用時間</h3>
    <table border="1">
    <tr><th>アプリケーション</th><th>表示時間(分)</th></tr>
tables_apps
    </table>
    <hr>
    <canvas id="bar_chart" width="500" height="500"></canvas>
    <hr>
    <h3>アプリケーションの使用割合</h3>
    <canvas id="pie_chart" width="500" height="500"></canvas>
    <hr>
    <h3>時間あたりのアプリケーション使用率</h3>
    <canvas id="stacked_chart" width="500" height="500"></canvas>
    <hr>
    <h3>ウィンドウごとの表示時間</h3>
    <table border="1">
    <tr><th>プロセス名</th><th>ウィンドウ名</th><th>表示時間(秒)</th></tr>
tables_active
    </table>
  </body>
</html>

実行に必要なファイルの一括ダウンロード

下記からファイルを一括でダウンロードできます。

アイコン

get_operation_log_v1.1.0

1 ファイル 10.59 KB
ダウンロード

コメント

タイトルとURLをコピーしました