Unexpected Token error in PowerShell for an AppInsight REST API query

Question

A guidance request here.

I have a rest API query that is querying Azure loganalytics workspace/application via powershell (v7.3.4). this is my query (modified for sharing here):

$appKey="myApiKey"
$appId="myApiAppId"
$resource="https://api.applicationinsights.io"
$secpasswd = ConvertTo-SecureString $appKey -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($appId, $secpasswd)

Connect-AzAccount -ServicePrincipal -Tenant "myTenantId" -Credential $mycreds
$res=Get-AzAccessToken -ResourceUrl $resource

$headers=@{"Authorization"="Bearer "+$res.Token}
$body=@{
            #"timespan" = "P90"; 
            "query" = "app('22222222-2222-2222-2222-222222222222').customEvents
            | where name == 'ItemProcessed'
            | where timestamp < now(-0d) and timestamp > now(-5d)
            | project timestamp, customDimensions, customMeasurements
            | extend TimeProperties = strcat('{"DateTimeStamp":"',timestamp,'"}'), customDimensions,customMeasurements
            | project TimeProperties,customDimensions,customMeasurements"
        } | ConvertTo-Json
$c = Invoke-RestMethod 'https://api.applicationinsights.io/v1/workspaces/11111111-1111-1111-1111-111111111111/query' -Method 'POST' -Headers $headers -Body $body -ContentType "application/json"
$c.tables | ConvertTo-Json

the powershell script is being run from VSCode 1.77.3 and it is flagging the following parse error:

ParserError: C:\Users\aquader\OneDrive - Nice Systems Ltd\Documents\ps\StackOverFlowApiQueryHelp.ps1:17:49
Line |
  17 |  … = strcat('{"DateTimeStamp":"',timestamp,'"}'), customDimensions,custo …
     |                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Unexpected token 'DateTimeStamp":"',timestamp,'"}'), customDimensions,customMeasurements             | project TimeProperties,customDimensions,customMeasurements"' in expression or statement.

Is there a more elegant way of passing the following in the $body?

| extend TimeProperties = strcat('{"DateTimeStamp":"',timestamp,'"}'), customDimensions,customMeasurements

the kql query runs fine:

I need to pass the timestamp info as a JSON key/val

Answer 1

I also received the same "unexpected token" error when I tried in my environment.

The issue is that the object getting through the body is improper. So, create a hashtable for the timeproperties object with @{} structure.

You can create a multiline string for the Kusto Query Language query while keeping formatting by placing the @ before the string.

Refer here to create a hashtable by @Doctor Scripto.

I modified your code as below:

$appKey="xxx"
$appId="AppID"
$resource="https://api.applicationinsights.io"
$secpwd = ConvertTo-SecureString $appKey -AsPlainText -Force
$creds = New-Object System.Management.Automation.PSCredential ($appId, $secpwd)

Connect-AzAccount -ServicePrincipal -Tenant "TenantID" -Credential $creds
$res=Get-AzAccessToken -ResourceUrl $resource

$headers=@{"Authorization"="Bearer "+$res.Token}
$body = @{
     "query" = @"app('xxxxxxxx').customEvents
 | where name == 'ItemProcessed'
 | where timestamp < now(-0d) and timestamp > now(-5d)
 | project timestamp, customDimensions, customMeasurements
 | extend TimeProperties = @{
     DateTimeStamp = timestamp
 }, customDimensions, customMeasurements
 | project TimeProperties, customDimensions, customMeasurements
 "@
 } | ConvertTo-Json

Output:

Refer MSDoc for the relevant information.