Azure Stream Analytics job Query IoTHub.ConnectionDeviceId null

Problem

Having connected Azure Stream Analytics job to IoT Hub, and trying to get hold of IoTHub.ConnectionDeviceId in the Stream Analytics job Query, you just get NULL value. Several instructions on the net just say that query such as below should return the device id, but no.

SELECT IoTHub.ConnectionDeviceId AS DeviceId
INTO SQL
FROM IoTHub TIMESTAMP BY EntryTime

Posts such as this almost made me open a support ticket as I was suspecting some platform related issue.

Solution

This one was rather trivial once I used SELECT * to find out what is really coming from the IoTHub, so changing the query to

SELECT IoTHub.IoTHub.ConnectionDeviceId

will do the trick and you can get hold of the Device ID without explicitly passing it from the IoT device to IoT Hub.

And further searching got me to this article that has clear example with query such as in my example.