Przepływ pracy analizy bezpieczeństwa z SQL Server

Konwertuj dzienniki debugowania DNS na CSV za pomocą DNSServer.DebugLogParser, importuj wynik do SQL Server i uruchom proste zapytanie wykrywające podejrzaną aktywność rekordów TXT.

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

Ten przykład pokazuje praktyczny przepływ pracy dla analityki bezpieczeństwa: analizuje dziennik debugowania DNS za pomocą Convert-DNSDebugLogFile, importuje wygenerowany plik CSV do SQL Server i uruchamia zapytanie, które wyróżnia nietypowo dużą liczbę zapytań o rekordy TXT.

Dla najlepszej interoperacyjności ten przykład zapisuje znaczniki czasu w formacie podobnym do ISO, używając -OutputCulture 'sv-SE'.

Wymagane moduły

Ten przykład korzysta z następujących modułów PowerShell:

  • DNSServer.DebugLogParser
  • SqlServer

Zainstaluj je, jeśli to konieczne:

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

Scenariusz

Użyj tego przepływu pracy, gdy chcesz przenieść przetworzone dane dziennika debugowania DNS do SQL Server, aby móc:

  • efektywnie przeszukiwać duże zbiory danych
  • tworzyć powtarzalne zapytania wykrywające
  • korelować aktywność na wielu serwerach DNS
  • przechowywać znormalizowane dane do późniejszej analizy

Wynik kroku konwersji

Convert-DNSDebugLogFile nie zapisuje bezpośrednio do SQL Server. Najpierw tworzy plik CSV. Ten plik CSV jest zbiorem danych importowanym do bazy danych.

W tym przykładzie:

  • dziennik wejściowy: C:\Administration\Logs\DNS\dns.log
  • wygenerowany CSV: C:\Administration\Logs\DNS\dns.csv
  • docelowa tabela: dbo.DNSQueries

Utwórz tabelę docelową

Uruchom poniższe polecenie raz w SQL Server, aby utworzyć tabelę docelową.

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;

Konwersja dziennika i import CSV

Poniższy przykład PowerShell wykonuje cały przepływ pracy:

  1. importuje wymagane moduły
  2. konwertuje dziennik debugowania DNS na CSV
  3. wczytuje wygenerowany CSV
  4. masowo importuje wiersze do 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 "Wygenerowany plik CSV '$csvPath' nie zawiera żadnych wierszy."
}

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

Zapytanie o podejrzaną aktywność rekordów TXT

Gdy dane znajdują się w SQL Server, możesz wyszukać klientów, którzy wykonują nietypowo dużą liczbę zapytań o rekordy 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;

Jeśli wolisz uruchomić zapytanie z poziomu PowerShell, użyj Invoke-Sqlcmd z modułu 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

Dlaczego zapytania TXT są interesujące

Duża liczba zapytań o rekordy TXT może być warta przejrzenia, ponieważ może wskazywać na:

  • tunelowanie DNS
  • wyciek danych przez DNS
  • nadużycie rekordów TXT przez złośliwe oprogramowanie lub narzędzia
  • nietypowo hałaśliwych lub źle skonfigurowanych klientów

To zapytanie to tylko punkt wyjścia. W środowisku produkcyjnym powinieneś dostosować próg i dodać filtry odpowiadające twojej infrastrukturze.

Uwagi operacyjne

  • Import-Csv wczytuje cały plik do pamięci. Przy bardzo dużych eksportach dzienników rozważ podejście strumieniowe zamiast budowania pełnej DataTable.
  • Zachowaj -ComputerName w kroku konwersji, aby rekordy pozostały przypisane po centralnym zaimportowaniu.
  • Używaj spójnego separatora i kultury podczas eksportu i importu.
  • Przed użyciem tego przepływu pracy do długoterminowego przechowywania zweryfikuj retencję, indeksowanie i kontrolę dostępu w SQL Server.