Skip to content

Latest commit

 

History

History
295 lines (218 loc) · 10.8 KB

howto-query-with-rest-api.md

File metadata and controls

295 lines (218 loc) · 10.8 KB
Raw
title description titleSuffix author ms.author ms.date ms.topic ms.service services
Use the REST API to query devices in Azure IoT Central
How to use the IoT Central REST API to query devices in an application, including filters, aggregation, and sorting.
Azure IoT Central
dominicbetts
dobett
10/14/2024
how-to
azure-iot-central
iot-central

How to use the IoT Central REST API to query devices

The IoT Central REST API lets you develop client applications that integrate with IoT Central applications. You can use the REST API to query devices in your IoT Central application. The following are examples of how you can use the query REST API:

  • Get the last 10 telemetry values reported by a device.
  • Find all devices that are in an error state and have outdated firmware.
  • Analyze telemetry trends from devices, averaged in 10-minute windows.
  • Get the current firmware version of all your thermostat devices.

This article describes how to use the /query API to query devices.

A device can group the properties, telemetry, and commands it supports into components and modules.

Every IoT Central REST API call requires an authorization header. To learn more, see How to authenticate and authorize IoT Central REST API calls.

For the reference documentation for the IoT Central REST API, see Azure IoT Central REST API reference.

To learn how to query devices by using the IoT Central UI, see How to use data explorer to analyze device data.

Run a query

Use the following request to run a query:

POST https://{your app subdomain}.azureiotcentral.com/api/query?api-version=2022-10-31-preview

The query is in the request body and looks like the following example:

{
  "query": "SELECT $id, $ts, temperature, humidity FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o WHERE WITHIN_WINDOW(P1D)"
}

The dtmi:eclipsethreadx:devkit:hlby5jgib2o value in the FROM clause is a device template ID. To find a device template ID, navigate to the Devices page in your IoT Central application and hover over a device that uses the template. The card includes the device template ID:

:::image type="content" source="media/howto-query-with-rest-api/show-device-template-id.png" alt-text="Screenshot that shows how to find the device template ID in the page URL.":::

The response includes telemetry from multiple devices that share the same device template. The response to this request looks like the following example:

{
  "results": [
    {
      "$id": "sample-003",
      "$ts": "2021-09-10T12:59:52.015Z",
      "temperature": 47.632160152311016,
      "humidity": 49.726422005390816
    },
    {
      "$id": "sample-001",
      "$ts": "2021-09-10T13:01:34.286Z",
      "temperature": 58.898120617808495,
      "humidity": 44.66125772328022
    },
    {
      "$id": "sample-001",
      "$ts": "2021-09-10T13:04:04.96Z",
      "temperature": 52.79601469228174,
      "humidity": 71.5067230188416
    },
    {
      "$id": "sample-002",
      "$ts": "2021-09-10T13:04:36.877Z",
      "temperature": 49.610062789623264,
      "humidity": 52.78538601804491
    }
  ]
}

Syntax

The query syntax is similar to SQL syntax and is made up of the following clauses:

  • SELECT is required and defines the data you want to retrieve, such as the device telemetry values.
  • FROM is required and identifies the device type you're querying. This clause specifies the device template ID.
  • WHERE is optional and lets you filter the results.
  • ORDER BY is optional and lets you sort the results.
  • GROUP BY is optional and lets you aggregate results.

The following sections describe these clauses in more detail.

SELECT clause

The SELECT clause lists the data values to include in the query output and can include the following items:

  • Telemetry. Use the telemetry names from the device template.
  • $id. The device ID.
  • $provisioned. A boolean value that shows if the device is provisioned yet.
  • $simulated. A boolean value that shows if the device is a simulated device.
  • $ts. The timestamp associated with a telemetry value.

If your device template uses components, then you reference telemetry defined in the component as follows:

{
  "query": "SELECT ComponentName.TelemetryName FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o"
}

You can find the component name in the device template:

:::image type="content" source="media/howto-query-with-rest-api/show-component-name.png" alt-text="Screenshot that shows how to find the component name." lightbox="media/howto-query-with-rest-api/show-component-name.png":::

The following limits apply in the SELECT clause:

  • No wildcard operator is available.
  • You can't have more than 15 items in the select list.
  • A query returns a maximum of 10,000 records.

Aliases

Use the AS keyword to define an alias for an item in the SELECT clause. The alias is used in the query output. You can also use it elsewhere in the query. For example:

{
  "query": "SELECT $id as ID, $ts as timestamp, temperature as t, pressure as p FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o WHERE WITHIN_WINDOW(P1D) AND t > 0 AND p > 50"
}

Tip

You can't use another item in the select list as an alias. For example, the following isn't allowed SELECT id, temp AS id....

The result looks like the following output:

{
  "results": [
    {
      "ID": "sample-002",
      "timestamp": "2021-09-10T11:40:29.188Z",
      "t": 40.20355053736378,
      "p": 79.26806508746755
    },
    {
      "ID": "sample-001",
      "timestamp": "2021-09-10T11:43:42.61Z",
      "t": 68.03536237975348,
      "p": 58.33517075380311
    }
  ]
}

TOP

Use the TOP to limit the number of results the query returns. For example, the following query returns the first 10 results:

{
    "query": "SELECT TOP 10 $id as ID, $ts as timestamp, temperature, humidity FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o"
}

If you don't use TOP, the query returns a maximum of 10,000 results.

To sort the results before TOP limits the number of results, use ORDER BY.

FROM clause

The FROM clause must contain a device template ID. The FROM clause specifies the type of device you're querying.

To find a device template ID, navigate to the Devices page in your IoT Central application and hover over a device that uses the template. The card includes the device template ID:

:::image type="content" source="media/howto-query-with-rest-api/show-device-template-id.png" alt-text="Screenshot that shows how to find the device template ID on the devices page." lightbox="media/howto-query-with-rest-api/show-device-template-id.png":::

You can also use the Devices - Get REST API call to get the device template ID for a device.

WHERE clause

The WHERE clause lets you use values and time windows to filter the results:

Time windows

To get telemetry received by your application within a specified time window, use WITHIN_WINDOW as part of the WHERE clause. For example, to retrieve temperature and humidity telemetry for the last day use the following query:

{
  "query": "SELECT $id, $ts, temperature, humidity FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o WHERE WITHIN_WINDOW(P1D)"
}

The time window value uses the ISO 8601 durations format. The following table includes some examples:

Example Description
PT10M Past 10 minutes
P1D Past day
P2DT12H Past 2 days and 12 hours
P1W Past week
PT5H Past five hours
'2021-06-13T13:00:00Z/2021-06-13T15:30:00Z' Specific time range

Value comparisons

You can get telemetry based on specific values. For example, the following query returns all messages where the temperature is greater than zero, the pressure is greater than 50, and the device ID is one of sample-002 and sample-003:

{
  "query": "SELECT $id, $ts, temperature AS t, pressure AS p FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o WHERE WITHIN_WINDOW(P1D) AND t > 0 AND p > 50 AND $id IN ['sample-002', 'sample-003']"
}

The following operators are supported:

  • Logical operators AND and OR.
  • Comparison operators =, !=, >, <, >=, <=, <>, and IN.

Note

The IN operator only works with telemetry and $id.

The following limits apply in the WHERE clause:

  • You can use a maximum of 10 operators in a single query.
  • In a query, the WHERE clause can only contain telemetry and device metadata filters.
  • In a query, you can retrieve up to 10,000 records.

Aggregations and GROUP BY clause

Aggregation functions let you calculate values such as average, maximum, and minimum on telemetry data within a time window. For example, the following query calculates average temperature and humidity from device sample-001 in 10-minute windows:

{
  "query": "SELECT AVG(temperature), AVG(pressure) FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o WHERE WITHIN_WINDOW(P1D) AND $id='{{DEVICE_ID}}' GROUP BY WINDOW(PT10M)"
}

The results look like the following output:

{
    "results": [
        {
            "$ts": "2021-09-14T11:40:00Z",
            "avg_temperature": 49.212146114456104,
            "avg_pressure": 48.590304135023764
        },
        {
            "$ts": "2021-09-14T11:30:00Z",
            "avg_temperature": 52.44844454703927,
            "avg_pressure": 52.25973211022142
        },
        {
            "$ts": "2021-09-14T11:20:00Z",
            "avg_temperature": 50.14626272506926,
            "avg_pressure": 48.98400386898757
        }
    ]
}

The following aggregation functions are supported: SUM, MAX, MIN, COUNT, AVG, FIRST, and LAST.

Use GROUP BY WINDOW to specify the window size. If you don't use GROUP BY WINDOW, the query aggregates the telemetry over the last 30 days.

Note

You can only aggregate telemetry values.

ORDER BY clause

The ORDER BY clause lets you sort the query results by a telemetry value, the timestamp, or the device ID. You can sort in ascending or descending order. For example, the following query returns the most recent results first:

{
  "query": "SELECT $id as ID, $ts as timestamp, temperature, humidity FROM dtmi:eclipsethreadx:devkit:hlby5jgib2o ORDER BY timestamp DESC"
}

Tip

Combine ORDER BY with TOP to limit the number of results the query returns after sorting.

Limits

The current limits for queries are:

  • No more than 15 items in the SELECT clause list.
  • No more than 10 logical operations in the WHERE clause.
  • The maximum length of a query string is 350 characters.
  • You can't use the wildcard (*) in the SELECT clause list.
  • Queries can retrieve up to 10,000 records.