Powershell Snippet - Query Azure Data Explorer

This code snippet demonstrates how to run a Kusto query against Azure Data Explorer (ADX) using PowerShell.

#-----------------------------------------
#  ADX Query
#-----------------------------------------

# Query for Azure Data Explorer
$KQLQuery              = "AADNonInteractiveUserSignInLogs | take 5"

#-----------------------------------------
# Application Registration details 
#-----------------------------------------

$Tenant     = 'laurierhodes.info'
$AppId      = 'aaaaaaaa-aaaa-aaaa-aaaaaaaaaaaa' 
$AppSecret  = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' 

$ADXCluster = "myadxcluster.australiasoutheast.kusto.windows.net"


#-----------------------------------------
# Constants
#-----------------------------------------

$ResourceID = "https://api.kusto.windows.net"

#-----------------------------------------
# Main
#-----------------------------------------


$Body = @{
    client_id     = $AppId  
    client_secret = $AppSecret
    resource      = $ResourceID
    grant_type    = "client_credentials"
}


$TokenEndpoint  = "https://login.microsoftonline.com/$($tenant)/oauth2/token"

$Response       = Invoke-WebRequest -UseBasicParsing -Uri $TokenEndpoint -Method "POST" -Body $Body 

$Responseobject = convertfrom-json -inputobject $Response.Content


# Add the token to headers for the ADX request

$Header         = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"

$null           = $Header.Add("Authorization", "Bearer "+$Responseobject.access_token)
$null           = $Header.Add("Content-Type", "application/json")
$null           = $Header.Add("Host", $ADXCluster)



$BodyObject = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$BodyObject.Add("db", "logs")
$BodyObject.Add("csl", "$($KQLQuery)")


$Body = @"
{
  "db":"logs",
  "csl":"$($KQLQuery)",
  "properties":"{\"Options\":{\"queryconsistency\":\"strongconsistency\"},\"Parameters\":{},\"ClientRequestId\":\"MyApp.Query;$([guid]::NewGuid())\"}"
}
"@


$uri = "https://securitylogs.australiasoutheast.kusto.windows.net/v2/rest/query"

$result = $null

$result = Invoke-WebRequest -Method POST -Uri $URI -Headers $header -Body (convertto-json -InputObject $BodyObject ) -UseBasicParsing


$responseobject = $null
$responseobject = convertfrom-json -inputobject $result.Content

$data = ($responseobject | Where-Object {$_.TableKind -Match "PrimaryResult"})


   $headerRow = $null
    $headerRow = $data.columns.ColumnName
    $columnsCount = $data.columns.Count

    # Format the Report
    $logData = @()
    foreach ($row in $data.rows) {
        $record = new-object PSObject
        for ($i = 0; $i -lt $columnsCount; $i++) {
            $record | add-member -membertype NoteProperty -name $headerRow[$i] -value $row[$i]
        }
        $logData += $record
        $record = $null
    }


# Display Log Data
$logData