Filtering azure events from EventHub with Stream Analytics job query

Question

So, I want to capture Administrative events sent by Azure to an EventHub with Stream Analytics Job and forward only the events which match an specific criteria to an Azure Function. The events come in an object like this (heavily trimmed to simplify):

{
  "records": [
    {
      "resourceId": "<resource_path>",
      "operationName": "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE",
    },
    {
      "time": "2021-03-19T19:19:56.0639872Z",
      "operationName": "MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE",
      "category": "Administrative",
      "resultType": "Accept",
      "resultSignature": "Accepted.Created",
      "properties": {
        "statusCode": "Created",
        "serviceRequestId": "<trimmed>",
        "eventCategory": "Administrative",
        "message": "Microsoft.Compute/virtualMachines/write",
        "hierarchy": "<trimmed>"
      },
      "tenantId": "<trimmed>"
    }
  ],
  "EventProcessedUtcTime": "2021-03-19T19:25:21.1471185Z",
  "PartitionId": 1,
  "EventEnqueuedUtcTime": "2021-03-19T19:20:43.9080000Z"
}

I want to filter the query based on these criteria: records[0].operationName = 'MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE' AND records[1].properties.statusCode = 'Created'. To achieve that, I began with the following query which returns this record, but it's lacking one of the criteria I NEED to match (statusCode)

SELECT
    records
INTO
    [output]
FROM
    [input]
WHERE
    GetArrayElement(records, 0).operationName = 'MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE'

Trying the query below doesn't work (it returns 0 matches):

SELECT
    records
INTO
    [output]
FROM
    [input]
WHERE
    GetArrayElement(records, 0).operationName = 'MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE'
    AND GetArrayElement(records, 1).properties.statusCode = 'OK'

Anyone has a clue on this?

Answer 1

Found out the solution! I need to use GetRecordPropertyValue, like so:

SELECT
    records
INTO
    [output]
FROM
    [input]
WHERE
    GetArrayElement(records, 0).operationName = 'MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE'
    AND GetRecordPropertyValue(GetArrayElement(records, 1).properties, 'statusCode') = 'Created'

Looks a bit clumsy to me, but it worked!