Entity Relationship diagram

entity-relationship-diagram.png

Notes:

  • `event_log` is the entry point of the event logs storage
  • `event_type` can be a Registration (0), Call (1), Message (2), Auth (3), QualityStatistics (4)
  • `message_type` supports currently two values : Received (0) or Delivered (1)
  • `registration_type` deals with Register (0), Unregister (1), Expired (2)

Query examples

-- Select all call logs.
SELECT event_log.id AS id, sip_from, sip_to, user_agent, date, status_code, reason, completed, call_id, cancelled
FROM event_log
JOIN event_call_log ON event_call_log.id = event_log.id
WHERE type_id = 1;
-- Select all unique user agents.
SELECT DISTINCT user_agent FROM event_log;
-- Select last logs with limit.
SELECT * FROM (
 SELECT event_log.id AS id, event_type.type AS event_type, sip_from, sip_to, user_agent, date, status_code, reason, completed, call_id,
    method, origin, user_exists, -- auth
   cancelled, -- call
   report, -- call_quality_statistics
   message_type.type AS message_type, uri, -- message
   registration_type.type AS registration_type, contacts -- registration
 FROM event_log
 LEFT JOIN event_auth_log ON event_auth_log.id = event_log.id
 LEFT JOIN event_call_log ON event_call_log.id = event_log.id
 LEFT JOIN event_call_quality_statistics_log ON event_call_quality_statistics_log.id = event_log.id
 LEFT JOIN event_message_log ON event_message_log.id = event_log.id
 LEFT JOIN event_registration_log ON event_registration_log.id = event_log.id
 LEFT JOIN event_type ON event_type.id = event_log.type_id
 LEFT JOIN message_type ON message_type.id = event_message_log.type_id
 LEFT JOIN registration_type on registration_type.id = event_registration_log.type_id
 ORDER BY event_log.id DESC
 LIMIT 100
) AS event
ORDER BY event.id ASC;
-- Select call and message logs (from last 24 hours).
SELECT event_log.id AS id, event_type.type AS event_type, sip_from, sip_to, user_agent, date, status_code, reason, completed, call_id,
  cancelled, -- call
 message_type.type AS message_type, uri -- message
FROM event_log
LEFT JOIN event_call_log ON event_call_log.id = event_log.id
LEFT JOIN event_message_log ON event_message_log.id = event_log.id
LEFT JOIN event_type ON event_type.id = event_log.type_id
LEFT JOIN message_type ON message_type.id = event_message_log.type_id
WHERE date > DATE_SUB(NOW(), INTERVAL 1 DAY);
Tags:
Created by Ronan on 2018/03/07 12:18