Flusso di lavoro per l'analisi della sicurezza con SQL Server

Converti i log di debug DNS in CSV con DNSServer.DebugLogParser, importa il risultato in SQL Server ed esegui una semplice query di rilevamento per attività sospette sui record TXT.

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

Questo esempio mostra un flusso di lavoro pratico per l’analisi della sicurezza: analizza un log di debug DNS con Convert-DNSDebugLogFile, importa il CSV generato in SQL Server ed esegui una query che evidenzia volumi insolitamente alti di interrogazioni a record TXT.

Per la migliore interoperabilità, questo esempio scrive i timestamp in un formato simile all’ISO usando -OutputCulture 'sv-SE'.

Moduli richiesti

Questo esempio utilizza i seguenti moduli PowerShell:

  • DNSServer.DebugLogParser
  • SqlServer

Installali se necessario:

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

Scenario

Usa questo flusso di lavoro quando vuoi spostare i dati del log di debug DNS analizzati in SQL Server per poter:

  • cercare grandi dataset in modo efficiente
  • costruire query di rilevamento ripetibili
  • correlare attività tra più server DNS
  • conservare dati normalizzati per indagini successive

Output del passaggio di conversione

Convert-DNSDebugLogFile non scrive direttamente in SQL Server. Prima crea un file CSV. Quel file CSV è il dataset importato nel database.

In questo esempio:

  • log di input: C:\Administration\Logs\DNS\dns.log
  • CSV generato: C:\Administration\Logs\DNS\dns.csv
  • tabella di destinazione: dbo.DNSQueries

Creare la tabella di destinazione

Esegui la seguente istruzione una volta in SQL Server per creare la tabella di destinazione.

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;

Convertire il log e importare il CSV

Il seguente esempio PowerShell esegue l’intero flusso di lavoro:

  1. importa i moduli richiesti
  2. converte il log di debug DNS in CSV
  3. carica il CSV generato
  4. importa in blocco le righe in 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 "Il file CSV generato '$csvPath' non contiene righe."
}

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

Query per attività sospette sui record TXT

Una volta che i dati sono in SQL Server, puoi cercare i client che effettuano un numero insolitamente alto di query ai record 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;

Se preferisci eseguire la query da PowerShell, usa Invoke-Sqlcmd dal modulo 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

Perché le query TXT sono interessanti

Volumi elevati di interrogazioni ai record TXT possono valere la pena di essere esaminati perché potrebbero indicare:

  • tunneling DNS
  • esfiltrazione di dati tramite DNS
  • abuso dei record TXT da parte di malware o strumenti
  • client insolitamente rumorosi o mal configurati

Questa query è solo un punto di partenza. In produzione, dovresti regolare la soglia e aggiungere filtri che si adattino al tuo ambiente.

Note operative

  • Import-Csv carica l’intero file in memoria. Per esportazioni di log molto grandi, considera un approccio in streaming invece di costruire un intero DataTable.
  • Mantieni -ComputerName nel passaggio di conversione così i record rimangono attribuibili dopo l’ingestione centralizzata.
  • Usa un delimitatore e una cultura coerenti durante esportazione e importazione.
  • Valida la conservazione, l’indicizzazione e il controllo degli accessi in SQL Server prima di usare questo flusso di lavoro per l’archiviazione a lungo termine.