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

---

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

---

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:

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

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

## 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

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

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

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

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

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