In Powershell, error when calling Azure Log Analytics query containing 'extend'

EDIT

Clarification, issue happens on both PowerShell versions due to az CLI implemented as a batch file (mistakenly took it for a .NET console app). The solution replacing " for "" works on both versions (tested).

If you want to get rid of these kind of issues for good, my recommendation will always be to avoid any type of external application when the same can be accomplished with a PowerShell Module, in this case you can install Az.OperationalInsights & Az.Accounts Modules:

Install-Module Az.OperationalInsights, Az.Accounts -Scope CurrentUser

Then, the code to accomplish the same becomes (you can do a one-liner too but becomes pretty unreadable):

Connect-AzAccount

$params = @{
    ResourceGroupName = '...tbd...'
    WorkspaceName     = '...tbd...'
    SavedSearchId     = '...tbd...'
}
$savedSearch = Get-AzOperationalInsightsSavedSearch @params

$params = @{
    WorkspaceId = 'xxxx-xxxx-xxxx-....'
    Query       = $savedSearch.Properties.Query
}
Invoke-AzOperationalInsightsQuery @params

To give more context, the issue very likely only happens in Windows PowerShell 5.1, in PowerShell 7 doesn't seem to be a problem. What is happening is that the shell is eating all double-quotes in the query passed as argument of --analytics-query, to prove it and helping visualizing the issue, we can use a little inline console application (assuming you have the .NET 10 SDK, you can run them to test yourself):

$query = @'
AzureDiagnostics
| where ResourceType == 'VAULTS'
| where OperationName contains '' 'Secret' or OperationName contains '' 'Key'
| where TimeGenerated > ago (60d)
| summarize Count = count() by id_s
| extend findchar = indexof(id_s,".",1,-1,1) -8
| extend kv = substring(id_s,8,findchar)
| extend findchar = indexof(id_s,"/",1,-1,4) +1
| extend sec = substring(id_s,findchar)
| extend findchar = indexof(sec,"/",1,-1,1)
| extend sec = iff((findchar != -1),substring(sec,0,findchar),sec)
| project kv, sec
'@

'
using System;

for (int i = 0; i < args.Length; i++)
    Console.WriteLine($"[{i}] {args[i]}");' |
dotnet run - -f net10.0 -- az monitor log-analytics query -w 123 --analytics--query $query

The above, in PS 7+ and assuming you are using the default value (Windows) for $PSNativeCommandArgumentPassing, would output:

[0] az
[1] monitor
[2] log-analytics
[3] query
[4] -w
[5] 123
[6] --analytics--query
[7] AzureDiagnostics
| where ResourceType == 'VAULTS'
| where OperationName contains '' 'Secret' or OperationName contains '' 'Key'
| where TimeGenerated > ago (60d)
| summarize Count = count() by id_s
| extend findchar = indexof(id_s,".",1,-1,1) -8
| extend kv = substring(id_s,8,findchar)
| extend findchar = indexof(id_s,"/",1,-1,4) +1
| extend sec = substring(id_s,findchar)
| extend findchar = indexof(sec,"/",1,-1,1)
| extend sec = iff((findchar != -1),substring(sec,0,findchar),sec)
| project kv, sec

But, in PS 5.1 you can see the problem, all " are gone:

[0] az
[1] monitor
[2] log-analytics
[3] query
[4] -w
[5] 123
[6] --analytics--query
[7] AzureDiagnostics
| where ResourceType == 'VAULTS'
| where OperationName contains '' 'Secret' or OperationName contains '' 'Key'
| where TimeGenerated > ago (60d)
| summarize Count = count() by id_s
| extend findchar = indexof(id_s,.,1,-1,1) -8
| extend kv = substring(id_s,8,findchar)
| extend findchar = indexof(id_s,/,1,-1,4) +1
| extend sec = substring(id_s,findchar)
| extend findchar = indexof(sec,/,1,-1,1)
| extend sec = iff((findchar != -1),substring(sec,0,findchar),sec)
| project kv, sec

As for the solution, as you've already figured out, replacing all " for ' in the KQL query solves it. And, for another solution that wouldn't require to change the query, doubling down on " seems to also solve the problem, this means adding .Replace to the resulted string of the inner command call (shortened for visibility):

$secrets = az monitor ... "$(az monitor log-analytics workspace...)".Replace('"', '""')

• The problem stems from embedded " characters in your string and the sad reality in Windows PowerShell (the legacy edition whose latest and last version is 5.1) is that an extra, manual layer of \-escaping of embedded " characters is required in arguments passed to external programs such as az.

  • See this answer for background information on this long-standing problem, which will not be fixed in Windows PowerShell, given that it will see security-critical fixes only and that backward compatibility must be maintained.

  • While this unexpected need for manual escaping is generally no longer necessary in PowerShell (Core) 7 (in v7.3+), it is in your case, as explained below.

In your particular case:

The workaround, given that you have the flexibility to use ' and " quoting interchangeably in your particular scenario, is to use only '...' quoting, which avoids the problem.

In cases where you need to stick with embedded "..." quoting, e.g. when passing JSON, you have to manual escaping is needed even in PowerShell (Core) 7 by default, because the az CLI happens to be implemented as a batch file, az.cmd (which delegates to python.exe) as a result of which - due to $PSNativeCommandArgumentPassing preference variable defaulting to 'Windows' on Windows - the broken legacy argument-passing is still used for batch files, among others.

Thus, a cross-edition solution is to unconditionally perform this escaping programmatically; using a simple example (create a batch file .\some.cmd with the following content: @echo %*):

# Define a string with embedded " chars.
$string = ' "foo": "bar" '

# Pass it to a batch file, with " chars. escaped as \"
# NOTE: In PowerShell (Core) 7, do this ONLY for executables and scripts 
#       to which legacy argument-passing applies by default:
#         batch files, sqlcmd.exe, ...
#       See the PSNativeCommandArgumentPassing docs link above for the full list.
.\some.cmd $string.Replace('"', '\"')

Note:

• A more sophisticated, regex-based replacement technique is needed if the input string may contain verbatim \" sequences that need to be preserved as such:
  ($string -replace '(\\*)"', '$1$1\"')

A PowerShell (Core) 7-only alternative is to set $PSNativeCommandArgumentPassing to 'Standard', which applies proper escaping to all external programs and scripts.

# PowerShell 7 only

# Define a string with embedded " chars.
$string = ' "foo": "bar" '

& {  # (Use a script block to scope the $PSNativeCommandArgumentPassing change.)

  # Instruct PowerShell to *unconditionally* escape embedded " chars. as \"
  $PSNativeCommandArgumentPassing = 'Standard'

  # Now the string can be passed as-is to the batch file.
  .\some.cmd $string

}

In both cases, the batch file will echo verbatim " \"foo\": \"bar\" ", showing the proper escaping of the embedded " chars. inside the "..."-enclosed argument (since external CLIs can only be expected to understand "..." quoting, PowerShell must translate even what was originally '...' quoting to "..." when constructing the process command line behind the scenes).

Without the techniques above, you'd see verbatim " "foo": "bar" ", and the lack of escaping of the embedded " chars. causes standard CLIs to parse this argument as
verbatim foo: bar (because it is interpreted as a single string composed of directly concatenated double-quoted and unquoted parts), resulting in the effective loss of the embedded " chars.