昨天說到有了資料，今天就來說明處理的方式。

需求

基本上的需求如下

• 原始資料保存
• 人員使用的帳號(客服或帳務處理)作業需在每日統計報表裡呈現
• 資料庫管理者使用的帳號作業需在每日統計報表裡呈現
• DCL 與 DDL 作業時需即時發送通知給主管
• 每日使用狀態報表

重要資料紀錄 DB

針對可得到的資料要作一個紀錄，用資料庫處理。

CREATE DATABASE IF NOT EXISTS `auditdb`;
USE `auditdb`;

CREATE TABLE IF NOT EXISTS `auditdata` (
  `timestamp` datetime NOT NULL,
  `serverhost` char(100) NOT NULL,
  `username` char(100) NOT NULL,
  `hosts` char(100) NOT NULL,
  `connectionid` char(100) NOT NULL,
  `queryid` char(100) NOT NULL,
  `operation` char(100) NOT NULL,
  `database` char(100) NOT NULL,
  `object` varchar(10000) DEFAULT NULL,
  `retcode` char(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

相關人員帳號使用情形紀錄

看 [username] 欄位即可得到資訊。

if ($Users -contains $username)
                {
                    $sql = "INSERT INTO " + $AuditDB + "." + $TraceFileData + " VALUES (STR_TO_DATE('" + $timestamp + "','%Y%m%d %H:%i:%S'), '" + $serverhost + "', '" + $username + "', '" + $hosts + "', '" + $connectionid + "', '" + $queryid + "', '" + $operation + "', '" + $database + "', '" + $object + "', '" + $retcode +"');"    
                    & $mysqlexe -u $AuditDBUserName --password=$AuditDBPassword -h $AuditDBServer  -e "$($sql)"
}

DCL 與 DDL 作業即時告警

如果 server_audit.dll 是用之前說的方式重新作一個，這邊就很簡單：看 [operation] 是不是 QUERY_DCL，QUERY_DML，QUERY_DDL 就可以了。如果不是用之前的方式，這邊就要分析 [object] 裡頭的內容了。

把所需的資料放到一個 ArrayList 裡，全部都 Parsing 完以後看看是不是有資料，有的話就發信告警。

if ($MailEventClasses -contains $operation)
{
    $DXL.Add(($timestamp, $serverhost, $username, $hosts, $connectionid, $queryid, $operation, $database, $object, $retcode))
}
...
if ($DXL.Count -ne 0)
{
    $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>"

        $DXL | Foreach-Object {

        $output = $output + "<tr><td>" + $_[0] + "</td><td>" +  $_[2] + "</td><td>" + $_[3] + "</td><td>" + $_[1] + "</td><td>" + $_[7] + "</td><td>" + $_[6] + "</td><td>" + $_[8] + "</td><td>" + $_[9] + "</td></tr>"
    }

    $output = $output + "</table>"
    Send-MailMessage -To $MailTo -From $MailFrom -Subject "資料庫特權活動即時告警" -Body "$output" -BodyAsHtml -SmtpServer $MailServer -Encoding ([System.Text.Encoding]::UTF8)        
}