Event log database schema
Last modified by Peio Rigaux on 2021/07/01 16:38
Entity Relationship diagram
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)
- /!\ the new field 'priority' is currently used to distinguish IMDN from 'real' messages
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 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 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 * 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);
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);
-- Count number of messages sent by one user.
select count(*) from event_log where type_id=2 and sip_from LIKE '%user%';
select count(*) from event_log where type_id=2 and sip_from LIKE '%user%';
-- Count the number of messages sent by one user to another
-- or Count the number of messages sent by 2 users to each other (when called 2 times with swapped users)
drop procedure if exists getMessageNbSentUser1ToUser2;
DELIMITER //
create procedure getMessageNbSentUser1ToUser2 (user1 varchar(64), user2 varchar(64))
begin
set @counter := 1;
set @ev_log_counter := 0;
set @max := 0;
-- Declares a temporary table.
create temporary table myTable (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, sip_to varchar(100), call_id varchar(100));
-- Insert your required data in the table
-- user1 to user2
-- type_id=2 specifies an event_log of type message
-- we want to select each chatroom and associated call-id to where user1 sent a message
INSERT INTO myTable (sip_to, call_id)
SELECT distinct sip_to, call_id from event_log ev1 where type_id=2 and sip_to LIKE '%chatroom%' and sip_from LIKE '%user1%' and priority='normal';
-- Initialize the @max variable. We'll use this variable in the next WHILE loop.
SET @max := (select COUNT(ID) FROM myTable);
set @tmp_count := 0;
-- Loop
WHILE @counter <= @max DO
-- we want to count all message received by user2 and sent by user1
-- the last 'or' treats the case of a direct message
set @tmp_count := (SELECT count(*) from event_log where type_id=2 and sip_to LIKE '%user2%' and priority='normal' and ((sip_from=(select myTable.sip_to from myTable where id=@counter) and call_id=(select myTable.call_id from myTable where id=@counter))) or sip_from LIKE '%user1%' );
set @ev_log_counter := @ev_log_counter + @tmp_count;
SET @counter := @counter + 1;
END WHILE;
select @ev_log_counter;
drop table myTable;
END//
delimiter ;
call getMessageNbSentUser1ToUser2('user1', 'user2');
call getMessageNbSentUser1ToUser2('user2', 'user1');
-- or Count the number of messages sent by 2 users to each other (when called 2 times with swapped users)
drop procedure if exists getMessageNbSentUser1ToUser2;
DELIMITER //
create procedure getMessageNbSentUser1ToUser2 (user1 varchar(64), user2 varchar(64))
begin
set @counter := 1;
set @ev_log_counter := 0;
set @max := 0;
-- Declares a temporary table.
create temporary table myTable (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, sip_to varchar(100), call_id varchar(100));
-- Insert your required data in the table
-- user1 to user2
-- type_id=2 specifies an event_log of type message
-- we want to select each chatroom and associated call-id to where user1 sent a message
INSERT INTO myTable (sip_to, call_id)
SELECT distinct sip_to, call_id from event_log ev1 where type_id=2 and sip_to LIKE '%chatroom%' and sip_from LIKE '%user1%' and priority='normal';
-- Initialize the @max variable. We'll use this variable in the next WHILE loop.
SET @max := (select COUNT(ID) FROM myTable);
set @tmp_count := 0;
-- Loop
WHILE @counter <= @max DO
-- we want to count all message received by user2 and sent by user1
-- the last 'or' treats the case of a direct message
set @tmp_count := (SELECT count(*) from event_log where type_id=2 and sip_to LIKE '%user2%' and priority='normal' and ((sip_from=(select myTable.sip_to from myTable where id=@counter) and call_id=(select myTable.call_id from myTable where id=@counter))) or sip_from LIKE '%user1%' );
set @ev_log_counter := @ev_log_counter + @tmp_count;
SET @counter := @counter + 1;
END WHILE;
select @ev_log_counter;
drop table myTable;
END//
delimiter ;
call getMessageNbSentUser1ToUser2('user1', 'user2');
call getMessageNbSentUser1ToUser2('user2', 'user1');