Writing Kusto queries through code, looking for JSON format conversion to Kusto queries with limited functionality

Question

We are moving the alert triggering conditions to our codebase (C++). Currently, these conditions are written as KQL queries on the Azure portal. We are creating logic that can convert JSON format values to KQL. The generated KQL query will be simple and use limited functionality of KQL. It will then use Azure APIs to create an alert on the Azure portal. Does anyone have experience with this and can provide suggestions on how they have resolved this problem? is Azure API is available which can convert JSON to kusto query?

Answer 1

@Saransh Gaur - It sounds like you are looking for a way to convert JSON format values to Kusto Query Language (KQL) queries with limited functionality. While there is no Azure API available to directly convert JSON to KQL queries, you can write your own code to perform this conversion.

One approach could be to define a mapping between the JSON format and the corresponding KQL query. For example, you could define a set of rules that map JSON fields to KQL functions and operators. Then, you can use these rules to generate the KQL query from the JSON input.

Here is an example of how you could convert a JSON input to a KQL query using this approach:

{
  "table": "MyCustomTable",
  "filter": {
    "column": "Status",
    "operator": "==",
    "value": "Error"
  },
  "projection": [
    "Timestamp",
    "Message"
  ]
}

MyCustomTable
| where Status == "Error"
| project Timestamp, Message

In this example, the JSON input specifies a table name, a filter condition, and a projection list. The corresponding KQL query is generated by mapping the JSON fields to KQL functions and operators.

Keep in mind that this approach has limitations and may not cover all possible scenarios. It is important to thoroughly test your code and ensure that it generates valid KQL queries.

Once you have generated the KQL query, you can use the Azure Monitor REST API to create an alert based on the query. The API allows you to create and manage alerts programmatically, so you can automate the alert creation process from your codebase.

I hope this helps! Let me know if you have any further questions.