Security Analysis Workflow with SQL Server

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

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:

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.

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

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

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:

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