# SQL Server -tietokannan turvallisuusanalyysityönkulku

> Muunna DNS-vianmäärityslokit CSV-muotoon DNSServer.DebugLogParserilla, tuo tulos SQL Serveriin ja suorita yksinkertainen havaitsemiskysely epäilyttävälle TXT-tietueiden toiminnalle.

---

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

---

Tässä esimerkissä näytetään käytännön työnkulku turvallisuusanalytiikkaan: jäsennä DNS-vianmääritysloki `Convert-DNSDebugLogFile`-komennolla, tuo luotu CSV SQL Serveriin ja suorita kysely, joka korostaa epätavallisen suuria TXT-tietueiden hakumääriä.

Parhaan yhteensopivuuden varmistamiseksi tämä esimerkki kirjoittaa aikaleimat ISO-tyyppisessä muodossa käyttämällä `-OutputCulture 'sv-SE'`.

## Tarvittavat moduulit

Tämä esimerkki käyttää seuraavia PowerShell-moduuleja:

- `DNSServer.DebugLogParser`
- `SqlServer`

Asenna ne tarvittaessa:

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

## Tilanne

Käytä tätä työnkulkua, kun haluat siirtää jäsenneltyjä DNS-vianmäärityslokitietoja SQL Serveriin, jotta voit:

- hakea suuria tietoaineistoja tehokkaasti
- rakentaa toistettavia havaitsemiskyselyjä
- korreloida toimintaa useiden DNS-palvelimien välillä
- säilyttää normalisoidut tiedot myöhempää tutkimusta varten

## Muunnosvaiheen tulos

`Convert-DNSDebugLogFile` ei kirjoita suoraan SQL Serveriin. Se luo ensin CSV-tiedoston. Tämä CSV-tiedosto on tietoaineisto, joka tuodaan tietokantaan.

Tässä esimerkissä:

- syöttöloki: `C:\Administration\Logs\DNS\dns.log`
- luotu CSV: `C:\Administration\Logs\DNS\dns.csv`
- kohdetaulu: `dbo.DNSQueries`

## Luo kohdetietokanta

Suorita seuraava lause SQL Serverissä kerran luodaksesi kohdetietokannan.

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

## Muunna loki ja tuo CSV

Seuraava PowerShell-esimerkki suorittaa koko työnkulun:

1. tuo tarvittavat moduulit
2. muuntaa DNS-vianmäärityslokin CSV-muotoon
3. lataa luodun CSV:n
4. tuo rivit eräajona SQL Serveriin

```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 "The generated CSV file '$csvPath' does not contain any rows."
}

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

## Kysely epäilyttävälle TXT-tietueiden toiminnalle

Kun tiedot ovat SQL Serverissä, voit hakea asiakkaita, jotka tekevät epätavallisen suuren määrän TXT-tietuehakukyselyjä.

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

Jos haluat suorittaa kyselyn PowerShellistä, käytä `Invoke-Sqlcmd`-komentoa `SqlServer`-moduulista:

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

## Miksi TXT-kyselyt ovat kiinnostavia

TXT-tietueiden hakujen suuri määrä voi olla tarkastelun arvoista, koska se voi viitata:

- DNS-tunnelointiin
- tietojen ulosvuotoon DNS:n kautta
- TXT-tietueiden väärinkäyttöön haittaohjelmien tai työkalujen toimesta
- epätavallisen meluisiin tai virheellisesti konfiguroituihin asiakkaisiin

Tämä kysely on vain lähtökohta. Tuotantoympäristössä sinun tulisi säätää kynnysarvoa ja lisätä suodattimia, jotka sopivat omaan ympäristöösi.

## Käyttöhuomautuksia

- `Import-Csv` lukee koko tiedoston muistiin. Erittäin suurten lokitiedostojen kanssa kannattaa harkita virtaavaa käsittelyä täyden `DataTable`n rakentamisen sijaan.
- Säilytä `-ComputerName` muunnosvaiheessa, jotta tietueet voidaan edelleen yhdistää lähteeseen keskitetyn tuonnin jälkeen.
- Käytä yhtenäistä erotinta ja kulttuuria vienti- ja tuontivaiheissa.
- Varmista säilytys, indeksointi ja käyttöoikeudet SQL Serverissä ennen tämän työnkulun käyttämistä pitkäaikaiseen tallennukseen.