Sicherheitsanalyse-Workflow mit SQL Server

Konvertiere DNS-Debug-Logs mit DNSServer.DebugLogParser in CSV, importiere das Ergebnis in SQL Server und führe eine einfache Abfrage zur Erkennung verdächtiger TXT-Record-Aktivitäten aus.

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

Dieses Beispiel zeigt einen praktischen Workflow für Sicherheitsanalysen: Ein DNS-Debug-Log wird mit Convert-DNSDebugLogFile geparst, die erzeugte CSV-Datei in SQL Server importiert und eine Abfrage ausgeführt, die ungewöhnlich hohe Mengen an TXT-Record-Abfragen hervorhebt.

Für beste Interoperabilität schreibt dieses Beispiel Zeitstempel in einem ISO-ähnlichen Format, indem -OutputCulture 'sv-SE' verwendet wird.

Erforderliche Module

Dieses Beispiel verwendet folgende PowerShell-Module:

  • DNSServer.DebugLogParser
  • SqlServer

Installiere sie bei Bedarf:

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

Szenario

Nutze diesen Workflow, wenn du geparste DNS-Debug-Log-Daten in SQL Server verschieben möchtest, um:

  • große Datensätze effizient zu durchsuchen
  • wiederholbare Erkennungsabfragen zu erstellen
  • Aktivitäten über mehrere DNS-Server hinweg zu korrelieren
  • normalisierte Daten für spätere Untersuchungen zu speichern

Ausgabe des Konvertierungsschritts

Convert-DNSDebugLogFile schreibt nicht direkt in SQL Server. Zuerst wird eine CSV-Datei erstellt. Diese CSV-Datei ist der Datensatz, der in die Datenbank importiert wird.

In diesem Beispiel:

  • Eingabe-Log: C:\Administration\Logs\DNS\dns.log
  • erzeugte CSV: C:\Administration\Logs\DNS\dns.csv
  • Ziel-Tabelle: dbo.DNSQueries

Erstelle die Zieltabelle

Führe die folgende Anweisung einmalig in SQL Server aus, um die Zieltabelle zu erstellen.

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;

Konvertiere das Log und importiere die CSV

Das folgende PowerShell-Beispiel führt den kompletten Workflow aus:

  1. Importiert die benötigten Module
  2. Konvertiert das DNS-Debug-Log in CSV
  3. Lädt die erzeugte CSV-Datei
  4. Importiert die Zeilen per Bulk-Import 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 "Die erzeugte CSV-Datei '$csvPath' enthält keine Zeilen."
}

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

Abfrage nach verdächtiger TXT-Record-Aktivität

Sobald die Daten in SQL Server sind, kannst du nach Clients suchen, die ungewöhnlich viele TXT-Record-Abfragen stellen.

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;

Wenn du die Abfrage lieber aus PowerShell heraus ausführst, nutze Invoke-Sqlcmd aus dem SqlServer-Modul:

$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

Warum TXT-Abfragen interessant sind

Hohe Volumina von TXT-Record-Abfragen können eine Überprüfung wert sein, da sie auf Folgendes hinweisen können:

  • DNS-Tunneling
  • Datenexfiltration über DNS
  • Missbrauch von TXT-Records durch Malware oder Tools
  • ungewöhnlich laute oder falsch konfigurierte Clients

Diese Abfrage ist nur ein Ausgangspunkt. Im produktiven Einsatz solltest du die Schwelle anpassen und Filter hinzufügen, die zu deiner Umgebung passen.

Betriebshinweise

  • Import-Csv liest die gesamte Datei in den Speicher ein. Bei sehr großen Log-Exports solltest du einen Streaming-Ansatz statt eines vollständigen DataTable-Aufbaus in Betracht ziehen.
  • Behalte -ComputerName im Konvertierungsschritt bei, damit die Datensätze nach zentraler Einspeisung zugeordnet bleiben.
  • Verwende während Export und Import einen konsistenten Trenner und eine konsistente Kultur.
  • Prüfe Aufbewahrung, Indizierung und Zugriffskontrolle in SQL Server, bevor du diesen Workflow für Langzeitspeicherung nutzt.