How to i expand JSON data in kusto/data explorer that has multiple layers?

Question

I am trying to ingest JSON array data (specifically the 'Objects' array) into Azure data explorer, as per this Microsoft article. (Only the JSON Array section)

https://docs.microsoft.com/en-us/azure/data-explorer/ingest-json-formats?tabs=kusto-query-language&source=docs#ingest-json-records-containing-arrays

My JSON data is different to the example, as it has an additional layer in the JSON, when expanding the raw event row to the second table, the row entered is blank. I assume the function can't find 'Objects' using the kusto function?

.create function EventRecordsExpand() {
    rawhsievents
    | mv-expand Objects = Event
    | project
        AlarmState = tostring(Objects["AlarmState"]),
        AreaOfInterest = tostring(Objects["AreaOfInterest"]),
        Category = tostring(Objects["Category"]),
        EncodedMessage = tostring(Objects["EncodedMessage"]),
        Fullname = tostring(Objects["Fullname"]),
        Id = tolong(Objects["Id"]),
        Message = tostring(Objects["Message"]),
        ReceiptTime = todatetime(Objects["ReceiptTime"]),
        RecordTime = todatetime(Objects["RecordTime"]),
        Severity = tostring(Objects["Severity"]),
        User = tostring(Objects["User"])
}

An example of my JSON data is below:

{
    "ExportedEvents": {
        "Header": {
            "SystemName": "Mids",
            "StartDate": "2020-11-03T12:28:00.55Z",
            "EndDate": "2020-11-03T12:28:11.521Z"
        },
        "Objects": [{
                "AlarmState": "",
                "AreaOfInterest": "",
                "Category": "Action",
                "EncodedMessage": "Kernel,469,M(Lib,101,S\"RequestExportXML\")",
                "Fullname": "System Resources.XML Interface.Support Processes.Batch Scheduler.Batch Schedule Scanner",
                "Id": 456020,
                "Message": "RequestExportXML request rejected - Invalid configuration",
                "ReceiptTime": "2020-11-03T12:28:00.55Z",
                "RecordTime": "2020-11-03T12:28:00.55Z",
                "Severity": "Low",
                "User": "Schedule"
            },
            {
                "AlarmState": "",
                "AreaOfInterest": "",
                "Category": "Action",
                "EncodedMessage": "Kernel,469,M(Lib,101,S\"RequestExportXML\")",
                "Fullname": "System Resources.XML Interface.Support Processes.Batch Scheduler.Batch Schedule Scanner",
                "Id": 456020,
                "Message": "RequestExportXML request rejected - Invalid configuration",
                "ReceiptTime": "2020-11-03T12:28:00.551Z",
                "RecordTime": "2020-11-03T12:28:00.551Z",
                "Severity": "Low",
                "User": "Schedule"
            }
        ]
    }
}

Do i need a second mv-expand to expand the data twice?

Yoni L. Yoni L. · Accepted Answer · 2021-01-15T21:00:26

it seems like you're mv-expanding the wrong dynamic object, and you need to access ExportedEvents.Objects first.

for example:

datatable(Event:dynamic)
[
    dynamic({
        "ExportedEvents": {
            "Header": {
                "SystemName": "Mids",
                "StartDate": "2020-11-03T12:28:00.55Z",
                "EndDate": "2020-11-03T12:28:11.521Z"
            },
            "Objects": [{
                    "AlarmState": "",
                    "AreaOfInterest": "",
                    "Category": "Action",
                    "EncodedMessage": "Kernel,469,M(Lib,101,S\"RequestExportXML\")",
                    "Fullname": "System Resources.XML Interface.Support Processes.Batch Scheduler.Batch Schedule Scanner",
                    "Id": 456020,
                    "Message": "RequestExportXML request rejected - Invalid configuration",
                    "ReceiptTime": "2020-11-03T12:28:00.55Z",
                    "RecordTime": "2020-11-03T12:28:00.55Z",
                    "Severity": "Low",
                    "User": "Schedule"
                },
                {
                    "AlarmState": "",
                    "AreaOfInterest": "",
                    "Category": "Action",
                    "EncodedMessage": "Kernel,469,M(Lib,101,S\"RequestExportXML\")",
                    "Fullname": "System Resources.XML Interface.Support Processes.Batch Scheduler.Batch Schedule Scanner",
                    "Id": 456020,
                    "Message": "RequestExportXML request rejected - Invalid configuration",
                    "ReceiptTime": "2020-11-03T12:28:00.551Z",
                    "RecordTime": "2020-11-03T12:28:00.551Z",
                    "Severity": "Low",
                    "User": "Schedule"
                }
            ]
        }
    })
]
| mv-expand Objects = Event.ExportedEvents.Objects
| project
        AlarmState = tostring(Objects["AlarmState"]),
        AreaOfInterest = tostring(Objects["AreaOfInterest"]),
        Category = tostring(Objects["Category"]),
        EncodedMessage = tostring(Objects["EncodedMessage"]),
        Fullname = tostring(Objects["Fullname"]),
        Id = tolong(Objects["Id"]),
        Message = tostring(Objects["Message"]),
        ReceiptTime = todatetime(Objects["ReceiptTime"]),
        RecordTime = todatetime(Objects["RecordTime"]),
        Severity = tostring(Objects["Severity"]),
        User = tostring(Objects["User"])

returns:

| AlarmState | AreaOfInterest | Category | EncodedMessage                            | Fullname                                                                                | Id     | Message                                                   | ReceiptTime                 | RecordTime                  | Severity | User     |
|------------|----------------|----------|-------------------------------------------|-----------------------------------------------------------------------------------------|--------|-----------------------------------------------------------|-----------------------------|-----------------------------|----------|----------|
|            |                | Action   | Kernel,469,M(Lib,101,S"RequestExportXML") | System Resources.XML Interface.Support Processes.Batch Scheduler.Batch Schedule Scanner | 456020 | RequestExportXML request rejected - Invalid configuration | 2020-11-03 12:28:00.5500000 | 2020-11-03 12:28:00.5500000 | Low      | Schedule |
|            |                | Action   | Kernel,469,M(Lib,101,S"RequestExportXML") | System Resources.XML Interface.Support Processes.Batch Scheduler.Batch Schedule Scanner | 456020 | RequestExportXML request rejected - Invalid configuration | 2020-11-03 12:28:00.5510000 | 2020-11-03 12:28:00.5510000 | Low      | Schedule |

How to i expand JSON data in kusto/data explorer that has multiple layers?

1 Answers