今天來說明每日匯總報表的作法。

如果之前的作法中，塞資料到資料庫的動作有作的話，這邊就十分簡單。

先設定一些資料。

$AuditDBServer = "server"
$AuditDB = "AuditDB"
$TraceFileData = "auditdata"
$AuditDBUserName = "username"
$AuditDBPassword = "password"
$mysqlexe = "D:\Program Files\MariaDB 10.1\bin\mysql.exe"
$Users = "'need','to','be','audit','users'"
$MailFrom = "from@abc.com"
$MailEventClasses = "'QUERY_DDL','QUERY_DCL'"
$MailTo = "to@def.com"
$MailServer = "smtp.server"

接下來移除 Audit 資料庫裡頭 3 天前的資料。

$NowTime = Get-Date
$TempPath = "D:\AuditFile"

$sql = "delete from " + $AuditDB + "." + $TraceFileData + " where timestamp < '"+("{0:yyyy-MM-dd}" -f $NowTime.AddDays(-3))+ "' or timestamp is Null;"
& $mysqlexe -u $AuditDBUserName --password=$AuditDBPassword -h $AuditDBServer  -e "$($sql)" 

符合 $MailEventClasses 的列出來，作為每日資料庫系統設定異動報表。

$output = "<table border=1 style=border-width: thin; border-spacing: 2px; border-style: solid; border-color: gray; border-collapse: collapse;>  
                 <tr><th>StartTime</th>
		     <th>LoginName</th>
	             <th>HostName</th>
		     <th>ServerName</th>
	             <th>DatabaseName</th>
	             <th>Operation</th>
                     <th>TextData</th>
                     <th>ReturnCode</th></tr>"

$sql  = "select '<tr><td>', ``timestamp``, '</td><td>', ``username`` , '</td><td>', ``hosts``, '</td><td>', ``serverhost`` , '</td><td>', ``database`` , '</td><td>', ``operation`` , '</td><td>', ``object``, '</td><td>', ``retcode`` , '</td></tr>' from " + $AuditDB + "." + $TraceFileData + " where operation in (" + $MailEventClasses + ") and timestamp between STR_TO_DATE('" +("{0:yyyy-MM-dd}" -f $NowTime.AddDays(-1))+ "', '%Y-%m-%d')  and STR_TO_DATE('" +("{0:yyyy-MM-dd}" -f $NowTime)+ "', '%Y-%m-%d')"
$output = $output + (& $mysqlexe -u $AuditDBUserName --password=$AuditDBPassword -h $AuditDBServer -sN -e "$($sql)" ) 
$output = $output + "</table>"

$Subj = $("{0:yyyy-MM-dd}" -f $NowTime.AddDays(-1)) + " 每日資料庫系統設定異動報表"
Send-MailMessage -To $MailTo -From $MailFrom -Subject "$Subj"  -Body "$output" -BodyAsHtml  -SmtpServer $MailServer -Encoding ([System.Text.Encoding]::UTF8)

符合 $Users 的列出來，作為每日資料庫使用者使用情形報表。

$output = "<table border=1 style=border-width: thin; border-spacing: 2px; border-style: solid; border-color: gray; border-collapse: collapse;>  
                 <tr><th>StartTime</th>
		     <th>LoginName</th>
	             <th>HostName</th>
		     <th>ServerName</th>
	             <th>DatabaseName</th>
	             <th>Operation</th>
                     <th>TextData</th>
                     <th>ReturnCode</th></tr>"

$sql  = "select '<tr><td>', ``timestamp``, '</td><td>', ``username`` , '</td><td>', ``hosts``, '</td><td>', ``serverhost`` , '</td><td>', ``database`` , '</td><td>', ``operation`` , '</td><td>', ``object``, '</td><td>', ``retcode`` , '</td></tr>' from " + $AuditDB + "." + $TraceFileData + " where username in (" + $Users + ") and timestamp between STR_TO_DATE('" +("{0:yyyy-MM-dd}" -f $NowTime.AddDays(-1))+ "', '%Y-%m-%d')  and STR_TO_DATE('" +("{0:yyyy-MM-dd}" -f $NowTime)+ "', '%Y-%m-%d')"
$sql
$output = $output + (& $mysqlexe -u $AuditDBUserName --password=$AuditDBPassword -h $AuditDBServer -sN -e "$($sql)" ) 
$output = $output + "</table>"


$Subj = $("{0:yyyy-MM-dd}" -f $NowTime.AddDays(-1)) + " 每日資料庫使用者使用情形報表"
Send-MailMessage -To $MailTo -From $MailFrom -Subject "$Subj"  -Body "$output" -BodyAsHtml  -SmtpServer $MailServer -Encoding ([System.Text.Encoding]::UTF8)