How to parse nested JSON, within a string, using Kusto

Question

I have a Python Azure Function that produces custom logging messages when the Function executes. I need to pull out some of the JSON values nested in the logging message.

How can I use Kusto to access the nested JSON within the logging message string?

Example logging message:

Desired values marked with <----------

####### EventGrid trigger processing an event: 
    {
        "id": "long-guid", 
        "data": {
            "api": "FlushWithClose", 
            "requestId": "long-guid", 
            "eTag": "long-guid", 
            "contentType": "application/octet-stream", 
            "contentLength": 16264, <----------------------
            "contentOffset": 0, 
            "blobType": "BlockBlob", 
            "blobUrl": "https://function.blob.core.windows.net/parentdir/childdir/file.name", 
            "url": "https://function.dfs.core.windows.net/parentdir/childdir/file.name", <---- JUST FILE.NAME here
            "sequencer": "long-guid", 
            "identity": "long-guid", 
            "storageDiagnostics": {
                "batchId": "long-guid"
            }
    }, 
    "topic": "/subscriptions/long-guid/resourceGroups/resourceGroup/providers/Microsoft.Storage/storageAccounts/accountName", 
    "subject": "/blobServices/default/containers/containerName/blobs/childDir/file.name",
    "event_type": "Microsoft.Storage.BlobCreated"
} #######

I imagine it has something to do with the Kusto extend function, but piping in...

| extend parsedMessage = todynamic(message)
| project timestamp, test = parsedMessage["id"]

...yields only an empty test column

Yoni L. · Accepted Answer · 2021-04-16 01:45:31Z

2

message in your specific case isn't a valid JSON payload - as it has the ###... EventGrid trigger processing an event: prefix (and a somewhat similar suffix).

That is why todynamic() isn't able to process it and why you're not able to reference properties in the JSON payload that's included in it.

Ideally, you would change the payload you ingest to be a valid JSON payload, and re-type the target column to dynamic instead of string.

If you can't do that, you can use the substring() function or parse operator to get everything but the aforementioned prefix/suffix, and parse the output of that using todynamic()

though note that doing that each time you query the data bears runtime overhead that could be avoided by following the advice above.

answered Apr 16, 2021 at 1:45

Yoni L.

26.4k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ericOnline Over a year ago

Thank you! Figured this out using trim() and substring(): | extend parsedMessage = todynamic(trim(@"#######",substring(message, 46)))

ericOnline Over a year ago

Once the extended function was applied, I used

| project operation_Id, timestamp, eventId = parsedMessage["id"], sourceContainer = split(parsedMessage["data"]["blobUrl"], "/")[3], fileName = split(parsedMessage["data"]["blobUrl"], "/")[6], contentLength = parsedMessage["data"]["contentLength"]

to access various pieces of the nested JSON shown in OP. Hope this helps others.

Collectives™ on Stack Overflow

How to parse nested JSON, within a string, using Kusto

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related