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