How to Analyze your AWS IoT Logs with Standard SQL queries

1. Locating your IoT Logs

Amazon CloudWatch allows you to observe and monitor resources and applications on AWS, on premises, and on other clouds. When AWS IoT Core logging is activated, all major events that occur (above ‘logging’ level) in your AWS IoT regional endpoints will be sent to Amazon CloudWatch, in a default CloudWatch log group named AWSIotLogsV2.

It is also possible to upload log records from existing device-side log files to a CloudWatch custom IoT Log Group, if required by your use case.

Cloudwatch Logs Streams in IoT log group(s) include all individual event logs. For this reason, they obviously represent a first-choice data source for anyone willing to understand events at a very granular level. As examples, you might want to:

• “Get the list of devices that got disconnected following last Friday’s power outage with their respective reconnect times”. This may help with understanding the impact of the power outage on your data collection.
• “Provide a list of client devices that have frequent connects/disconnects”. This may help with understanding connectivity challenges and other hardware issues leading to intermittent connectivity.
• “Generate a detailed report on devices activity last month”. This may help understanding how different groups of devices have been utilizing the services to perform charge-backs to other business units and much more other use cases.

CloudWatch Logs Insights (the default CloudWatch tool which enables you to interactively search and analyze your log data in Amazon CloudWatch Logs) uses a purpose-built query language with a few simple but powerful commands.

However, If you feel more comfortable with SQL, you can leverage it instead to query your IoT Operational Logs, by using CloudWatch Connector feature of Amazon Athena. Amazon Athena is a serverless enterprise grade SQL query engine capable of querying against a variety of data sources from Amazon S3, to Relational Databases, to flat text files (CSV).

2. How Amazon Athena CloudWatch connector handles Log Groups

The Amazon Athena CloudWatch connector enables Amazon Athena to communicate with CloudWatch so that you can query your log data in Amazon Athena using standard SQL queries.

The Athena CloudWatch connector maps:

• your LogGroups as schemas (that is, databases)
• each LogStream as a table.
• a special all_log_streams view that contains all LogStreams in the LogGroup. This view enables you to query all the logs in a LogGroup at once instead of searching through each LogStream individually.

3. Creating an Amazon Athena CloudWatch connector for your IoT Log Group

Follow this procedure to create a CloudWatch connector to your IoT LogGroup on Amazon Athena console.

The results should look similar to the screens below.

3.1. CloudWatch connector in Amazon Athena

3.2. Amazon Athena database and tables

Here are the Athena databases and tables created from the default IoT Log Group AWSIoTLogsV2 (refer to Log Group screenshots in paragraph 1. above).

Pay attention to the number of tables in Athena (55 = 54 + 1), corresponding to 54 log streams (from CloudWatch) + 1 ‘consolidated streams’ table (“all_log_streams”)

4. Query IoT Logs data using SQL in Amazon Athena

Now that all the IoT Logs data are available to Amazon Athena, SQL queries can be run to get the needed insights.

4.1. Example 1: Get the chattiest devices ranking

SQL Command

SELECT json_extract_scalar(message, '$.clientId') as client_Device_Id, count(*) as Publish_In_Count 
    FROM "AWSIotLogsV2".all_log_streams 
    where json_extract_scalar(message, '$.eventType') = 'Publish-In' 
    group by json_extract_scalar(message, '$.clientId') 
    order by count(*) desc

Output

The chattiest devices in the entire fleet since starting AWS IoT Core logging in the account are “test_client”, “mqtt5”,“” and “Elb_Thing”. From these insights, one may realize there is a chatty client without clientId (an intruder, a test environment client that hasn’t been properly decommissioned, etc.).

4.2. Example 2: Single out devices with aberrant connect/disconnect patterns

SQL Command

Getting October 2023 detailed Month-To-Date statistics on devices Connect/Disconnect to AWS IoT Core.

SELECT json_extract_scalar(message, '$.clientId') as client_Device_Id, json_extract_scalar(message, '$.eventType') as eventType, count(*) as Count 
    FROM "AWSIotLogsV2".all_log_streams 
    where json_extract_scalar(message, '$.eventType') in ('Connect', 'Disconnect') 
        and json_extract_scalar(message, '$.timestamp') between '2023-10-01' and '2023-11-01'
    group by json_extract_scalar(message, '$.clientId'), json_extract_scalar(message, '$.eventType')
    order by json_extract_scalar(message, '$.clientId')

Output

If all the devices in the list are of the same model and belong to the same fleet onsite for example, we may infer from this output that the device “test_client” needs further investigation and perhaps schedule a technician visit on-site!

5. Insights beyond IoT Logs

Amazon Athena Connector goes far beyond AWS IoT Logs analysis; it also allows access to AWS IoT Metrics, other services logs and metrics, and even business data like AWS Cost and Usage Reports. This paves the way for more complex data analysis capabilities and providing answer to vital business questions like:

• What is the list of IoT devices that caused us to exceed the message broker's connect request quota last month?
• How can I fairly split monthly bills between multiple IoT tenants under the same account (according to their actual usage)?
• What are the IoT device families that incur the largest costs in the Cloud?
• ...

6. Conclusion

CloudWatch Logs Insights (the default tool which enables you to interactively search and analyze your log data in Amazon CloudWatch Logs) uses a purpose-built query language with a few simple but powerful commands.

However, for more complex business needs, Amazon Athena Connector leverages SQL to query, not only AWS IoT Logs, but also other events happening around and beyond the immediate IoT scope (Certificates creation, Lambda authorizers execution, Billing events, ...).

By integrating data from disparate sources (and therefore enabling a more comprehensive overview of your IoT landscape), Athena Connector for CloudWatch allows you to provide accurate answers to a broader spectrum of business operation needs.