sqlbox by default stores DLR, MT, MO in sent_sms messages after message is being sent or received.
for monitoring you might want to consider kannel monitor included in the latest svn package or writing it yourself 2012/10/22 Ravindra Gupta // Viva <ravin...@vivainfomedia.com> > Dear Spameden, > > We have configured sqlbox in one of our Linux servers, We require to store > MT and MO pdu in mysql database. > > How we can do for the same. > > Ravindra Gupta B: +91.22.6785 6785 Server AdministratorD: +91.22.6785 > 6754 ravin...@vivaconnect.in M: +91.98199 92242 www.vivaconnect.in > Blog <http://blog.vivaconnect.in/> > LinkedIn<http://www.linkedin.com/company/viva-infomedia-pvt-ltd> > Twitter <https://twitter.com/vivaconnect1> > Facebook<https://www.facebook.com/pages/VivaConnect/181323090987> > YouTube<https://www.youtube.com/watch?v=MoLPYhTvNlg&feature=player_embedded> > > *Viva Infomedia Pvt. Ltd,* 242, Oshiwara Industrial Centre, New Link > Road, Goregaon (West), Mumbai- 400104. > > DISCLAIMER: This e-mail and any files transmitted with it are for the sole > use of the intended recipient(s) and may contain confidential and > privileged information. If you are not the intended recipient, please > contact the sender by reply e-mail and destroy all copies and the original > message. Any unauthorized review, use, disclosure,dissemination, > forwarding, printing or copying of this email or any action taken in > reliance on this e-mail is strictly prohibited and may be unlawful. The > recipient acknowledges that Viva Infomedia Pvt.Ltd. or its subsidiaries and > associated companies are unable to exercise control or ensure or guarantee > the integrity of/over the contents of the information contained in e-mail > transmissions and further acknowledges that any views expressed in this > message are those of the individual sender and no binding nature of the > message shall be implied or assumed unless the sender does so expressly > with due authority of Viva Infomedia Pvt.Ltd. Before opening any > attachments please check them for viruses and defects. > > > > On Sat, Oct 13, 2012 at 8:11 AM, spameden <spame...@gmail.com> wrote: > >> There is no "WEB panel" for monitoring SQLBox, but you can code one, it's >> very easy I think. >> >> Right now I'm using this code just to monitor what happens with kannel >> via the WEB. >> >> It gets current pending queue on multiple sqlbox'es and displays kannel's >> status page. You can extend it to allow kannel's different commands. >> >> Here is what I'm using so you'll get an idea: >> >> <? >> require_once('class_mysql.php'); >> include_once('curl.class.php'); >> $DB=NEW DB_MYSQL('kannel_db', 'kannel_user', 'password', 'localhost'); >> $DB->query("show tables like 'send_sms%'"); >> $tables = $DB->collect("Tables_in_kannel (send_sms%)"); >> $DB->free_result(); >> >> foreach($tables as $i => $table) { >> $DB->query("SELECT count(*) AS cnt FROM `".$table."` WHERE >> time<=UNIX_TIMESTAMP(NOW())"); >> $DB->free_result(); >> $result[$i]['count'] = $DB->next_record('cnt'); >> $result[$i]['name'] = $table; >> } >> $DB->close(); >> $get = new cURL(); >> ?> >> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> >> <html> >> <title>Kannel Status</title> >> <body> >> Current Pending Queue:<br> >> <? foreach($result as $tmp) { ?> >> <?=$tmp['name']?>: <?=(is_null($tmp['count']) ? >> 0:'<b>'.$tmp['count'].'</b>')?><br> >> <? } >> >> ?> >> <pre> >> <?=$get->get('http://127.0.0.1:13000/status')?> >> </pre> >> </body> >> </html> >> >> >> >> 2012/8/29 Ravindra Gupta // Viva <ravin...@vivainfomedia.com> >> >>> Dear Rene >>> >>> We have successfully configured SQLBOX on our Linux server, I have some >>> query as given below. >>> >>> 1) Any web panel in SQLBOX where we will be Monitored SMS bulk queue >>> like kannel. >>> >>> >>> On Wed, Aug 8, 2012 at 9:13 PM, Rene Kluwen <rene.klu...@chimit.nl>wrote: >>> >>>> Sorry… Now I see what is happening.**** >>>> >>>> You should comment out line 597 of the original gw/sqlbox.c as well.*** >>>> * >>>> >>>> ** ** >>>> >>>> == Rene**** >>>> >>>> ** ** >>>> >>>> *From:* Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] >>>> *Sent:* Wednesday, 08 August, 2012 15:51 >>>> *To:* Rene Kluwen >>>> *Cc:* us...@vm1.kannel.org >>>> *Subject:* Re: dlr-url call on sqlbox**** >>>> >>>> ** ** >>>> >>>> ** ** >>>> >>>> Dear Rene,**** >>>> >>>> ** ** >>>> >>>> We have commented the code in sqlbox.c file and found the row in >>>> sent_sms table.**** >>>> >>>> ** ** >>>> >>>> */* if (msg_type(msg) == sms) {***** >>>> >>>> * debug("sqlbox", 0, "smsbox_to_bearerbox: sms received");** >>>> *** >>>> >>>> * msg_escaped = msg_duplicate(msg);***** >>>> >>>> * gw_sql_save_msg(msg_escaped, octstr_imm("MT")); ***** >>>> >>>> * msg_destroy(msg_escaped);***** >>>> >>>> *} */***** >>>> >>>> ** ** >>>> >>>> */* if (msg_type(msg) == sms) {x", 0, "smsbox_to_bearerbox: sms >>>> received");***** >>>> >>>> * msg_escaped = msg_duplicate(msg);***** >>>> >>>> * gw_sql_save_msg(msg_escaped, octstr_imm("MT")); ***** >>>> >>>> * msg_destroy(msg_escaped);***** >>>> >>>> * }***** >>>> >>>> **/***** >>>> >>>> ** ** >>>> >>>> *mysql**> select count(*) from sent_sms;* >>>> >>>> *+----------+* >>>> >>>> *| count(*) |* >>>> >>>> *+----------+* >>>> >>>> *| 10 | * >>>> >>>> *+----------+* >>>> >>>> *1 row in set (0.00 sec)* >>>> >>>> * * >>>> >>>> ** ** >>>> >>>> ** ** >>>> >>>> On Wed, Aug 8, 2012 at 6:14 PM, Rene Kluwen <rene.klu...@chimit.nl> >>>> wrote:**** >>>> >>>> Hello Ravindra,**** >>>> >>>> **** >>>> >>>> 1. The sent_sms table is a log table. It records all smsmessages >>>> that have been sent, either through a connected >>>> smsbox or via an insert in send_sms. Entries are never deleted. If you >>>> don’t need a log of your sms messages, you can comment out the line >>>> “gw_sql_save_msg(msg, octstr_imm("MT"));” in the gw/sqlbox.c file.**** >>>> >>>> 2. If you don’t need them, you can just delete everything from >>>> the sent_sms table, for instance in a cron table. Or otherwise, see 1.* >>>> *** >>>> >>>> 3. sqlbox doesn’t manage an internal queue. If you need >>>> information about the sqlbox queue size, just apply a “select count(*) >>>> from send_sms” and it will give you the number of messages still in the >>>> queue.**** >>>> >>>> 4. There’s several options. If you don’t need a log of the >>>> messages that are sent, comment out the line that saves sent messages (see >>>> 1.). Also, if you want to increase speed of inserting messages in send_sms, >>>> you can look at the mysql syntax “INSERT… DELAYED”. This is what your >>>> client program should do. Another thing is to adjust the >>>> SQLBOX_MYSQL_SELECT_QUERY query in gw/sql_mysql.h. You can increase >>>> LIMIT 0,1 to a bigger value. I never bothered to test this out on high >>>> volumes. So please feel free to test and share your experiences in the >>>> list. >>>> Also there’s a number of things you can do to increase mysql speed, >>>> which is beyond the scope of this mailinglist.**** >>>> >>>> **** >>>> >>>> **** >>>> >>>> Hope this helps,**** >>>> >>>> **** >>>> >>>> Rene**** >>>> >>>> **** >>>> >>>> *From:* Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] >>>> *Sent:* Wednesday, 08 August, 2012 09:34**** >>>> >>>> >>>> *To:* Rene Kluwen >>>> *Cc:* us...@vm1.kannel.org >>>> *Subject:* Re: dlr-url call on sqlbox**** >>>> >>>> **** >>>> >>>> Dear Rene,**** >>>> >>>> **** >>>> >>>> While working on sqlbox, I have come across some queries :- **** >>>> >>>> 1. When we insert on send_sms table, it invokes and sends it to >>>> sent_sms which further submits it to the Operator and it deletes the >>>> entry >>>> from send_sms. However when we receive the deliver_sm from operator, it >>>> doesn't >>>> deletes the entry from sent_sms table.**** >>>> 2. If the above scenario continues it would increase the size of >>>> sent_sms table and will affect the mysql performance.**** >>>> 3. How we will manage queue in sqlbox, is there any patch available >>>> which will give us the queue related information.**** >>>> 4. How to increase insertion speed to send_sms tables or sent_sms >>>> table. **** >>>> >>>> ** ** >>>> >>>> On Wed, Aug 1, 2012 at 6:24 PM, Rene Kluwen <rene.klu...@chimit.nl> >>>> wrote:**** >>>> >>>> I don’t understand quite what you mean with your questions.**** >>>> >>>> But to answer:**** >>>> >>>> **** >>>> >>>> 1. Yes, you can send bulk sms through smsbox. It should work >>>> better than sending them via smsbox.**** >>>> >>>> 2. Performance issues are dependant of a number of things. Check >>>> the complete chain (smsbox -> sqlbox -> mysql -> bearerbox -> smsc). >>>> Usually sqlbox is not the cause.**** >>>> >>>> **** >>>> >>>> == Rene**** >>>> >>>> **** >>>> >>>> *From:* Ravindra Gupta // Viva [mailto:ravin...@vivainfomedia.com] >>>> *Sent:* Wednesday, 01 August, 2012 12:29 >>>> *To:* Rene Kluwen >>>> *Cc:* us...@vm1.kannel.org**** >>>> >>>> >>>> *Subject:* Re: dlr-url call on sqlbox**** >>>> >>>> **** >>>> >>>> **** >>>> >>>> Dear Rene,**** >>>> >>>> **** >>>> >>>> Thanks for your response.**** >>>> >>>> **** >>>> >>>> We have successfully configured SQLBOX in our server.**** >>>> >>>> **** >>>> >>>> We need some query as given below.**** >>>> >>>> **** >>>> >>>> 1. Can we send bulk load using SQLBOX i.e ( 5 to 10 Lac sms).**** >>>> 2. SQLBOX totally on database so any performance issue occurred >>>> when we have inserted 1 Lac to 10 Lac sms.**** >>>> 3. We have used mysql 5.5 version.**** >>>> >>>> Kindly share your expert sugesstion for the same.**** >>>> >>>> **** >>>> >>>> On Wed, Aug 1, 2012 at 3:05 PM, Rene Kluwen <rene.klu...@chimit.nl> >>>> wrote:**** >>>> >>>> The error is self-explanatory.**** >>>> >>>> You should just dlrencode your parameters in the dlr-url. Not the url >>>> itself.**** >>>> >>>> **** >>>> >>>> So insert: >>>> http://10.0.0.1/sms/dlrNEW.php?dlr=%25d%26to%3D%25p%26smsID%3D%25A%26BatchID%3Dravi123-5d12d21asdq1asravi >>>> **** >>>> >>>> **** >>>> >>>> == Rene**** >>>> >>>> **** >>>> >>>> **** >>>> >>>> **** >>>> >>>> ** ** >>>> >>> >>> >> >
