New-AzKustoScript does not support the .show tables command

[eeertel]

Description

When executing the following command:

New-AzKustoScript `
    -ClusterName $clusterInfo["ClusterName"] `
    -ResourceGroupName $clusterInfo["ClusterResourceGroup"] `
    -DatabaseName $KUSTO_AUDIT_DATABASE_NAME `
    -Name "_custom_name" `
    -SubscriptionId (Get-AzContext).Subscription.Id `
    -ScriptContent ".show tables"

the response is: [BadRequest] The provided script contains unsupported command. The commands must start with the following verbs: '.create, .create-or-alter, .create-merge, .alter, .alter-merge, .enable'

The documentation does not specify a list of supported/unsupported commands. Please advise which cmdlet can be used to execute other commands (like .show xxx)

Script or Debug output

No response

Environment data

Name                           Value
----                           -----
PSVersion                      7.3.1
PSEdition                      Core
GitCommitId                    7.3.1
OS                             Microsoft Windows 10.0.19044
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Module versions

ModuleType Version    PreRelease Name                                ExportedCommands
---------- -------    ---------- ----                                ----------------
Script     2.10.4                Az.Accounts                         {Add-AzEnvironment, Clear-AzConfig, Clear-AzContext, Clear-AzDefault…}
Script     2.1.0                 Az.Kusto                            {Add-AzKustoClusterLanguageExtension, Add-AzKustoDatabasePrincipal, Get-AzKustoAttachedDatabaseConfiguration, Get…

Error output

PS C:\VSTS\ResourceAudit> REsolve-AzError
Upcoming breaking changes in the cmdlet 'Resolve-AzError' :
The `Resolve-Error` alias will be removed in a future release.  Please change any scripts that use this alias to use `Resolve-AzError` instead.
Note : Go to https://aka.ms/azps-changewarnings for steps to suppress this breaking change warning, and other information on breaking changes in Azure PowerShell.

   HistoryId: 2

Message        : [BadRequest] The provided script contains unsupported command. The commands must start with the following verbs: '.create, .create-or-alter, .create-merge, .alter, .alter-merge, .enable'
StackTrace     :    at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Kusto.ScriptsCreateOrUpdate_Call(HttpRequestMessage request, Func`3 onOk, Func`3 onDefault, IEventListener eventListener, ISendAsync sender)
                    at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Kusto.ScriptsCreateOrUpdate_Call(HttpRequestMessage request, Func`3 onOk, Func`3 onDefault, IEventListener eventListener, ISendAsync sender)
                    at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Kusto.ScriptsCreateOrUpdate(String subscriptionId, String resourceGroupName, String clusterName, String databaseName, String scriptName, IScript body, Func`3 onOk, Func`3 onDefault, IEventListener eventListener, ISendAsync sender)
                    at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Cmdlets.NewAzKustoScript_CreateExpanded.ProcessRecordAsync()
Exception      : Microsoft.Azure.PowerShell.Cmdlets.Kusto.Runtime.UndeclaredResponseException
InvocationInfo : {New-AzKustoScript_CreateExpanded}
Line           :   $result = New-AzKustoScript `

Position       : At C:\VSTS\ResourceAudit\scripts\SetupKustoSqlAuditIngestion.ps1:259 char:3
                 +   $result = New-AzKustoScript `
                 +   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HistoryId      : 2

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @ilayrn, @orhasban, @zoharHenMicrosoft, @sagivf, @Aviv-Yaniv.

Issue Details

---

### Description When executing the following command: ``` New-AzKustoScript ` -ClusterName $clusterInfo["ClusterName"] ` -ResourceGroupName $clusterInfo["ClusterResourceGroup"] ` -DatabaseName $KUSTO_AUDIT_DATABASE_NAME ` -Name "_custom_name" ` -SubscriptionId (Get-AzContext).Subscription.Id ` -ScriptContent ".show tables" ``` the response is: [BadRequest] The provided script contains unsupported command. The commands must start with the following verbs: '.create, .create-or-alter, .create-merge, .alter, .alter-merge, .enable' The documentation does not specify a list of supported/unsupported commands. Please advise which cmdlet can be used to execute other commands (like .show xxx) ### Script or Debug output _No response_ ### Environment data ```PowerShell Name Value ---- ----- PSVersion 7.3.1 PSEdition Core GitCommitId 7.3.1 OS Microsoft Windows 10.0.19044 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0 ``` ### Module versions ```PowerShell ModuleType Version PreRelease Name ExportedCommands ---------- ------- ---------- ---- ---------------- Script 2.10.4 Az.Accounts {Add-AzEnvironment, Clear-AzConfig, Clear-AzContext, Clear-AzDefault…} Script 2.1.0 Az.Kusto {Add-AzKustoClusterLanguageExtension, Add-AzKustoDatabasePrincipal, Get-AzKustoAttachedDatabaseConfiguration, Get… ``` ### Error output ```PowerShell PS C:\VSTS\ResourceAudit> REsolve-AzError Upcoming breaking changes in the cmdlet 'Resolve-AzError' : The `Resolve-Error` alias will be removed in a future release. Please change any scripts that use this alias to use `Resolve-AzError` instead. Note : Go to https://aka.ms/azps-changewarnings for steps to suppress this breaking change warning, and other information on breaking changes in Azure PowerShell. HistoryId: 2 Message : [BadRequest] The provided script contains unsupported command. The commands must start with the following verbs: '.create, .create-or-alter, .create-merge, .alter, .alter-merge, .enable' StackTrace : at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Kusto.ScriptsCreateOrUpdate_Call(HttpRequestMessage request, Func`3 onOk, Func`3 onDefault, IEventListener eventListener, ISendAsync sender) at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Kusto.ScriptsCreateOrUpdate_Call(HttpRequestMessage request, Func`3 onOk, Func`3 onDefault, IEventListener eventListener, ISendAsync sender) at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Kusto.ScriptsCreateOrUpdate(String subscriptionId, String resourceGroupName, String clusterName, String databaseName, String scriptName, IScript body, Func`3 onOk, Func`3 onDefault, IEventListener eventListener, ISendAsync sender) at Microsoft.Azure.PowerShell.Cmdlets.Kusto.Cmdlets.NewAzKustoScript_CreateExpanded.ProcessRecordAsync() Exception : Microsoft.Azure.PowerShell.Cmdlets.Kusto.Runtime.UndeclaredResponseException InvocationInfo : {New-AzKustoScript_CreateExpanded} Line : $result = New-AzKustoScript ` Position : At C:\VSTS\ResourceAudit\scripts\SetupKustoSqlAuditIngestion.ps1:259 char:3 + $result = New-AzKustoScript ` + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ HistoryId : 2 ```

Author:	eeertel
Assignees:	-
Labels:	`Service Attention`, `question`, `customer-reported`, `Azure Data Explorer`
Milestone:	-

[dingmeng-xue]

Involve Kusto team. It seems a feature request according to service return.

[Aviv-Yaniv]

Hi @eeertel and thanks for contacting! The .show command verb is excluded from the valid script supported verbs, and the error is correct. Here is a link to documentation on script resource with supported verbs list: Kusto Script Script resource is great to use for data-plane commands.

[Aviv-Yaniv]

Commented in the GitHub thread and linked docs. The show command is excluded by design.

[eeertel]

Hello @Aviv-Yaniv,

thank you for the response - understood. The problem that we encounter is that we want to use the .show xxx command (as recommended by the Kusto documentation - see the TIP section here) before changing or creating a table. As part of a DevOps pipeline we need to make sure that certain tables and functions exist and would prefer to let the pipeline fail if a table/function already exists with a wrong configuration. Since the .show xxx commands are not supported, we have to fallback on using .NET client libraries from Powershell as described here (a solution which is by far not so elegant). Once we are forced to use .NET client libraries (since not all commands are supported via cmdlets) it does not make much sense to use the native cmdlets in the same script since it would result in a mix-up which would be difficult to maintain. Is there a road map to support all commands via "native" Powershell cmdlets in the future, or would you advise us to keep using .NET client libraries ? Thank you in advance, bye //emil

[Aviv-Yaniv]

Hi @eeertel

There is no plan to support show verb in script, because one cannot reuse it 's results for verification automatically (the command can be used for manual verification). Here are some advices that hope to be helpful: If certain table should created by script, and you'd like to make sure it created successfully, please leverage the continueOnErrors with false value. One can use create-or-alter to overcome existing items.

[eeertel]

Hello @Aviv-Yaniv,

create-or-alter function(s) are not available for tables (see here).

This means, that there is no way to make sure that the .create table command does what is expected.

Take the following example:

1. Create a table

   .create table test_table(value: int)

2. if somebody now changes the table with this command

   .alter table test_table(xvalue: string)

3. the following cmdlet will return a ProvisioningState of "Succeeded" although the table is NOT changed (it still has one field named xvalue of type string)

   $result = New-AzKustoScript `
   -ClusterName $clusterInfo["ClusterName"] `
   -ResourceGroupName $clusterInfo["ClusterResourceGroup"] `
   -DatabaseName $KUSTO_AUDIT_DATABASE_NAME `
   -Name "_custom_name" `
   -SubscriptionId (Get-AzContext).Subscription.Id `
   -ScriptContent ".create table test_table(value: int)" 

   Are you aware of any solution for step 3 to make sure that the table is created in the desired way ?

tnx & bye //emil

[Aviv-Yaniv]

Hi @eeertel

The matching for create-or-alter for tables is alter-merge

Running 3 doesn't intended to remove columns, so it shouldn't fail

I advise to reduce authorizations of tables altering to the minimum required If no guarantee can be made on changes to tables schemas, is favorable to add a monitoring mechanism