one more approach track DLR status via database trigger

/* MySQL table and trigger to track SMS status changes - [email protected] */

--
--

/* find recently sent sms fast */
create index ix_sent_sms_time on sent_sms (time);

/* find sent sms by dlr url fast */
create index ix_sent_sms_dlr_url on sent_sms (dlr_url);

--
--

/* SMS status track */
create table sms_track (
dlr_url varchar(255) primary key, -- SMS key, as dlr url, must be present and unique to track SMS status
sms_id int(10) unique, -- ID of last SMS referring to above URL, MT or DLR
-- sms status, as dlr mask:
dlr_mask int(10) not null, -- 0-created, 8-sent (to SMSC), 1-success, 2-fail, 16-expired (or other error), 4-buffered, 32-intermediate
stale int(1) default 0 not null, -- is SMS stale, due to timeout on local host
create_time datetime not null, -- when SMS was created
update_time datetime not null, -- when SMS status was last updated
valid_time datetime not null   -- when SMS will expire
);

/* */
-- create index ix_sms_track_create_time on sms_track (create_time);

/* find recently updated messages fast */
create index ix_sms_track_update_time on sms_track (update_time);

/* find messages to become stale fast */
create index ix_sms_track_expire_time on sms_track (dlr_mask,valid_time);

delimiter //
--
drop trigger if exists sent_sms_sms_track_ins //

/* */
create trigger sent_sms_sms_track_ins
after insert on sent_sms
for each row
begin
if new.dlr_url is not null and new.time is not null then
    if new.sms_type = 2 then
        insert into sms_track (dlr_url,sms_id,dlr_mask,stale,create_time,update_time,valid_time)
        values (new.dlr_url,new.sql_id,0,0,from_unixtime(new.time),from_unixtime(new.time),
        from_unixtime(new.time + ifnull(new.validity,10800)*60)); -- use 7 days validity by default
    elseif new.sms_type = 3 then
        update sms_track set sms_id = new.sql_id,dlr_mask = new.dlr_mask,update_time = from_unixtime(new.time)
        where dlr_url = new.dlr_url and stale = 0;
    end if;
end if;
end;
//
delimiter ;


/*

-- show track list
select sql_id,smsc_id,sender,receiver,t.dlr_mask,stale,create_time,update_time,valid_time
from sms_track t left join sent_sms s on s.sql_id = t.sms_id where 1=1
-- and smsc_id='mts'
order by create_time desc,sql_id desc limit 25;

-- show wait list
select sql_id,smsc_id,sender,receiver,t.dlr_mask,create_time,
unix_timestamp(now())-unix_timestamp(update_time) elapsed,
unix_timestamp(valid_time)-unix_timestamp(now()) remains
from sms_track t left join sent_sms s on s.sql_id = t.sms_id where 1=1
and t.dlr_mask not in (1,2,16) -- waiting
-- and smsc_id='mts'
order by create_time desc,sql_id desc limit 25;

*/


/*

--
-- save existing data about stale SMS
--
drop table if exists tmp_stale;
create table tmp_stale as select * from sms_track where stale = 1;
select count(*) from tmp_stale;
select * from tmp_stale;

--
-- re-compute sms_track data based on sent_sms data and saved stale SMS data
--
insert into sms_track (dlr_url,sms_id,dlr_mask,stale,create_time,update_time,valid_time)
-- create table tmp_check as
select 
i.dlr_url,
ifnull(t.sms_id,  i.sql_id)   sms_id,
ifnull(t.dlr_mask,s.dlr_mask) dlr_mask,
ifnull(t.stale,0) stale,
ifnull(t.create_time,i.create_time) create_time,
ifnull(t.update_time,i.update_time) update_time,
ifnull(t.valid_time, i.valid_time) valid_time
from (
select dlr_url,
max(sql_id) sql_id,
from_unixtime(min(time)) create_time,
from_unixtime(max(time)) update_time,
from_unixtime(min(time) + ifnull(max(validity),1080)*60) valid_time
from sent_sms 
where sms_type in (2,3) and dlr_url is not null and time is not null
group by dlr_url
) i
join sent_sms s on s.sql_id = i.sql_id
left join tmp_stale t on t.dlr_url = i.dlr_url
;

*/

/* show SMS completion stats */

-- only for those messages which processing ended during last round 5 minutes (10:05-10:10 and so on)
-- processing delay is shown as ONE historically first value not exceeding 1 hour

select
count(*) attempt,
count(case when dlr_mask = 1 then 1 else null end) success,
count(case when dlr_mask !=1 then 1 else null end) failure,
ifnull(substring_index(group_concat(case when dlr_mask = 1 and delay < 3600 then delay else null end),',',1),'') delay
from (
 select sms_id,create_time,update_time,unix_timestamp(update_time)-unix_timestamp(create_time) delay,dlr_mask
 from sms_track
 where update_time >= from_unixtime(floor(unix_timestamp(now())/300)*300) - interval 5 minute
 and update_time < from_unixtime(floor(unix_timestamp(now())/300)*300)
-- where update_time >= '2014-10-10 16:45:00' and update_time < '2014-10-10 16:50:00'
 and dlr_mask in (1,2,16) -- 1-success, 2-fail, 16-expired (SMSC fail)
) s
;

/* show SMS wait stats */
select count(*) wait_count,round(ifnull(avg(elapsed),0)) avg_elapsed from (
select sql_id,smsc_id,sender,receiver,t.dlr_mask,stale,create_time,
unix_timestamp(now())-unix_timestamp(update_time) elapsed,
unix_timestamp(valid_time)-unix_timestamp(now()) remains
from sms_track t left join sent_sms s on s.sql_id = t.sms_id where 1=1
and t.dlr_mask not in (1,2,16) -- waiting
-- and smsc_id='mts'
) s
where elapsed >= 60 and elapsed < 3600 -- only elapsed time between 1 minute and 1 hour
;

--
select now();

-- show messages that are stale
select * from sms_track where dlr_mask not in (1,2,16) and valid_time <= now() - interval 1 minute;

-- mark stale messages
update sms_track set dlr_mask = 16,stale = 1,update_time=now() where dlr_mask not in (1,2,16) and valid_time <= now() - interval 1 minute;

/* clean up SMS data track */

--
select now();

-- clean up SMS track
delete from sms_track where update_time <= now() - interval 100 day;

-- clean up sent sms
delete from sent_sms where time <= unix_timestamp(now() - interval 100 day);

-- clean up dlr table from stale SMS data
select d.* from dlr d left join sms_track t on t.dlr_url = d.url and t.stale = 0 where t.dlr_url is null;
delete from dlr where url in (select url from (select d.* from dlr d left join sms_track t on t.dlr_url = d.url and t.stale = 0 where t.dlr_url is null) s);