使用 SQL Server 的安全分析工作流

使用 DNSServer.DebugLogParser 将 DNS 调试日志转换为 CSV，导入 SQL Server， 并运行简单查询检测可疑的 TXT 记录活动。

For AI agents: a documentation index is available at /llms.txt; a markdown version of this page is available at /cn/docs/examples/security-analysis-sql/index.md.

此示例展示了一个实用的安全分析工作流：使用 Convert-DNSDebugLogFile 解析 DNS 调试日志，将生成的 CSV 导入 SQL Server，并运行查询以突出显示异常大量的 TXT 记录查询。

为了最佳的互操作性，此示例通过使用 -OutputCulture 'sv-SE' 以类似 ISO 的格式写入时间戳。

所需模块

此示例使用以下 PowerShell 模块：

  • DNSServer.DebugLogParser
  • SqlServer

如有需要，请安装它们：

Install-Module -Name DNSServer.DebugLogParser -Scope CurrentUser
Install-Module -Name SqlServer -Scope CurrentUser

场景

当你想将解析后的 DNS 调试日志数据移入 SQL Server，以便你可以：

  • 高效搜索大数据集
  • 构建可重复使用的检测查询
  • 关联多个 DNS 服务器的活动
  • 保留规范化数据以供后续调查

时，请使用此工作流。

转换步骤的输出

Convert-DNSDebugLogFile 不直接写入 SQL Server。它首先创建一个 CSV 文件。该 CSV 文件即为导入数据库的数据集。

在此示例中：

  • 输入日志：C:\Administration\Logs\DNS\dns.log
  • 生成的 CSV：C:\Administration\Logs\DNS\dns.csv
  • 目标表：dbo.DNSQueries

创建目标表

在 SQL Server 中运行以下语句一次，创建目标表。

IF OBJECT_ID('dbo.DNSQueries', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.DNSQueries (
        DateTime      datetime2(0)   NOT NULL,
        ThreadId      int            NULL,
        Context       nvarchar(20)   NULL,
        PacketId      int            NULL,
        Protocol      nvarchar(10)   NULL,
        Direction     nvarchar(10)   NULL,
        ClientIP      nvarchar(64)   NULL,
        Xid           nvarchar(16)   NULL,
        Type          nvarchar(16)   NULL,
        Opcode        nvarchar(16)   NULL,
        FlagsHex      nvarchar(16)   NULL,
        FlagsChar     nvarchar(16)   NULL,
        ResponseCode  nvarchar(32)   NULL,
        QuestionType  nvarchar(32)   NULL,
        QuestionName  nvarchar(512)  NULL,
        Information   nvarchar(max)  NULL,
        Details       nvarchar(max)  NULL,
        ComputerName  nvarchar(256)  NULL
    );
END;

转换日志并导入 CSV

以下 PowerShell 示例执行完整工作流：

  1. 导入所需模块
  2. 将 DNS 调试日志转换为 CSV
  3. 加载生成的 CSV
  4. 批量导入行到 SQL Server
# Requires -Modules DNSServer.DebugLogParser, SqlServer

Import-Module -Name DNSServer.DebugLogParser -ErrorAction Stop
Import-Module -Name SqlServer -ErrorAction Stop

$logPath = 'C:\Administration\Logs\DNS\dns.log'
$csvPath = 'C:\Administration\Logs\DNS\dns.csv'
$serverInstance = 'SQLServer'
$databaseName = 'DNSLogs'
$delimiter = ';'

$createTableSql = @'
IF OBJECT_ID('dbo.DNSQueries', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.DNSQueries (
        DateTime      datetime2(0)   NOT NULL,
        ThreadId      int            NULL,
        Context       nvarchar(20)   NULL,
        PacketId      int            NULL,
        Protocol      nvarchar(10)   NULL,
        Direction     nvarchar(10)   NULL,
        ClientIP      nvarchar(64)   NULL,
        Xid           nvarchar(16)   NULL,
        Type          nvarchar(16)   NULL,
        Opcode        nvarchar(16)   NULL,
        FlagsHex      nvarchar(16)   NULL,
        FlagsChar     nvarchar(16)   NULL,
        ResponseCode  nvarchar(32)   NULL,
        QuestionType  nvarchar(32)   NULL,
        QuestionName  nvarchar(512)  NULL,
        Information   nvarchar(max)  NULL,
        Details       nvarchar(max)  NULL,
        ComputerName  nvarchar(256)  NULL
    );
END
'@

Convert-DNSDebugLogFile `
    -InputFile $logPath `
    -ComputerName 'DNS01' `
    -OutputType CSV `
    -OutputFile $csvPath `
    -Delimiter $delimiter `
    -OutputCulture 'sv-SE'

$rows = Import-Csv -Path $csvPath -Delimiter $delimiter

if (-not $rows) {
    throw "生成的 CSV 文件 '$csvPath' 不包含任何行。"
}

$dataTable = [System.Data.DataTable]::new()
foreach ($columnName in $rows[0].PSObject.Properties.Name) {
    $null = $dataTable.Columns.Add($columnName, [string])
}

foreach ($row in $rows) {
    $dataRow = $dataTable.NewRow()
    foreach ($column in $dataTable.Columns) {
        $columnName = $column.ColumnName
        $dataRow[$columnName] = $row.$columnName
    }

    $null = $dataTable.Rows.Add($dataRow)
}

$connectionString = "Server=$serverInstance;Database=$databaseName;Integrated Security=True"
$connection = [System.Data.SqlClient.SqlConnection]::new($connectionString)

try {
    $connection.Open()

    $command = $connection.CreateCommand()
    $command.CommandText = $createTableSql
    $null = $command.ExecuteNonQuery()

    $bulkCopy = [System.Data.SqlClient.SqlBulkCopy]::new($connection)
    $bulkCopy.DestinationTableName = 'dbo.DNSQueries'

    foreach ($column in $dataTable.Columns) {
        $null = $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName)
    }

    $bulkCopy.WriteToServer($dataTable)
}
finally {
    $connection.Dispose()
}

查询可疑的 TXT 记录活动

数据进入 SQL Server 后，你可以搜索发出异常大量 TXT 记录查询的客户端。

SELECT
    ComputerName,
    ClientIP,
    QuestionName,
    COUNT(*) AS QueryCount
FROM dbo.DNSQueries
WHERE QuestionType = 'TXT'
GROUP BY
    ComputerName,
    ClientIP,
    QuestionName
HAVING COUNT(*) > 100
ORDER BY QueryCount DESC;

如果你更喜欢从 PowerShell 运行查询，可以使用 SqlServer 模块中的 Invoke-Sqlcmd：

$query = @'
SELECT
    ComputerName,
    ClientIP,
    QuestionName,
    COUNT(*) AS QueryCount
FROM dbo.DNSQueries
WHERE QuestionType = 'TXT'
GROUP BY
    ComputerName,
    ClientIP,
    QuestionName
HAVING COUNT(*) > 100
ORDER BY QueryCount DESC;
'@

Invoke-Sqlcmd `
    -ServerInstance 'SQLServer' `
    -Database 'DNSLogs' `
    -Query $query

为什么 TXT 查询很有意义

大量的 TXT 记录查询值得关注，因为它们可能表明：

  • DNS 隧道
  • 通过 DNS 进行数据外泄
  • 恶意软件或工具滥用 TXT 记录
  • 异常嘈杂或配置错误的客户端

此查询只是一个起点。在生产环境中，你应调整阈值并添加符合你环境的过滤条件。

运行注意事项

  • Import-Csv 会将整个文件读入内存。对于非常大的日志导出，考虑使用流式处理而非构建完整的 DataTable。
  • 在转换步骤中保留 -ComputerName，以便在集中摄取后仍能归属记录。
  • 导出和导入时使用一致的分隔符和文化设置。
  • 在将此工作流用于长期存储前，验证 SQL Server 中的保留策略、索引和访问控制。