Could anyone please spot easily what am I doing wrong here?
I'm using the query below:
SELECT
GetArrayElement(Requests.context.custom.dimensions, 0),
GetType( GetArrayElement(Requests.context.custom.dimensions, 0)),
GetRecordPropertyValue(GetArrayElement(Requests.context.custom.dimensions, 0), "Response-Body")
INTO
PowerBICreateScheduleDurations
FROM
AppInsightsIncomingRequests AS Requests
To parse this input file:
{ "request": [{ "id": "dff22190-ecc8-44d2-aa3f-453c3d533c4d", "name": "", "count": 1, "responseCode": 401, "success": false, "url": "", "urlData": { "base": "", "host": "", "hashTag": "", "protocol": "https" }, "durationMetric": { "value": 4786.0, "count": 1.0, "min": 4786.0, "max": 4786.0, "stdDev": 0.0, "sampledValue": 4786.0 } } ], "internal": { "data": { "id": "f0b0f800-ab16-11e8-89c8-ed6412963258", "documentVersion": "1.61" } }, "context": { "data": { "eventTime": "2018-08-28T23:05:54.8884157Z", "isSynthetic": false, "samplingRate": 100.0 }, "cloud": {}, "device": { "type": "PC", "roleName": "", "roleInstance": "", "screenResolution": {} }, "session": { "isFirst": false }, "operation": { "id": "dff22190-ecc8-44d2-aa3f-453c3d533c4d", "parentId": "dff22190-ecc8-44d2-aa3f-453c3d533c4d", "name": "" }, "location": { "clientip": "0.0.0.0", "continent": "North America", "country": "United States" }, "custom": { "dimensions": [{ "Response-Body": "response 0" }, { "Operation Name": "" }, { "ApimanagementRegion": "" }, { "ApimanagementServiceName": "" }, { "Cache": "None" }, { "API Name": "" }, { "HTTP Method": "GET" } ], "metrics": [{ "Response Size": { "count": 1.0, "max": 343.0, "min": 343.0, "sampledValue": 343.0, "stdDev": 0.0, "sum": 343.0, "value": 343.0 } }, { "Request Size": { "count": 1.0, "max": 0.0, "min": 0.0, "sampledValue": 0.0, "stdDev": 0.0, "sum": 0.0, "value": 0.0 } }, { "Client Time (in ms)": { "count": 1.0, "max": 0.0, "min": 0.0, "sampledValue": 0.0, "stdDev": 0.0, "sum": 0.0, "value": 0.0 } } ] } } }
The desired behavior is for the GetRecordPropertyValue... line to extract the "response 0" string, yet it returns null. The downloaded output is below:
[{
"getarrayelement": {
"Response-Body": "response 0"
},
"gettype": "record",
"getrecordpropertyvalue": null
}
]
Please change you query to use single quotes around 'Response-Body'. In SQL double quotes are used to escape column names (similar to []) so you query is interpreted as column with name "Response-Body" instead of string constant "Response-Body"
SELECT GetArrayElement(Requests.context.custom.dimensions, 0), GetType( GetArrayElement(Requests.context.custom.dimensions, 0)), GetRecordPropertyValue(GetArrayElement(Requests.context.custom.dimensions, 0), 'Response-Body') INTO PowerBICreateScheduleDurations FROM AppInsightsIncomingRequests AS Requests