# Security Analysis Workflow with SQL Server

> Convert DNS debug logs to CSV with DNSServer.DebugLogParser, import the result into SQL Server, and run a simple detection query for suspicious TXT record activity.

---

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

---

This example shows a practical workflow for security analytics: parse a DNS
debug log with `Convert-DNSDebugLogFile`, import the generated CSV into SQL
Server, and run a query that highlights unusually high volumes of TXT record
lookups.

For best interoperability, this example writes timestamps in an ISO-like format
by using `-OutputCulture 'sv-SE'`.

## Required modules

This example uses the following PowerShell modules:

- `DNSServer.DebugLogParser`
- `SqlServer`

Install them if needed:

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

## Scenario

Use this workflow when you want to move parsed DNS debug log data into SQL
Server so you can:

- search large datasets efficiently
- build repeatable detection queries
- correlate activity across multiple DNS servers
- retain normalized data for later investigation

## Output of the conversion step

`Convert-DNSDebugLogFile` does not write directly to SQL Server. It first
creates a CSV file. That CSV file is the dataset imported into the database.

In this example:

- input log: `C:\Administration\Logs\DNS\dns.log`
- generated CSV: `C:\Administration\Logs\DNS\dns.csv`
- destination table: `dbo.DNSQueries`

## Create the target table

Run the following statement once in SQL Server to create the target table.

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

## Convert the log and import the CSV

The following PowerShell example performs the full workflow:

1. imports the required modules
2. converts the DNS debug log to CSV
3. loads the generated CSV
4. bulk imports the rows into 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 "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()
}
```

## Query for suspicious TXT record activity

Once the data is in SQL Server, you can search for clients that issue unusually
high numbers of TXT record queries.

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

If you prefer to run the query from PowerShell, use `Invoke-Sqlcmd` from the
`SqlServer` module:

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

## Why TXT queries are interesting

High volumes of TXT record lookups can be worth reviewing because they may
indicate:

- DNS tunneling
- data exfiltration over DNS
- abuse of TXT records by malware or tooling
- unusually noisy or misconfigured clients

This query is only a starting point. In production, you should tune the
threshold and add filters that match your environment.

## Operational notes

- `Import-Csv` reads the full file into memory. For very large log exports,
  consider a streaming approach instead of building a full `DataTable`.
- Keep `-ComputerName` in the conversion step so records remain attributable
  after central ingestion.
- Use a consistent delimiter and culture during export and import.
- Validate retention, indexing, and access control in SQL Server before using
  this workflow for long-term storage.