Azure IoT Hub - How to get what's newer of desired vs reported property in device twins?

Question

I'm using .NET Core 3 to read from device twins in an Azure IoT hub. I want to get property X and that property is, as always, both stored in the desired and reported properties. I want to get the one that's newer. This information is written in the metadata.

My question is, is this possible via just the IoT Hub Query Language or do I have to fetch from both desired and reported and check this out myself?

Answer 1

The Azure IoT Hub Query Language supports only the subset of the SQL statements, so the following example (device1 and twin property color) shows a workaround for missing a CASE statement:

1. query string to get the desired property as the lastUpdated:

   querystring = $"SELECT devices.properties.desired.color FROM devices WHERE deviceId = 'device1' and devices.properties.desired.$metadata.color.$lastUpdated > devices.properties.reported.$metadata.color.$lastUpdated";
   

2. if the return value is empty, we have to make the second query to obtain a reported property such as:

    querystring = $"SELECT devices.properties.reported.color FROM devices WHERE deviceId = 'device1' and devices.properties.reported.$metadata.color.$lastUpdated > devices.properties.desired.$metadata.color.$lastUpdated";
   

3. if the return value is still empty, there are missing our desired and/or reported property in the device twin or the deviceId is wrong.

The following code snippet shows an example of the above usage:

using Microsoft.Azure.Devices;
using System.Linq;
using System;
using System.Threading.Tasks;

namespace ConsoleApp3
{
    class Program
    {
        static string connectionString = "*****";

        static async Task Main(string[] args)
        {
            RegistryManager registryManager = RegistryManager.CreateFromConnectionString(connectionString);

            string deviceId = "device1";
            string propertyName = "color";
            string querystring = $"SELECT devices.properties.desired.{propertyName} FROM devices WHERE deviceId = '{deviceId}' and devices.properties.desired.$metadata.{propertyName}.$lastUpdated > devices.properties.reported.$metadata.{propertyName}.$lastUpdated";

            dynamic prop = null;
            for (int ii = 0; ii < 2; ii++)
            {
                var query = registryManager.CreateQuery(querystring);
                {
                    prop = (await query.GetNextAsJsonAsync())?.FirstOrDefault();
                    if (prop == null)
                        querystring = $"SELECT devices.properties.reported.{propertyName} FROM devices WHERE deviceId = '{deviceId}' and devices.properties.reported.$metadata.{propertyName}.$lastUpdated > devices.properties.desired.$metadata.{propertyName}.$lastUpdated";
                    else
                        break;
                }
            }
            Console.WriteLine(prop ?? $"Not found property '{propertyName}' or device '{deviceId}'");
        }
    }
}

UPDATE:

In the case of multiple properties, we have to check each property individually by code in the fetched device twin entity. The following code snippet shows an example of this checking:

// multiple properties
querystring = $"SELECT devices.properties FROM devices WHERE deviceId='{deviceId}'";
var query2 = registryManager.CreateQuery(querystring);
JObject prop2 = JObject.Parse((await query2.GetNextAsJsonAsync())?.FirstOrDefault());

JToken desired = prop2.SelectToken("properties.desired");
JToken reported = prop2.SelectToken("properties.reported");

string pathLastUpdated = $"$metadata.{propertyName}.$lastUpdated";          

var color = (DateTime)desired.SelectToken(pathLastUpdated) > (DateTime)reported.SelectToken(pathLastUpdated) ?
            (string)desired[propertyName] : (string)reported[propertyName];

// more properties

Console.WriteLine(color);

also, you can create an extension class to simplify the code, see the following example:

public static class JObjectExtensions
{
    public static T GetLastUpdated<T>(this JObject properties, string propertyName)
    {
        JToken desired = properties.SelectToken("properties.desired");
        JToken reported = properties.SelectToken("properties.reported");
        string pathLastUpdated = $"$metadata.{propertyName}.$lastUpdated";

        return (DateTime)desired.SelectToken(pathLastUpdated) > (DateTime)reported.SelectToken(pathLastUpdated) ?
            desired.SelectToken(propertyName).ToObject<T>() : reported.SelectToken(propertyName).ToObject<T>();
    }

    public static string GetLastUpdated(this JObject properties, string propertyName)
    {
        return GetLastUpdated<string>(properties, propertyName);
    }
}

the following usage of the above extension shows how can be obtained any desired vs reported properties based on their lastUpdated timestamp:

color = prop2.GetLastUpdated(propertyName);

string color2 = prop2.GetLastUpdated("test.color");

var test = prop2.GetLastUpdated<JObject>("test");

string jsontext = prop2.GetLastUpdated<JObject>("test").ToString(Formatting.None);

var test2 = prop2.GetLastUpdated<Test>("test");

int counter = prop2.GetLastUpdated<int>("counter");

Note, that the exception is thrown in the case of property missing.