CODEWITHSUNDEEP
azure-log-analyticskqlazure-dashboard
Neha
Neha

Reputation: 97

How to project JSON output( array form) into tabular form through kusto query

In my azure monitor log output, data is in array format, I want to use kusto query and fetch that array data in tabular format. Could you please help how to do this, without pasting JSON array manually in KQL.

JSON Output array :

{
    "count": 6,
    "value": [
        {
            "Total_Record_Count": 6,
            "date": "2021-02-01"
        },
        {
            "Total_Record_Count": 4,
            "date": "2020-11-02"
        },
        {
            "Total_Record_Count": 6,
            "date": "2020-10-01"
        },
        {
            "Total_Record_Count": 1,
            "date": "2020-09-01"
        },
        {
            "Total_Record_Count": 3,
            "date": "2020-08-03"
        },
        {
            "Total_Record_Count": 18,
            "date": "2020-07-01"
        }   
    ]
}

I want Kusto query output like :

Total_Record_Count        Date
6                         2021-03-01
4                         2021-02-01
6                         2021-01-01
1                         2020-12-01
3                         2020-11-01
18                        2020-10-01

Upvotes: 0

Views: 1154

Answers (1)

Slavik N
Slavik N

Reputation: 5298

Here you go:

Table
| project d.value
| mv-expand d_value
| project RecordCount = d_value['Total_Record_Count'], Date = d_value['date']

Explanation:

  1. First you have to extract the value by doing YourDynamicColumnName.value, or YourDynamicColumnName['value'] - these are the same.

  2. The next step is to use mv-expand in order to split the array into multiple records (see doc for more info).

  3. As a last step you'll need to extract the two values from every cell, again, by using the following syntax: ColumnName.Key or ColumnName['Key'] (the two are identical).

Upvotes: 1

Related Questions