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

> Convierte registros de depuración DNS a CSV con DNSServer.DebugLogParser, importa el resultado en SQL Server y ejecuta una consulta simple para detectar actividad sospechosa de registros TXT.

---

LLMS index: [llms.txt](/v1.2.0.0/llms.txt)

---

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:

```powershell
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.

```sql
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  

```powershell
# 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.

```sql
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`:

```powershell
$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.