Querying ADX with PowerShell and REST

 The following script provides an example of using PowerShell to directly query Azure Data Explorer Records using REST.

Prerequisites

You will also need to ensure that the Application you use is granted the Database Viewer role permission.   This role can be added through the portal on the database Overview -> Permissions -> Add.   Note that ADX does not support table level viewer permissions.  

PowerShell Script
 

This script executes the PowerShell query and produces an array of JSON objects for any records returned.  

<#
  Purpose:  Run queries against Azure Data Explorer

#>

# Variables
$clusterUrl = "https://securitylogs.australiaeast.kusto.windows.net"
$databaseName = "mydb"
$appId = "e3bd024a-b849-4dd2-8ce6-af88b4323ddf"
$secret = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
$tenantId = "08a3b042-84bd-4cc7-81eb-7d2879fb1d61"
$resource = "https://kusto.kusto.windows.net"

# Get Access Token
$body = @{
    grant_type    = "client_credentials"
    client_id     = $appId
    client_secret = $secret
    resource      = $resource
}
$response = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$tenantId/oauth2/token" -ContentType "application/x-www-form-urlencoded" -Body $body
$token = $response.access_token

# KQL query to execute

$kqlQuery = @"
Syslog 
| where TimeGenerated > ago(1d)
| where SeverityLevel == 'error'
"@

# Set headers
$headers = @{
    'Content-Type' = 'application/json'
    'Authorization' = "Bearer $token"
}

# Query URI
$queryUri = "$clusterUrl/v2/rest/query"

# Request body
$queryBody = @{
    "db" = $databaseName
    "csl" = $kqlQuery
}


# Initialize an array to store the results
$results = @()

try {
    # Execute the KQL query
    $response = Invoke-RestMethod -Uri $queryUri -Method Post -Headers $headers -Body ($queryBody | ConvertTo-Json -Depth 5)


    foreach ($frame in $response) {
        switch ($frame.FrameType) {
            "DataSetHeader" {
                Write-Debug "DataSetHeader: Version=$($frame.Version), IsProgressive=$($frame.IsProgressive)"
            }
            "DataTable" {
                Write-Debug "DataTable: TableId=$($frame.TableId), TableKind=$($frame.TableKind), TableName=$($frame.TableName)"
                Write-Debug "Columns: $($frame.Columns | ConvertTo-Json -Depth 5)"

                if ($frame.TableKind -eq "PrimaryResult") {
                    $columns = $frame.Columns
                    foreach ($row in $frame.Rows) {
                        $record = @{}
                        for ($i = 0; $i -lt $columns.Count; $i++) {
                            $record[$columns[$i].ColumnName] = $row[$i]
                        }
                        $results += $record
                    }
                }

                Write-Debug "Rows:"
                $results | ConvertTo-Json -Depth 5
            }
            "DataSetCompletion" {
                Write-Debug "DataSetCompletion: HasErrors=$($frame.HasErrors), Cancelled=$($frame.Cancelled)"
                if ($frame.HasErrors) {
                    Write-Debug "Errors: $($frame.OneApiErrors | ConvertTo-Json -Depth 5)"
                }
            }
            default {
                Write-Output "Unknown frame type: $($frame.Type)"
            }
        }
    }
} catch {
    Write-Error "Failed to execute KQL query: $_"
    Write-Output "Error Details: $($_.Exception | Format-List -Force)"
}
finally {

    # Output the final array of JSON objects
    Write-Output "Final Result Array:"
    $results | ConvertTo-Json -Depth 5

}

Outputing JSON allows for the records to be more readily used with external systems.