MQTTvue/mqtt-vue-dashboard/server/migrations/create_device_logs_view.sql

CREATE OR REPLACE VIEW vw_device_logs AS
SELECT 
  NULL as id,
  cc.clientid,
  CASE 
    WHEN cc.event = 'client.connected' THEN 'connect'
    WHEN cc.event = 'client.disconnected' THEN 'disconnect'
    ELSE 'unknown'
  END as event_type,
  cc.timestamp as event_time,
  NULL as topic,
  NULL as payload,
  NULL as qos,
  cc.username,
  cc.peername,
  cc.proto_ver,
  cc.node,
  cc.reason as details,
  cc.timestamp as created_at
FROM vw_client_connections cc
WHERE cc.event IN ('client.connected', 'client.disconnected')

UNION ALL

SELECT 
  mm.id,
  mm.clientid,
  CASE 
    WHEN mm.message_type = 'publish' THEN 'publish'
    WHEN mm.message_type = 'subscribe' THEN 'subscribe'
    WHEN mm.message_type = 'unsubscribe' THEN 'unsubscribe'
    ELSE mm.message_type
  END as event_type,
  mm.message_time as event_time,
  mm.topic,
  mm.payload,
  mm.qos,
  mm.username,
  NULL as peername,
  mm.proto_ver,
  mm.node,
  NULL as details,
  mm.created_at
FROM mqtt_messages mm
WHERE mm.message_type IN ('publish', 'subscribe', 'unsubscribe')

ORDER BY event_time DESC;