Search code examples
jsonazureloggingazure-application-insightskql

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

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

enter image description here

Solution

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