Início / dba / Perguntas / 238796
Accepted
John Eisbrener
Asked: 2019-05-23 07:27:39 +0800 CST

Informações de Métricas do Arquivo de Auditoria do SQL Server

  • 772

Atualmente, estou trabalhando em um ambiente que exige que habilitemos o SQL Server Audit , em toda a empresa. Geramos .sqlauditarquivos pois temos diretrizes de retenção e acesso, e estou descobrindo que precisamos compilar informações de tendências sobre os arquivos gerados, como número criado por hora, volume total de informações de auditoria, etc. de DMVs/DMFs que fornecem informações de definição de auditoria (a lista pode ser encontrada aqui ), mas nada relacionado à saída do processo de Auditoria do SQL Server.

Atualmente, estou executando scripts do PowerShell para coletar essas informações dos metadados dos .sqlauditarquivos, mas prefiro não depender de um processo externo, se possível. Eu examinei procedimentos armazenados estendidos, como xp_dirtree, mas isso só retorna o número de arquivos e não as informações de tamanho, portanto, não fornece uma imagem tão completa quanto eu preciso aqui. xp_cmdshell, embora viável, não é permitido por motivos óbvios de segurança e porque a conformidade com o NIST proíbe explicitamente seu uso. Existem abordagens dentro do mecanismo de banco de dados (estou perguntando sobre coisas que posso atingir com o TSQL) onde posso obter essas informações, como um evento estendido ou DMV oculto que ainda não encontrei? Sinto que fiz uma extensa pesquisa, mas nunca me surpreendo se perder alguma coisa.

sql-server audit

1 respostas

  1. Best Answer

    Eu também posso postar minha abordagem terribad caso outros estejam interessados. Basicamente, o seguinte é um processo de 2 etapas. A primeira é criar uma tabela em um banco de dados de sua escolha que conterá um log dos metadados do arquivo de auditoria que você deseja coletar:

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

    Estou simplificando apenas rastreando o nome, a data de criação e o tamanho (comprimidos e descompactados). Por que compactado? Bem, temos sistemas em nosso ambiente que geram terabytes de atividade de auditoria por dia, então temos que compactar os .sqlauditarquivos imediatamente após serem gerados, caso contrário nos deparamos com uma situação de Tribble . Por causa disso, a rotina do PowerShell abaixo extrai alguns metadados dos .ziparquivos diretamente.

    A segunda parte desse processo é configurar um trabalho SQLAgent com uma etapa do PowerShell que aciona o seguinte:

    #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"

    O que essa rotina faz é identificar o caminho no qual os arquivos de auditoria são gravados, bem como o registro de data e hora do último arquivo de auditoria rastreado. Usando isso, ele executa um Get-ChildItemno diretório identificado procurando por todos os *.ziparquivos que retornam apenas os arquivos mais antigos que o último arquivo que identificamos anteriormente.

    SUPOSIÇÕES:

    1. Essa rotina assume que a definição de auditoria está configurada de forma que a incapacidade de gravar um arquivo de auditoria resultará no desligamento do servidor. Se você não tem esse requisito, você vai querer ajustar a consulta (ou seja, esse é o motivo do predicado WHERE sfa.on_failure = 1)
    2. Esta rotina está apenas procurando por .ziparquivos. Se você não compactar seus arquivos .sqlaudit, você se deparará com um cenário em que os metadados do último .sqlauditarquivo provavelmente serão alterados na próxima execução porque o mecanismo de banco de dados ainda está gravando nele. Nesse caso, sugiro ignorar o último arquivo retornado na matriz, o que deve evitar dados duplicados/inconsistentes.

    Finalmente, isso é totalmente específico para o meu ambiente, mas faz exatamente o que eu preciso. Se você tiver sugestões, atualizações, etc. poste outra resposta, pois mais ideias são sempre apreciadas.

    • 0

relate perguntas