Persisting IoTHub Device Connection State using an Azure Function

Posted on Sunday, October 2, 2016

In this blogpost we're going to look at how we can persists the connection state of an IoTHub device into a SQL Database.
The connection state is also available in the IoTHub device registry, but quering the device registry each time an application needs the connection state, isn't optimal.
This is also the case because there's a limit on the number of queries you can do on the device registry.
By leveraging IoTHub Operations Monitoring and combining that with an Azure Function, it is extremly easy to persist the connection state in a SQL Database and query this database whenever an application needs to know a device connection state.

Operations Monitoring

To persist this connection state, we're going to use the Operations Monitoring ability of the IoTHub.
With Operations Monitoring, IotHub will log events for a number of categories to an EventHub compatible endpoint.
These categories are:

  • Device identity operations
  • Device telemetry
  • Cloud-to-device commands
  • Connections
  • File uploads

See this article for more information about the categories.

One of the categories this post will look into, is the Connections category.
When this category has been enabled, IoTHub will create "connect" and "disconnect" events as soon as the connection state for a device has changed.

Functions App

After enabling the Connections category, an Azure Function can be created to process these events.
Let's first create the Azure Function App.

When the Function App has been created, a new function can be added.
Select the EventHubTrigger - C# as template and then create a new EventHub connection.

The ConnectionString should be in this format: 'Endpoint=<Event Hub-compatible endpoint>;SharedAccessKeyName=iothubowner;SharedAccessKey=<AccesKey>'
The Event Hub-compatible endpoint can be found in the IoTHub Operations Monitoring settings.
The AccesKey for the IoTHubOwner can be found in the IoTHub Shared Access Policies.
After creating the new connection, the Event Hub name also needs to be filled in. This Event Hub-compatible name can also be found in the IoTHub Operations Monitoring settings.

To make life in the Function App easier, Json.NET will be used to deserialize the event the IoTHub created.
Before we can do this, the Function needs to know about this dependency. This can be done by including a project.json file.
First select "Show files" and then add the project.json file.

Paste these dependencies in the project.json file.

  "frameworks": {
      "dependencies": {
        "Newtonsoft.Json": "9.0.1",

After saving this file, the Function app will start restoring these NuGet dependencies.

Now that all dependencies are in place, the run.csx file can be updated with the following code:

using System;
using System.Configuration;
using System.Data.SqlClient;
using Newtonsoft.Json;

public static void Run(string myEventHubMessage, TraceWriter log)
        var connectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

        var message = JsonConvert.DeserializeObject<MonitoringMessage>(myEventHubMessage);

        using (var connection = new SqlConnection(connectionString))
            var sqlStatement = "UPDATE [dbo].[Devices] " +
                                "SET [IsConnected] = @IsConnected	" +
                                "WHERE [DeviceId] = @DeviceId";
            using (var dataCommand = new SqlCommand(sqlStatement, connection))
                dataCommand.Parameters.AddWithValue("IsConnected", message.operationName == "deviceConnect");
                dataCommand.Parameters.AddWithValue("DeviceId", message.deviceId);


                log.Info($"Device {message.deviceId} changed state: {message.operationName}");
    catch (Exception ex)

public class MonitoringMessage
    public string deviceId { get; set; }
    public string operationName { get; set; }
    public int? durationMs { get; set; }
    public string authType { get; set; }
    public string protocol { get; set; }
    public DateTimeOffset? time { get; set; }
    public string category { get; set; }
    public string level { get; set; }
    public int? statusCode { get; set; }
    public int? statusType { get; set; }
    public string statusDescription { get; set; }

This code is pretty self explanatory, it first deserialize the IoTHub event and then updates a SQL table and sets the IsConnected field for this device to true or false.

The DatabaseConnectionString can be configured in the app settings of this Functions App.

The SQL Database used in this post only contains 1 table and that table can be created using this statement:

CREATE TABLE [dbo].[Devices]
  [DeviceId] NVARCHAR(250),
  [IsConnected] BIT


After saving the run.csx file, the Function App can now be tested by simulating an IoTHub connection event.

Or, if you want, you can create an actual IoTHub device and connect and disconnect. In this post you can find a solution on GitHub

Disclaimer: Any views or opinions expressed on this blog are my own personal ones and do not represent my employer in any way.