Available fields in rule engine SQL statements

The fields available in the SELECT and WHERE clauses are related to the type of event. Among them, clientid, username and event are common fields that is contained by each type of event.

Use Rule Engine SQL to Handle Message Publishing

The SQL statement of the rules engine can handle the message publishing. In a rule statement, the user can specify one or more topics with the FROM clause, and the rule will be triggered when any message is published to the specified topic.

example

SELECT
  payload.msg as msg,
  clientid,
  username,
  payload,
  topic,
  qos
FROM
  "t/#"

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "qos": 1,
  "payload": "{\"msg\":\"hello\"}",
  "msg": "hello",
  "clientid": "c_emqx"
}

Use Rule Engine SQL to Handle Events

The SQL statements of the rule engine can handle both messages (message publishing) and events (client online and offline, client subscription, etc.). For messages, the FROM clause is directly followed by the topic name; for events, the FROM clause is followed by the event topic.

The topic of the event message starts with "$events/", such as "$events/client_connected", "$events/session_subscribed". If you want emqx to publish the event message, you can configure it in the emqx_rule_engine.conf file.

Event topic available for FROM clause

$events/message_delivered

Trigger the rule when a message is put into the underlying socket

example

SELECT
  from_clientid,
  from_username,
  topic,
  qos,
  node,
  timestamp
FROM
  "$events/message_delivered"

output

{
  "topic": "t/a",
  "timestamp": 1645002753259,
  "qos": 1,
  "node": "emqx@127.0.0.1",
  "from_username": "u_emqx_1",
  "from_clientid": "c_emqx_1"
}

$events/message_acked

The rule is triggered when the message is sent to the client and an ack is received from the client. Only QOS1 and QOS2 messages will be triggered

example

SELECT
  from_clientid,
  from_username,
  topic,
  qos,
  node,
  timestamp
FROM
  "$events/message_acked"

output

{
  "topic": "t/a",
  "timestamp": 1645002965664,
  "qos": 1,
  "node": "emqx@127.0.0.1",
  "from_username": "u_emqx_1",
  "from_clientid": "c_emqx_1"
}

$events/message_dropped

Trigger rule when a message has no subscribers

example

SELECT
  reason,
  topic,
  qos,
  node,
  timestamp
FROM
  "$events/message_dropped"

output

{
  "topic": "t/a",
  "timestamp": 1645003103004,
  "reason": "no_subscribers",
  "qos": 1,
  "node": "emqx@127.0.0.1"
}

$events/delivery_dropped

Trigger rule when subscriber’s message queue is full

example

SELECT
  from_clientid,
  from_username,
  reason,
  topic,
  qos
FROM "$events/delivery_dropped"

output

{
  "topic": "t/a",
  "reason": "queue_full",
  "qos": 1,
  "from_username": "u_emqx_1",
  "from_clientid": "c_emqx_1"
}

$events/client_connected

Trigger the rule when the terminal is connected successfully

example

SELECT
  clientid,
  username,
  keepalive,
  is_bridge
FROM
  "$events/client_connected"

output

{
  "username": "u_emqx",
  "keepalive": 60,
  "is_bridge": false,
  "clientid": "c_emqx"
}

$events/client_disconnected

Trigger rule when terminal connection is lost

example

SELECT
  clientid,
  username,
  reason,
  disconnected_at,
  node
FROM
  "$events/client_disconnected"

output

{
  "username": "u_emqx",
  "reason": "normal",
  "node": "emqx@127.0.0.1",
  "disconnected_at": 1645003578536,
  "clientid": "c_emqx"
}

$events/client_connack

The rule event is triggered when the server sends a CONNACK packet to the client. reason_code contains the error reason code.

The MQTT v5.0 protocol renames the return code to a reason code, adding a reason code to indicate more types of errors(Reason code and ACK - MQTT 5.0 new features (opens new window)).

MQTT v3.1.1

MQTT v5.0

example

SELECT
  clientid,
  username,
  reason,
  node
FROM
  "$events/client_connack"

output

{
  "username": "u_emqx",
  "reason": "success",
  "node": "emqx@127.0.0.1",
  "connected_at": 1645003578536,
  "clientid": "c_emqx"
}

$events/client_check_acl_complete

The rule event is triggered when the client check acl complete.

example

SELECT
  clientid,
  username,
  topic,
  action,
  result,
  is_cache,
  node
FROM
  "$events/client_check_acl_complete"

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "action": "publish",
  "result": "allow",
  "is_cache": "false",
  "node": "emqx@127.0.0.1",
  "clientid": "c_emqx"
}

$events/session_subscribed

Trigger the rule when the terminal subscribes successfully

example

SELECT
  clientid,
  username,
  topic,
  qos
FROM
  "$events/session_subscribed"

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "qos": 1,
  "clientid": "c_emqx"
}

$events/session_unsubscribed

Triggered when the terminal subscription is cancelled successfully

example

SELECT
  clientid,
  username,
  topic,
  qos
FROM
  "$events/session_unsubscribed"

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "qos": 1,
  "clientid": "c_emqx"
}

Rule engine buildin functions