Azure Stream Analytics job exposes IoT Hub values

Whenever I build a new IoT solution in Azure, I have to build this service chain. I have to connect a string of services like IoT Hub, Stream Analytics Job, Event Hub and Azure Function to have a quick insight in the telemetry coming in.

Today I found out a little feature which saves me a lot of time and now lesser data must be put in the telemetry.

I was under the assumption that a Stream Analytics job just handled plain (json) messages from their input. Now I am able to pinpoint the device the telemetry is coming from, for free, using a nifty feature of the IoT Hub!

Until now, when telemetry was coming in like:

{
"water":14.0,
"light":290
}

I handled it in the Stream Analytics job query like:

SELECT
    CAST(water as float) as water,
    CAST(light as float) as lumen
INTO
    huboutput 
FROM
    hubinput

In an Azure Function with just the standard example code:

using System;
public static void Run(string myEventHubMessage, TraceWriter log)
{
 log.Info($"My First C# Event Hub trigger function processed a message: {myEventHubMessage}");
}

This will give:

2016-09-25T14:59:12.157 Function started (Id=8e617e92-6492-439a-8d2d-d324694a55a4)
2016-09-25T14:59:12.157 My First C# Event Hub trigger function processed a message: {"water":23.0,"lumen":801.0}
2016-09-25T14:59:12.157 Function completed (Success, Id=8e617e92-6492-439a-8d2d-d324694a55a4)

What is missing, is the device and the time of the message. The time is not an issue, Stream analytics could generate that (there are actually multiple times to choose from). But I have broken my head already over the DeviceId. So normally, I let the device name be part of  the telemetry.

Yesterday, I was doing som experiments and I was thinking: well, the Stream Analytics job uses an SQL-like language, what will happen if I select * as in ‘all fields’ ?

So I tried:

SELECT
    *
INTO
    huboutput 
FROM
    hubinput

The message logged by the Azure Function was overwhelming:

{
"water":13.0,
"light":265,
"EventProcessedUtcTime":"2016-09-25T14:51:41.4983949Z",
"PartitionId":0,
"EventEnqueuedUtcTime":"2016-09-25T14:51:41.6260000Z",
"IoTHub":
{
"MessageId":null,
"CorrelationId":null,
"ConnectionDeviceId":"DeviceOne",
"ConnectionDeviceGenerationId":"636104025011206963",
"EnqueuedTime":"0001-01-01T00:00:00.0000000",
"StreamId":null
}
}

And there is was, the DeviceId! Out trusty IoT Hub passes several fields of interesting Hub data along the telemetry.

So I ended up with the following Stream Analytics query:

SELECT
  CAST(water as float) as water,
  CAST(light as float) as lumen,
  EventProcessedUtcTime,
  IoTHub.ConnectionDeviceId
INTO
  huboutput
FROM
  hubinput

Notice the IoTHub.ConnectionDeviceId… Would this work?

Yes, it works! The final result in the Azure Function is now:

2016-09-25T15:42:38.409 Function started (Id=25cb63d4-d011-47ea-bec6-e2d7a3597a33)
2016-09-25T15:42:38.409 My First C# Event Hub trigger function processed a message: {"water":24.0,"lumen":668.0,"eventprocessedutctime":"2016-09-25T15:42:33.8572311Z","connectiondeviceid":"DeviceOne"}
2016-09-25T15:42:38.409 Function completed (Success, Id=25cb63d4-d011-47ea-bec6-e2d7a3597a33)

Now I have a DeviceId, Ho-Ho-Ho!

So I do not have to pass the DeviceId in my telemetry anymore. It always felt redundant. I hope you agree with that 😉

Advertentie