# 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.

FieldExplanation
idMQTT message ID
clientidClient ID of the sender
usernameUsername of the sender
payloadMQTT payload
peerhostClient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
flagsFlags
headersInternal data related to the message processing
pub_propsThe PUBLISH Properties (MQTT 5.0 only)
timestampTimestamp (ms)
publish_received_atTime when PUBLISH message reaches Broker (ms)
nodeNode name of the trigger event

example

SELECT
  payload.msg as msg,
  clientid,
  username,
  payload,
  topic,
  qos
FROM
  "t/#"
1
2
3
4
5
6
7
8
9

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "qos": 1,
  "payload": "{\"msg\":\"hello\"}",
  "msg": "hello",
  "clientid": "c_emqx"
}
1
2
3
4
5
6
7
8

# 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

Event topic nameExplanation
$events/message_deliveredmessage delivery
$events/message_ackedmessage acknowledged
$events/message_droppedMessage dropped
$events/client_connectedConnection complete
$events/client_disconnectedDisconnect
$events/client_connackConnection ack
$events/client_check_acl_completeACL check complete
$events/session_subscribedSubscribe
$events/session_unsubscribedUnsubcribe

# $events/message_delivered

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

FieldExplanation
idMQTT message ID
from_clientidClient ID of the sender
from_usernameUsername of the sender
clientidClient ID of the receiver
usernameUsername of the receiver
payloadMQTT payload
peerhostclient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
flagsflags
pub_propsThe PUBLISH Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
publish_received_atTime when PUBLISH message reaches Broker (ms)
nodeNode name of the trigger event

example

SELECT
  from_clientid,
  from_username,
  topic,
  qos,
  node,
  timestamp
FROM
  "$events/message_delivered"
1
2
3
4
5
6
7
8
9

output

{
  "topic": "t/a",
  "timestamp": 1645002753259,
  "qos": 1,
  "node": "emqx@127.0.0.1",
  "from_username": "u_emqx_1",
  "from_clientid": "c_emqx_1"
}
1
2
3
4
5
6
7
8

# $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

FieldExplanation
idMQTT message id
from_clientidClient ID of the sender
from_usernameUsername of the sender
clientidClient ID of the receiver
usernameUsername of the receiver
payloadMQTT payload
peerhostclient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
flagsflags
pub_propsThe PUBLISH Properties (MQTT 5.0 only)
puback_propsThe PUBACK Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
publish_received_atTime when PUBLISH message reaches Broker (ms)
nodeNode name of the trigger event

example

SELECT
  from_clientid,
  from_username,
  topic,
  qos,
  node,
  timestamp
FROM
  "$events/message_acked"
1
2
3
4
5
6
7
8
9

output

{
  "topic": "t/a",
  "timestamp": 1645002965664,
  "qos": 1,
  "node": "emqx@127.0.0.1",
  "from_username": "u_emqx_1",
  "from_clientid": "c_emqx_1"
}
1
2
3
4
5
6
7
8

# $events/message_dropped

Trigger rule when a message has no subscribers

FieldExplanation
idMQTT message id
reasonreason for dropping, possible reasons:
no_subscribers: no clients subscribes the topic
clientidClient ID of the sender
usernameUsername of the sender
payloadMQTT payload
peerhostClient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
flagsflags
pub_propsThe PUBLISH Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
publish_received_atTime when PUBLISH message reaches Broker (ms)
nodeNode name of the trigger event

example

SELECT
  reason,
  topic,
  qos,
  node,
  timestamp
FROM
  "$events/message_dropped"
1
2
3
4
5
6
7
8

output

{
  "topic": "t/a",
  "timestamp": 1645003103004,
  "reason": "no_subscribers",
  "qos": 1,
  "node": "emqx@127.0.0.1"
}
1
2
3
4
5
6
7

# $events/delivery_dropped

Trigger rule when subscriber's message queue is full

FieldExplanation
idMQTT message id
reasonreason for dropping, possible reasons:
queue_full: the message queue is full(QoS>0)
no_local: it's not allowed for the client to received messages published by themselves
expired: the message or the session is expired
qos0_msg: the message queue is full(QoS0)
from_clientidClient ID of the sender
from_usernameUsername of the sender
clientidClient ID of the receiver
usernameUsername of the receiver
payloadMQTT payload
peerhostclient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
flagsflags
pub_propsThe PUBLISH Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
publish_received_atTime when PUBLISH message reaches Broker (ms)
nodeNode name of the trigger event

example

SELECT
  from_clientid,
  from_username,
  reason,
  topic,
  qos
FROM "$events/delivery_dropped"
1
2
3
4
5
6
7

output

{
  "topic": "t/a",
  "reason": "queue_full",
  "qos": 1,
  "from_username": "u_emqx_1",
  "from_clientid": "c_emqx_1"
}
1
2
3
4
5
6
7

# $events/client_connected

Trigger the rule when the terminal is connected successfully

FieldExplanation
clientidclientid
usernameCurrent MQTT username
mountpointMountpoint for bridging messages
peernameIPAddress and Port of terminal
socknameIPAddress and Port listened by emqx
proto_nameprotocol name
proto_verprotocol version
keepaliveMQTT keepalive interval
clean_startMQTT clean_start
expiry_intervalMQTT Session Expiration time
is_bridgewhether it is MQTT bridge connection
connected_atTerminal connection completion time (s)
conn_propsThe CONNECT Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
nodeNode name of the trigger event

example

SELECT
  clientid,
  username,
  keepalive,
  is_bridge
FROM
  "$events/client_connected"
1
2
3
4
5
6
7

output

{
  "username": "u_emqx",
  "keepalive": 60,
  "is_bridge": false,
  "clientid": "c_emqx"
}
1
2
3
4
5
6

# $events/client_disconnected

Trigger rule when terminal connection is lost

FieldExplanation
reasonReason for disconnection of terminal
normal:the client is actively disconnected
kicked:the server kicks out, and it is kicked out through REST API
keepalive_timeout: keepalive timeout
not_authorized: auth failed,or acl_nomatch = disconnect, Pub/Sub without permission will disconnect the client
tcp_closed: the peer has closed the network connection
internal_error: malformed message or other unknown errors
clientidclient ID
usernameCurrent MQTT username
peernameIPAddress and Port of terminal
socknameIPAddress and Port listened by emqx
disconnected_atTerminal disconnection completion time (s)
disconn_propsThe DISCONNECT Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
nodeNode name of the trigger event

example

SELECT
  clientid,
  username,
  reason,
  disconnected_at,
  node
FROM
  "$events/client_disconnected"
1
2
3
4
5
6
7
8

output

{
  "username": "u_emqx",
  "reason": "normal",
  "node": "emqx@127.0.0.1",
  "disconnected_at": 1645003578536,
  "clientid": "c_emqx"
}
1
2
3
4
5
6
7

# $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.

FieldExplanation
reason_codeReason code
clientidClient ID of the sender
usernameUsername of the sender
peernameIPAddress and Port of terminal
socknameIPAddress and Port listened by emqx
proto_nameprotocol name
proto_verprotocol version
keepaliveMQTT keepalive interval
clean_startMQTT clean_start
expiry_intervalMQTT Session Expiration time
connected_atTerminal connection completion time (s)
conn_propsThe CONNECT Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
nodeNode name of the trigger event

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

reason_codedescription
connection_acceptedConnection accepted
unacceptable_protocol_versionThe server does not support the MQTT protocol requested by the client
client_identifier_not_validThe client ID is the correct UTF-8 string, but is not allowed by the server
server_unavaliableNetwork connection has been established, but MQTT service is unavailable
malformed_username_or_passwordThe data in the username or password is in the wrong format
unauthorized_clientClient connection is not authorized

MQTT v5.0

reason_codedescription
successconnect success
unspecified_errorUnspecified error
malformed_packetMalformed Packet
protocol_errorProtocol Error
implementation_specific_errorImplementation specific error
unsupported_protocol_versionUnsupported Protocol Version
client_identifier_not_validClient Identifier not valid
bad_username_or_passwordBad User Name or Password
not_authorizedNot authorized
server_unavailableServer unavailable
server_busyServer busy
bannedBanned
bad_authentication_methodBad authentication method
topic_name_invalidTopic Name invalid
packet_too_largePacket too large
quota_exceededQuota exceeded
retain_not_supportedRetain not supported
qos_not_supportedQoS not supported
use_another_serverUse another server
server_movedServer moved
connection_rate_exceededConnection rate exceeded

example

SELECT
  clientid,
  username,
  reason,
  connected_at,
  node
FROM
  "$events/client_connack"
1
2
3
4
5
6
7
8

output

{
  "username": "u_emqx",
  "reason": "success",
  "node": "emqx@127.0.0.1",
  "connected_at": 1645003578536,
  "clientid": "c_emqx"
}
1
2
3
4
5
6
7

# $events/client_check_acl_complete

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

FieldExplanation
clientidClient ID of the sender
usernameUsername of the sender
peerhostClient IPAddress
topicMQTT topic
actionpublish or subscribe
resultallow or deny, acl check result
is_cachetrue or false
When is_cache is true, the acl data comes from the cache
When is_cache is false, the acl data comes from the plugs
timestampTimestamp (ms)
nodeNode name of the trigger event

example

SELECT
  clientid,
  username,
  topic,
  action,
  result,
  is_cache,
  node
FROM
  "$events/client_check_acl_complete"
1
2
3
4
5
6
7
8
9
10

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "action": "publish",
  "result": "allow",
  "is_cache": "false",
  "node": "emqx@127.0.0.1",
  "clientid": "c_emqx"
}
1
2
3
4
5
6
7
8
9

# $events/session_subscribed

Trigger the rule when the terminal subscribes successfully

FieldExplanation
clientidClient ID
usernameCurrent MQTT username
peerhostclient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
sub_propsThe SUBSCRIBE Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
nodeNode name of the trigger event

example

SELECT
  clientid,
  username,
  topic,
  qos
FROM
  "$events/session_subscribed"
1
2
3
4
5
6
7

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "qos": 1,
  "clientid": "c_emqx"
}
1
2
3
4
5
6

# $events/session_unsubscribed

Triggered when the terminal subscription is cancelled successfully

FieldExplanation
clientidClient ID
usernameCurrent MQTT username
peerhostclient IPAddress
topicMQTT topic
qosEnumeration of message QoS 0,1,2
unsub_propsThe UNSUBSCRIBE Properties (MQTT 5.0 only)
timestampEvent trigger time(millisecond)
nodeNode name of the trigger event

example

SELECT
  clientid,
  username,
  topic,
  qos
FROM
  "$events/session_unsubscribed"
1
2
3
4
5
6
7

output

{
  "username": "u_emqx",
  "topic": "t/a",
  "qos": 1,
  "clientid": "c_emqx"
}
1
2
3
4
5
6

Rule engine buildin functions