主页 / dba / 问题 / 238796
Accepted
John Eisbrener
Asked: 2019-05-23 07:27:39 +0800 CST

SQL Server 审核文件指标信息

  • 772

我目前正在一个需要我们在企业范围内启用SQL Server Audit的环境中工作。我们生成.sqlaudit文件，因为我们有保留和访问指南，我发现我们需要编译有关生成的文件的趋势信息，例如每小时创建的数量、审计信息的总量等。目前，我只知道提供审计定义信息的 DMV/DMF 的数量（可以在此处找到列表），但与 SQL Server 审计过程的输出无关。

我目前正在运行 PowerShell 脚本来从.sqlaudit文件的元数据中收集这些信息，但如果可能的话，我不希望依赖外部进程。我研究了扩展存储过程，例如xp_dirtree，但它只返回文件数而不是大小信息，所以它没有提供我需要的完整图片。 xp_cmdshell，虽然可行，但出于明显的安全原因是不允许的，因为NIST 合规性明确禁止其使用。数据库引擎中是否有任何方法（我问的是我可以用 TSQL 解决的问题）我可以获得这些信息，例如我还没有遇到的扩展事件或隐藏的 DMV？我觉得我已经进行了一些广泛的挖掘，但如果我错过了什么，我从不感到惊讶。

sql-server audit

1 个回答

  1. Best Answer

    如果其他人感兴趣，我不妨张贴我的绝妙方法。基本上，以下是一个两步过程。首先是在您选择的数据库中创建一个表，该表将保存您希望收集的审核文件元数据的日志：

    CREATE TABLE dbo.Audit_FileMetrics
(
    ID  BIGINT  IDENTITY(1,1) NOT NULL,
    [Name] VARCHAR(512),
    [LastWriteTime] DATETIME2,
    [Length] BIGINT,
    [CompressedLength] BIGINT
)

    我通过只跟踪名称、创建日期和大小（压缩和未压缩）来保持简单。为什么要压缩？好吧，我们环境中的系统每天会产生数 TB 的审计活动，因此我们必须在.sqlaudit文件生成后立即对其进行压缩，否则我们会遇到Tribble情况。因此，下面的 PowerShell 例程.zip直接从文件中提取一些元数据。

    此过程的第二部分是使用触发以下内容的 PowerShell 步骤设置 SQLAgent 作业：

    #required to read the header data from the .zip files
Add-Type -assembly "system.io.compression.filesystem"

#get filepath where audit files are written as well as the last file's timestamp
$filePath = Invoke-Sqlcmd -Query "SELECT TOP 1 sfa.log_file_path, af.LastWriteTime FROM sys.server_file_audits sfa CROSS APPLY(SELECT COALESCE(MAX(LastWriteTime), CAST('1900-01-01' AS DATETIME2)) AS LastWriteTime FROM <EnterDatabaseNameHere>.dbo.Audit_FileMetrics) af WHERE sfa.on_failure = 1" -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"
#not "needed", but if you run this block over and over in ISE, this is handy as it clears out the array
$auditFiles = @()
#get list of .zip files we have not yet collected meta data from
$files = ls $filePath.log_file_path -Filter "*.zip" | Where-Object {$_.LastWriteTime -gt $filePath.LastWriteTime}
ForEach($file IN $files)
{
    $myPath = $filePath.log_file_path + $file.Name
    #read/append file header meta data
    $auditFiles += [io.compression.zipfile]::OpenRead("$myPath").Entries | SELECT Name, LastWriteTime, Length, @{Name="CompressedLength";Expression={$file.Length}}
}

#build out INSERT command statement
$sqlCommandInsert = 'INSERT INTO dbo.Audit_FileMetrics ([Name], [LastWriteTime], [Length], [CompressedLength]) '
$sqlCommandValues = 'VALUES '
$sqlQuery = ''
$i = 0

ForEach($auditFile IN $auditFiles)
{
    $sqlCommandValues += "('" + $auditFile.Name + "', '" + $auditFile.LastWriteTime + "', " + $auditFile.Length + ", " + $auditFile.CompressedLength + "), "
    $i++

    if ($i -eq 1000)
    {
        #cannot insert more than 1000 VALUES tuples, so commit a batch of 1000
        $sqlQuery = $sqlCommandInsert + $sqlCommandValues.Substring(0, $sqlCommandValues.Length - 2)
        Invoke-Sqlcmd -Query $sqlQuery -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"
        $sqlCommandValues = 'VALUES '
        $i = 0
    }
}

#final insert into log table
$sqlQuery = $sqlCommandInsert + $sqlCommandValues.Substring(0, $sqlCommandValues.Length - 2)
Invoke-Sqlcmd -Query $sqlQuery -Database "<EnterDatabaseNameHere>" #-ServerInstance "localhost"

    该例程所做的是识别写入审计文件的路径以及跟踪的最后一个审计文件的时间戳。使用它，它会Get-ChildItem针对已识别的目录运行查找所有*.zip文件，这些文件仅返回比我们之前识别的最后一个文件更旧的文件。

    假设：

    1. 此例程假定审核定义已配置为无法写出审核文件将导致服务器关闭。如果你没有这个要求，你会想要调整查询（即这是谓词的原因WHERE sfa.on_failure = 1）
    2. 此例程仅查找.zip文件。如果您不压缩 .sqlaudit 文件，您将遇到这样一种情况，即最后一个.sqlaudit文件上的元数据可能会在下一次运行时发生变化，因为数据库引擎仍在写入它。在这种情况下，我建议忽略数组中返回的最后一个文件，这样可以避免任何重复/不一致的数据。

    最后，这完全特定于我的环境，但它正是我所需要的。如果您有建议、更新等，请发布另一个答案，因为更多的想法总是受到赞赏。

    • 0

相关问题