Flujo de trabajo de análisis de seguridad con SQL Server

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

Este ejemplo muestra un flujo de trabajo práctico para análisis de seguridad: analiza un registro de depuración DNS con Convert-DNSDebugLogFile, importa el CSV generado en SQL Server y ejecuta una consulta que resalta volúmenes inusualmente altos de búsquedas de registros TXT.

Para una mejor interoperabilidad, este ejemplo escribe marcas de tiempo en un formato tipo ISO usando -OutputCulture 'sv-SE'.

Módulos requeridos

Este ejemplo utiliza los siguientes módulos de PowerShell:

• DNSServer.DebugLogParser
• SqlServer

Instálalos si es necesario:

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

Escenario

Usa este flujo de trabajo cuando quieras mover datos analizados de registros de depuración DNS a SQL Server para poder:

• buscar grandes conjuntos de datos de forma eficiente
• crear consultas de detección repetibles
• correlacionar actividad en múltiples servidores DNS
• conservar datos normalizados para investigaciones posteriores

Salida del paso de conversión

Convert-DNSDebugLogFile no escribe directamente en SQL Server. Primero crea un archivo CSV. Ese archivo CSV es el conjunto de datos que se importa a la base de datos.

En este ejemplo:

• registro de entrada: C:\Administration\Logs\DNS\dns.log
• CSV generado: C:\Administration\Logs\DNS\dns.csv
• tabla destino: dbo.DNSQueries

Crear la tabla destino

Ejecuta la siguiente instrucción una vez en SQL Server para crear la tabla destino.

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;

Convertir el registro e importar el CSV

El siguiente ejemplo en PowerShell realiza el flujo completo:

1. importa los módulos requeridos
2. convierte el registro de depuración DNS a CSV
3. carga el CSV generado
4. importa masivamente las filas a 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 "El archivo CSV generado '$csvPath' no contiene filas."
}

$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()
}

Consulta para actividad sospechosa en registros TXT

Una vez que los datos están en SQL Server, puedes buscar clientes que realicen un número inusualmente alto de consultas de registros 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;

Si prefieres ejecutar la consulta desde PowerShell, usa Invoke-Sqlcmd del módulo SqlServer:

$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

Por qué las consultas TXT son interesantes

Los volúmenes altos de búsquedas de registros TXT pueden valer la pena revisarse porque pueden indicar:

• túneles DNS
• exfiltración de datos por DNS
• abuso de registros TXT por malware o herramientas
• clientes inusualmente ruidosos o mal configurados

Esta consulta es solo un punto de partida. En producción, deberías ajustar el umbral y agregar filtros que se adapten a tu entorno.

Notas operativas

• Import-Csv lee el archivo completo en memoria. Para exportaciones de registros muy grandes, considera un enfoque de streaming en lugar de construir un DataTable completo.
• Mantén -ComputerName en el paso de conversión para que los registros sigan siendo atribuibles después de la ingestión centralizada.
• Usa un delimitador y cultura consistentes durante la exportación e importación.
• Valida la retención, indexación y control de acceso en SQL Server antes de usar este flujo para almacenamiento a largo plazo.