I want to capture connect/disconnect events in AWS IoT Core and trigger a Lambda function based on the connection status. However, there are occasional connections with a clientId that starts with “iotconsole” that I want to exclude in the rule.

The following query works without any issues.

SELECT topic(5) as deviceid, * as data FROM '$aws/events/presence/#' WHERE eventType in ['connected', 'disconnected']

However, the following SQL query causes an error.

ERROR Rule SQL…

SELECT topic(5) as deviceid, * as data FROM '$aws/events/presence/#' WHERE eventType in ['connected', 'disconnected'] AND STARTSWITH(clientid(), 'iotconsole')=false

SELECT topic(5) as deviceid, * as data FROM '$aws/events/presence/#' WHERE eventType in ['connected', 'disconnected'] AND STARTSWITH(topic(5), 'iotconsole')=false

Error message

{
    "timestamp": "2024-05-28 01:11:29.122",
    "logLevel": "DEBUG",
    "traceId": "616fc1c4-41c1-2580-d03a-d26dca42701e",
    "accountId": "816509324099",
    "status": "Success",
    "eventType": "RuleExecution",
    "clientId": "iotconsole-014bebc7-94a0-43d9-99e3-48c6fc60b0ee",
    "topicName": "$aws/events/presence/disconnected/iotconsole-014bebc7-94a0-43d9-99e3-48c6fc60b0ee",
    "ruleName": "IOTRT_UE1_QAS_LESEBMETC_DEVICE_CON_DCON",
    "ruleAction": "IOTRT_UE1_QAS_LESEBMETC_DEVICE_CON_DCON",
    "principalId": "AIDA34G57RNB5OWYW5WQB",
    "details": "Message does not satisfy WHERE clause condition"
}

{
    "timestamp": "2024-05-28 01:11:29.122",
    "logLevel": "ERROR",
    "traceId": "616fc1c4-41c1-2580-d03a-d26dca42701e",
    "accountId": "816509324099",
    "status": "Failure",
    "eventType": "RuleExecution",
    "clientId": "iotconsole-014bebc7-94a0-43d9-99e3-48c6fc60b0ee",
    "topicName": "$aws/events/presence/disconnected/iotconsole-014bebc7-94a0-43d9-99e3-48c6fc60b0ee",
    "ruleName": "IOTRT_UE1_QAS_LESEBMETC_DEVICE_CON_DCON",
    "ruleAction": "EvaluatingWhereClause",
    "principalId": "AIDA34G57RNB5OWYW5WQB",
    "details": "Undefined result"
}

The SQL query keeps causing errors and I don’t know what the problem is.