If you need to search for and download a device list, including ProductKey and DeviceName, based on a specified condition in IoT Platform, you can use the advanced search feature. This feature allows you to use SQL-like statements to search for devices, such as online devices. This article describes how to perform an advanced search and the SQL-like syntax.
Limits
- This feature is available in the China (Shanghai) and Japan (Tokyo) regions.
- Limits on features: Advanced search is not supported on the page of the public instance.
Scenarios
Advanced search is supported in the following scenarios in the IoT Platform console:
- Manage devices: On the Advanced Search tab of the page, search for specified devices.
You can also call the QueryDeviceBySQL operation to perform an advanced search. API operation-based advanced search is not limited to the preceding scenarios.
Procedure
This article describes how to use the advanced search feature to add devices to a device group and search for devices based on TSL models.
- Log on to the IoT Platform console.
- On the Overview page, select an environment, find the instance that you want to manage, and click the instance ID or instance name. Important This step is required only if Enterprise Edition instances are available. If the Enterprise Edition instances are unavailable in the region that you selected, skip this step. For information about supported regions and instances, see Overview.
- In the left-side navigation pane, choose . Click the Advanced Search tab.
- On the Advanced Search tab, use the advanced search feature to search for devices.
SQL-like syntax
A SQL-like statement consists of the WHERE clause and the ORDER BY clause (optional). The SELECT clause, the LIMIT clause, and the WHERE keyword of the WHERE clause are omitted. Each statement cannot exceed 400 characters in length.
Example:
product_key = "a1*****" order by active_timeWHERE clause
Syntax:
[condition1] AND [condition2] The WHERE keyword is omitted.
You can specify a maximum of five conditions. Nesting is unsupported. For more information, see the search fields and operators in the following tables.
You can use the AND and OR logical operators. A maximum of five operators can be used.
ORDER BY clause (optional)
The ORDER BY clause is used for sorting. The fields that can be sorted include gmt_create, gmt_modified, and active_time.
This clause can be left unspecified. In this case, the results are randomly sorted.
Search fields
| Field | Type | Description |
|---|---|---|
| product_key | text | The ProductKey of the product to which the device belongs. |
| iot_id | text | The ID of the device. By default, iot_id is returned. |
| name | text | The DeviceName of the device. |
| active_time | date | The time when the device was activated. The time uses the yyyy-MM-dd HH:mm:ss.SSS format, accurate to milliseconds. |
| nickname | text | The alias of the device. |
| gmt_create | date | The time when the device was created. The time uses the yyyy-MM-dd HH:mm:ss.SSS format, accurate to milliseconds. |
| gmt_modified | date | The time when the device information was last updated. The time uses the yyyy-MM-dd HH:mm:ss.SSS format, accurate to milliseconds. |
| status | text | The status of the device. Valid values:
|
| group.group_id | text | The ID of the device group. |
| tag.tag_name | text | The tag key of the device. |
| tag.tag_value | text | The value of the device tag. |
| ota_module.name | text | The name of the OTA module. We recommend that you use this field with ota_module.version to specify the OTA module corresponding to the current OTA version number of the device. |
| ota_module.version | text | The current OTA version number of the device. We recommend that you use this field with ota_module.name. |
Operators
| Operator | Supported data type |
|---|---|
| = | number, date, text, and keyword |
| > | number and date |
| < | number and date |
| LIKE | text |
LIKE supports prefix matching, but does not support suffix matching or wildcard matching. A prefix must meet the following requirements:
- The prefix must be at least 4 characters and cannot contain any special characters, such as backslashes (\), forward slashes (/), ampersands (&), plus signs (+), hyphens (-), exclamation points (!) , parentheses (), colons (:), tildes (~), brackets [], braces {}, asterisks (*), and question marks (?).
- The prefix must end with a percent sign (
%).
Example:
product_key = "a1*********" and name LIKE "test%"