0

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
Slavik N
  • 4,705
  • 17
  • 23
Neha
  • 87
  • 1
  • 2
  • 7

1 Answers1

1

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).

Slavik N
  • 4,705
  • 17
  • 23
  • Here we need to paste JSON Array every time in the query? Cant we do it in automatic way, mentioning ADFPipelinename and ADFActivityName then parse_json(Output).Total_Record_Count. something like this.. – Neha Mar 02 '21 at 16:19
  • No, you don't need to paste the json. I just put it here as an example. The actual query that you need is the last 4 lines of what I wrote. I'll update the answer by deleting the sample json, in order not to confuse the others. – Slavik N Mar 02 '21 at 20:28
  • I dont have table here.. data is in JSON array format.. I searched and everywhere solution is given: pasting json code and using mv-expand in query.. – Neha Mar 03 '21 at 09:55
  • You wrote "data is in JSON array format" - but where does this data reside? If it's in a table, then you'll indeed need mv-expand, and then use the solution I provided. – Slavik N Mar 03 '21 at 14:48
  • I am fetching data from Azure monitor, In Log analytics using Log management functions like : ADFPipeline, Pipelinename, ADFActivity, ActivittyName, etc. – Neha Mar 04 '21 at 05:24