Information sent to the iotQi cloud from your devices is stored in a No-SQL database, resident in a secure cloud location.  Using an access token obtained from Setup or WebAPI you can query this database.  For increased security database access tokens last 2 hours, before expiring.  If you need access for a longer period, you should build into your process a mechanism to request a new token prior to the previous token’s expiration.


This guide discusses…

  • How to obtain an access token
  • How to connect to the device events database
  • SQL language constructs that are applicable to the device database with examples


Obtaining Device Data Access Tokens

You can obtain Device Data Access Tokens via Setup or via a Web API method call.  Both approaches are discussed below. 




A token looks like this...

type=resource&ver=1&sig=umJOOU7xW7aR20/pW7wJrw==;lGupm2069XsJaqPWPvQcLU2W2cwsW+jRtvJ0J+lEpo/rS2FwmU WCpuORw603MGzN+b3VT8FY6TuHsbWUNL8kS2flswIlE/c4OqDSDcSgZb2ebsXzvh/FJxiatHztc0LTGt9l4WFg0zjyyEKDmPmt11J Hhgud/Sv2fVXc4XG2Wj9+ySz3xrdzMEBtV4faTNYS9ksGLhjenLCBMOCF7Pc/EcGd2nqRv6uno5jxmtmoDs9jNXIYqWEjVujVH8BP yo2asT7Tzfb1YwarmUeomguB4gMx3s7vaps3xq21cTO7DSxoYMCs+BltpfrbEanvAxGckwRR5LQpqNXrRvsBNXNnQQ==;


In Setup

The Setup application allows you to retrieve a data access token via your clipboard to paste into a script or the command line of the Event Reader (discussed below).

  1. From within Setup, navigate to the Subscription containing the device data you are interested in: Account | Subscriptions | Details (list link)
  2. On the Subscription Detail form, you will find copy links for both your Subscription Identifier (does not change) and a Data Access Token (expires 2 hours after retrieval).


WebAPI Actions

If you are building a more automatic process, it would make sense that you build into your process a mechanism to get a fresh data access token immediately before using it.  In this scenario you would get the token via the iotQi Web API using the api/events/subscriptionToken/{subscription} web method.  This is a GET, you can use the subscription ID or the subscription name for {subscription}, and you can use either your username\ password or a subscription API key for authentication.


Using Postman

For more information about using Postman and CURL with the iotQi Web API search the help center for Postman or CURL. 


Device Event Data Connection Options

And the options are...




Console
The web-based Console application allows for a “peek” at your device data.  Via Console, you can view your device list and from there look at the last 100 events, alerts, or command-responses sent from your device.


Event Tool (exe)
LooUQ has built a Windows console-based application call Event Tool that is available here in on our Answer Center to allow you to download data from the iotQi cloud events database.  You can download all of your data or use a SQL command to limit the results to a subset of your device data.


Event Tool (source code)
The Event Tool utility (described above) is an open-source console application written in .NET C# and is available as source code on our GitHub site.  Use it as is, or modify it to be part of your back-end system processes.



Querying Device Event Data

We will cover the SQL part of queries in a section below, but before we cover the SQL operators it is important for you to know about the format of device event data.  The fields discussed below will become part of your query statement.To start off, lets cover the overall schema of device events.  Shown below is a typical “telemetry” event, with device generated user data.  At the top-level there are 6 objects or fields…

  • Message Identifier (line 2, “id”): This field uniquely identified the event, this is the event document’s primary key.
  • Partition Key (line 3, “pkey”): The partition key separates event documents by iotQi subscription, the token used to gain access to event documents is tied to this Partition Key.
  • iotQi Meta Information (lines 4 through 20, “iotqiMeta”): The iotQi meta object contains several fields with information about the device, its parents, and information pertaining to the processing of the event message received from the device. The iotQi Meta object is discussed in more detail in a separate section below.
  • Display Value (line 21, “displayValue”): A text field used by your device code as you see fit, this can be a text phrase or a simple value, it is all up to you.
  • Event Body (lines 22 through 29, “eventBody”): This is the information generated from your IoT device. 
  • Database Meta (lines 30 through 34): This information is automatically appended to the event data when it is stored in Azure Cosmos DB.



iotQi Meta Information

The items below are found within the iotqiMeta object.  To reference them you will prefix them with “iotqiMeta.”, like “iotqiMeta.messageType”.

  • Message ID (line 5, "messageId"): Message ID is the unique identifier generated by the iotQi code within your device. Like the id field at the top-level of the schema it is an unique identifier to this document.
  • Correlation ID (line 6, "correlationId"): The correlation ID field is only populated for command response events. It identifies the “messageId” of the event going to the device.
  • Event Type (line 7, "eventType"): Event type tells us what kind of message this event represents. There are 3 types of events that you will see: 
    • Telemetry (“telemetry”): The non-priority, data series events your devices generated on a periodic basis.
    • Command-Response (“command-response”): The response to a command that you have sent to your device via the WebAPI or indirectly via the Console application.
    • Alert (“alert”): These are high-priority events that will be converted into email or SMS notifications.
  • Event Class (line 8, "eventClass"): Event class indicates the origin scope of the event message. Only two values are implemented: “user" indicates your device logic originated the message, while “iotqi” indicates the message was system originated.
  • Event Name (line 9, "eventName"): The event name is defined to mean different things depending on the type of message (event type). In general, it is the title of the event.  
    • For telemetry, it is the name of your data series.
    • For alerts, it is the subject of the alert.
  • Event Time (line 10, "eventTimeUtc"): The time when the device message entered into the iotQi cloud.
  • Event Process Time (line 11, "_eventProcessedTimeUtc"): This is the timestamp for the message leaving the first stage of the cloud processing pipeline. 
  • Storage Process Time (line 12, "_eventStoredTimeUtc"): Storage process time indicates when the device message was written to storage as a device event data document.
  • Organization ID (line 13, "organizationId"): Each account (aka organization) in iotQi has a unique identifier, you may recognize the format as GUIDs.
  • Organization Name (line 14, "organizationName"): The name of your account\organization. At registration the organization\account name will be your username, but this can be changed.
  • Subscription ID (line 15, "subscriptionId"): Like organizations (aka accounts) all subscriptions have a unique GUID format identifier.
  • Subscription Name (line 16, "subscriptionName"): The name of your subscription, the first subscription created during registration is set to "Default Subscription". You can change a subscription name and if you create new subscriptions you will need to provide a unique subscription name within your account.
  • Device ID (line 17, "deviceId"): A GUID formatted unique identifier for your device. Device names can be changed, but a device ID does not change.
  • Device Name (line 18, "deviceName"): The name you assigned to the device in Setup.
  • Device Location (line 19, "location"): The text value you assigned to the device in Setup.




All times are expressed as universal time or UTC, in ISO-8601 time formats. These date\time values look like this:  2017-01-21T16:35:51Z. The _EventProcessTimeUtc and _EventStoredTimeUtc are internal fields that are used by LooUQ for operational monitoring and are not recommended for use in applications consuming event data. These fields may be removed at a future date.


SQL Summary

The device event database uses Azure DocumentDb and is a no-SQL data store, but you can still use a subset of ANSI SQL to query it and get your data out in powerful fashion.  The Event Reader utility and, soon, the Console web-application will support this SQL functionality to filter your data responses to meet your needs.



The simplest query is just a select, like…


SELECT * FROM events 
-or-  the convenient version of SELECT * FROM events e


As you build up the parts of your query you will reference the set name for all fields, so you will see why you may prefer the second version: e.displayValue is shorter than events.displayValue and a little easier to see the intended use.


Where Clause

The WHERE clause (WHERE <filter_condition>) is optional. It specifies the condition(s) that the JSON documents provided by the source must satisfy in order to be included as part of the result. Any JSON document must evaluate the specified conditions to "true" to be considered for the result.




SELECT e.iotqiMeta.deviceName

FROM events e

WHERE e.iotqiMeta.deviceId = "5231175b-2a28-4f21-9d92-7ea15e5ff3e2"





Type    
Operators
Arithmetic
 +,-,*,/,% 
 Bitwise 
|, &, ^, <<, >>, >>> (zero-fill right shift)
 Logical 
AND, OR, NOT
Comparison
 =, !=, <, >, <=, >=, <> 
String
 || (concatenate) 


Unary Operators
 +,-, ~ and NOT 


Keywords

  • BETWEEN
  • IN
  • Ternary (?)
  • Coalesce (??)
  • TOP
  • ORDER BY


Functions

Function Group
Operator Functions
Mathematical Functions 
ABS, CEILING, EXP, FLOOR, LOG, LOG10, POWER, ROUND, SIGN, SQRT, SQUARE, TRUNC, ACOS, ASIN, ATAN, ATN2, COS, COT, DEGREES, PI, RADIANS, SIN, and TAN 
Type Checking Functions 
IS_ARRAY, IS_BOOL, IS_NULL, IS_NUMBER, IS_OBJECT, IS_STRING, IS_DEFINED, and IS_PRIMITIVE 
String Functions 
CONCAT, CONTAINS, ENDSWITH, INDEX_OF, LEFT, LENGTH, LOWER, LTRIM, REPLACE, REPLICATE, REVERSE, RIGHT, RTRIM, STARTSWITH, SUBSTRING, and UPPER 
Array Functions 
 ARRAY_CONCAT, ARRAY_CONTAINS, ARRAY_LENGTH, and ARRAY_SLICE 
Spatial Functions 
ST_DISTANCE, ST_WITHIN, ST_INTERSECTS, ST_ISVALID, and ST_ISVALIDDETAILED 


Next Topics

For a more in-depth coverage of Device Event (DocumentDB) SQL queries check out these links…