Microsoft.Azure.Kusto.Data SetParameter does not work[BUG]

[Frankwayne]

Describe the bug I cannot use the SetParameter function and pass in input variables from my users and run the expected kusto query

Expected behavior I expect to be able to pass in input variables from my user and get the kusto query results back

Actual behavior (include Exception or Stack Trace) I get an exception back from the .net SDK of the Microsoft.Auzre.Kusto.Data Dll

I can get the query to return result if I hard code in values.

InnerException = {"{\"error\":{\"code\":\"FailedToQueryDraft\",\"message\":\"failed to query Draft resource name:{removed}, resource id: {removed}",\"@type\":\"adxproxy.Exceptions.FailedToQueryDraft\",\"@message\":\"failed to query D...

To Reproduce

1. Create a console app with the Microsoft.Azure.Justo.Data nuget package.
2. Attempt to set up a query with parameterized inputs

3. Run the query and get the exception.

   string query = @"declare query_parameters(userId:string, communityId: string, transactionType:string); requests | where name has transactionType | where customDimensions.communityid == communityId | where user_AuthenticatedId == userId | limit 200 ";

Environment: .Net Core 3.1 Blazer Server Microsoft.Azure.Kusto.Data 8.1.2

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @radennis.

[ghost]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @radennis.

[Frankwayne]

I would very much like to use the Kusto queries in my client applications, but I need the protection of query parameterization. If there is another dll that does that for me I'll be glad to switch.

[radennis]

Can you please explain what is declare query_parameters?

Kusto supports functions which get parameters, would that answer your needs?

[Frankwayne]

Hey radennis that is my kusto query, I'm using. Here is more of the code I'm trying to use

userId communityId transactionType

are all input variables with a dataType of String

//// connection information removed.

  var kustoConnectionStringBuilder = new KustoConnectionStringBuilder(serviceUri, "{removed}")
                .WithAadApplicationKeyAuthentication(applicationClientId, applicationKey, authority);

            IEnumerable<AppInsightsData> results = Enumerable.Empty<AppInsightsData>();

            var client = KustoClientFactory.CreateCslQueryProvider(kustoConnectionStringBuilder);

            // It is strongly recommended that each request has its own unique
            // request identifier. This is mandatory for some scenarios (such as cancelling queries)
            // and will make troubleshooting easier in others.

    string query = @"declare query_parameters(userId:string,
communityId: string,
transactionType:string);
requests
| where name has transactionType
| where customDimensions.communityid == communityId
| where user_AuthenticatedId == userId
| limit 200
";

            var queryParameters = new Dictionary<string, string>()
            {
                {"userId", userId},
                {"communityId", communityId },
                { "transactionType", "transactionType" }
            };

            //var clientRequestProperties = new ClientRequestProperties(options:null,
            // parameters: queryParameters);

            var clientRequestProperties = new ClientRequestProperties();
            clientRequestProperties.ClientRequestId = Guid.NewGuid().ToString();
           clientRequestProperties.SetParameter("userid", userId);
           clientRequestProperties.SetParameter("communityId", communityId);
           clientRequestProperties.SetParameter("transactionType", transactionType);

            using (var reader = client.ExecuteQuery(query, clientRequestProperties))
            {

                results = Dapper.SqlMapper.Parse<AppInsightsData>(reader).ToArray();
            }

            return results;

[Frankwayne]

please let me know if you need any more details.

[Frankwayne]

any hope on this issue, or will I need to look at other methods to solve my issue ?

[radennis]

@pdrayton @alongafni can you guys help?

[divyajay]

@Frankwayne can you provide the ActivityId for your query, so I can see what happened in our logs? Unfortunately, a clusterUri of the format 'https://ade.applicationinsights.io/subscriptions/....' is not supproted in the KustoConnectionStringBuilder class, I'd have expected it to fail much earlier. I am actually surprised this is the error you see

[Frankwayne]

@divyajay I'm following the instructions here to turn on the ADX preview

how to set up preview for ADX and app insights https://docs.microsoft.com/en-us/azure/data-explorer/query-monitor-data

once that is set up you can being to use your dll to query to the service.

My current activityId is "003189b0-b09f-4bf0-aea1-a999fa8379d1" I have change some things about my query such as parameters, so you may see different names on the inputs. I can see my parameters on the clientRequestProperties.

[divyajay]

Looks like the request did get sent along fine, but the Application Insights Query API does not support query parameters - so you ended up with a Semantic error because of that. There's no way to do this from an external client today. Kql Magic for Jupyter gives you a way to do parameterize your queries - https://mybinder.org/v2/gh/Microsoft/jupyter-Kqlmagic/master?filepath=notebooks%2FParametrizeYourQuery.ipynb, and so do Azure Monitor Workbooks.

[Frankwayne]

Perhaps I'm forming the documentation wrong. This page details the possibility to query parameters

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/api/netfx/request-properties

"Providing values for query parameterization as request properties Kusto queries can refer to query parameters by using a specialized declare query-parameters statement in the query text. This statement lets client applications parameterize Kusto queries based on user input, in a secure manner, and without fear of injection attacks.

Programmatically, set properties values by using the ClearParameter, SetParameter, and HasParameter methods.

In the REST API, query parameters appear in the same JSON-encoded string as the other request properties.

Sample client code for using request properties"

The page even has an example, The only thing it doesn't show is how to form the query itself.

[Frankwayne]

perhaps it has to be formed in this way. I know the first bit of the second line of code looks like python, but the query itself may give me some clues. I'm going to give it a shot

p_dict = {'p_limit':5,'p_not_state':'OHIO'}

%%kql Samples@help -displayid='True' -params_dict p_dict let limit = p_limit; let _notval = p_not_state; StormEvents | where State != _notval | summarize count() by State | sort by count_ | limit limit

[divyajay]

You are using a Proxy to query the Application Insights API using Kusto SDK. The Proxy does not add capabilities to the query experience, and today these are the only request properties the Application Insights API supports - https://dev.applicationinsights.io/documentation/Using-the-API/RequestOptions

[Frankwayne]

sounds like I will be stuck regex the input to my queries then. Thanks for your help and clarifying the issue at hand.