Mass Query of Last Online Timestamp of Things

To retrieve a "last online" timestamp for all disconnected IoT Things in your fleet, you have a few options:

1. Fleet Indexing with Connectivity Data: While you're correct that the connectivity timestamp might be missing after approximately an hour of disconnection, fleet indexing still provides the most direct way to query device connectivity status. However, this approach has limitations for devices disconnected for longer periods.

2. Lifecycle Events + Database: A more reliable approach is to capture and store the lifecycle events. AWS IoT publishes connect/disconnect events to MQTT topics ($aws/events/presence/connected/clientId and $aws/events/presence/disconnected/clientId). You can:

• Create an IoT rule to capture these events
• Store the timestamp data in a database (DynamoDB, RDS, etc.)
• Query this database when you need historical connectivity information

3. Lambda Function with ListPrincipalThings: For devices authenticating with mTLS, you can extract the principal identifier (certificate ID) from lifecycle events, build your certificate ARN, and use the ListPrincipalThings API to get your thing name. This can be done in a Lambda function invoked via a rule action.

4. Timestamps in Messages: If your devices send regular messages, you can add timestamps to each published message. If devices lack this capability, you can use server-side features like AWS IoT Rules SQL language's timestamp() function to enrich messages with timestamps when received.

For a comprehensive solution that maintains AWS as the single source of truth while providing historical data:

• Use IoT rules to capture lifecycle events
• Store these events in a database with the thing name and timestamp
• Create a query mechanism (API Gateway + Lambda or direct database queries) to retrieve the last online timestamp for any device

This approach gives you complete control over how long you retain the data and allows you to query devices that have been offline for extended periods.
Sources
AWS IoT Thing Connected Event, Find the thing Name from Event. | AWS re:Post
Manage thing indexing - AWS IoT Core
Lifecycle events - AWS IoT Core
Data management - Internet of Things (IoT) Lens