Re: logging messages to sqlbox

It's all correct, dlr table is only used to store temporarily items which
didn't receive DLR yet.

You need to check sent_sms table for archived DLR / MT / MO entries.

2012/8/6 Clay Zevely <[email protected]>

> I have reinstalled kannel via svn. Then installed sqlbox.****
>
> ** **
>
> I have created two separate configs.****
>
> Smskannel.conf and sqlbox.conf.****
>
> ** **
>
> ** **
>
> I have injected an sms via smsc and it is making to to the kannel however
> I do npt see it logging in the database.****
>
> I am sure I must have something configured incorrectly however I am at a
> loss what or where.****
>
> I have included sections of the configs and logs below do see the messages
> in  the kannel.store.bak file and can attach that if needed,****
>
> How can I get the messages to log to the database?****
>
> ** **
>
> If I connect to mysql and select * from dlr I get ****
>
> mysql> select * from dlr;****
>
> Empty set (0.00 sec)****
>
> ** **
>
> mysql>****
>
> ** **
>
> ** **
>
> Clay****
>
>
> ___________________________________________________________________________________________________________________________________________________
> ****
>
> ** **
>
> My smkannel.cfg is below****
>
> ** **
>
> #CORE****
>
> group = core****
>
> admin-port = 13000****
>
> smsbox-port = 13001****
>
> admin-password = hostin****
>
> status-password = hostin****
>
> admin-allow-ip = "*.*.*.*"****
>
> wdp-interface-name = "*"****
>
> log-file = "/var/log/kannel/bearerbox.log"****
>
> store-file = "/var/log/kannel/kannel.store"****
>
> log-level = 0****
>
> box-deny-ip = "*.*.*.*"****
>
> box-allow-ip = "*.*.*.*"****
>
> dlr-storage=mysql****
>
> ** **
>
> #SMSBOX SETUP****
>
> group = smsbox****
>
> bearerbox-host = localhost****
>
> sendsms-port = 13013****
>
> sendsms-chars = "0123456789 +-"****
>
> global-sender = 12345****
>
> log-file = "/var/log/kannel/smsbox.log"****
>
> log-level = 0****
>
> ** **
>
> # SEND-SMS USERS****
>
> group = sendsms-user****
>
> username = test****
>
> password = hostin****
>
> ** **
>
> group = mysql-connection****
>
> id = sqlbox-db****
>
> host = localhost****
>
> username = root****
>
> password = "hostin"****
>
> database = delivery****
>
> max-connections = 10****
>
> ** **
>
> # DLR SETUP****
>
> #mysql connection****
>
> group = mysql-connection****
>
> id = mydlr****
>
> host = localhost****
>
> username = root****
>
> password = "hostin"****
>
> database = dlr****
>
> max-connections = 10****
>
> ** **
>
> group = dlr-db****
>
> id = mydlr****
>
> table=dlr****
>
> field-smsc=smsc****
>
> field-timestamp=ts****
>
> field-destination=destination****
>
> field-source=source****
>
> field-service=service****
>
> field-url=url****
>
> field-mask=mask****
>
> field-status=status****
>
> field-boxc-id=boxc****
>
> ** **
>
> # SMSC Fake****
>
> group = smsc****
>
> smsc = fake****
>
> port = 10000****
>
> connect-allow-ip = 127.0.0.1****
>
> ** **
>
> # A do-nothing service.****
>
> group = sms-service****
>
> keyword = nop****
>
> text = "You asked nothing and I did it!"****
>
> ** **
>
> # SMS SERVICE black-list****
>
> group = sms-service****
>
> keyword = black****
>
> text = "You are not allowed to use this service, Go away!"****
>
> ** **
>
> # SMSC SMPP****
>
> group = smsc****
>
> smsc = smpp****
>
> host = 208.52.112.246****
>
> port = 16400****
>
> receive-port = 16400****
>
> smsc-username = Track****
>
> smsc-password = Track****
>
> system-type = "VMA"****
>
> address-range = ""****
>
> ** **
>
> # SMS SERVICE Default****
>
> # there should be default always****
>
> group = sms-service****
>
> keyword = default****
>
> text = "No service specified. "****
>
> ** **
>
>
> _________________________________________________________________________________________________________________________________________________
> ****
>
> ** **
>
> my sqlbox.conf is below****
>
> group = sqlbox****
>
> id = mydlr****
>
> smsbox-id = smsbox****
>
> bearerbox-host = localhost****
>
> bearerbox-port = 13001****
>
> smsbox-port = 13005****
>
> sql-log-table = sent_sms****
>
> sql-insert-table = send_sms****
>
> log-file = "/var/log/kannel/kannel-sqlbox.log"****
>
> log-level = 0****
>
> ** **
>
> group = mysql-connection****
>
> id = mydlr****
>
> host = localhost****
>
> username = root****
>
> password = "hostin"****
>
> database = dlr****
>
> max-connections = 10****
>
>
> ____________________________________________________________________________________________________________________________________________________________
> ****
>
> ** **
>
> In bearerbox.log I see message****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG: SMPP PDU 0xa1d04d8 dump:****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   type_name: deliver_sm****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   command_id: 5 = 0x00000005****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   command_status: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   sequence_number: 34 = 0x00000022**
> **
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   service_type: NULL****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   source_addr_ton: 0 = 0x00000000***
> *
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   source_addr_npi: 0 = 0x00000000***
> *
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   source_addr: "17577230835"****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   dest_addr_ton: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   dest_addr_npi: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   destination_addr: "19038120026"***
> *
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   esm_class: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   protocol_id: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   priority_flag: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   schedule_delivery_time: NULL****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   validity_period: "120809115422016-"
> ****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   registered_delivery: 0 = 0x00000000
> ****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   replace_if_present_flag: 0 =
> 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   data_coding: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   sm_default_msg_id: 0 = 0x00000000*
> ***
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   sm_length: 47 = 0x0000002f****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   short_message:****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:    Octet string at 0xa1d0658:****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:      len:  47****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:      size: 48****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:      immutable: 0****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:      data: 73 6d 65 67 61 74 65 77
> 61 79 20 74 6f 20 73 6d   smegateway to sm****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:      data: 73 63 20 74 6f 20 6b 61
> 6e 6e 65 6c 20 76 69 61   sc to kannel via****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:      data: 20 73 6d 70 70 20 6d 65
> 73 73 61 67 65 20 31       smpp message 1****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:    Octet string dump ends.****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG: SMPP PDU dump ends.****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG: SMPP[SMPP:
> 208.52.112.246:16400/16400:Track:VMA]: Sending PDU:****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG: SMPP PDU 0xa1d06d0 dump:****
>
> 2012-08-06 11:54:22 [4714] [17] DEBUG: send_msg: sending msg to box:
> <127.0.0.1>****
>
> 2012-08-06 11:54:22 [4714] [17] DEBUG: boxc_sender: sent message to
> <127.0.0.1>****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   type_name: deliver_sm_resp****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   command_id: 2147483653 = 0x80000005
> ****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   command_status: 0 = 0x00000000****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   sequence_number: 34 = 0x00000022**
> **
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG:   message_id: NULL****
>
> 2012-08-06 11:54:22 [4714] [8] DEBUG: SMPP PDU dump ends.****
>
>
> _________________________________________________________________________________________________________________________________________________________
> ****
>
> ** **
>
> ** **
>
> My smsbox.log contains****
>
> 2012-08-06 11:43:38 [4748] [0] INFO: Added logfile
> `/var/log/kannel/smsbox.log' with level `0'.****
>
> 2012-08-06 11:43:38 [4748] [0] INFO: Service global sender set as '12345'*
> ***
>
> 2012-08-06 11:43:38 [4748] [0] INFO: HTTP: Opening server at port 13013.**
> **
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 1
> (gwlib/fdset.c:poller)****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 2
> (gwlib/http.c:server_thread)****
>
> 2012-08-06 11:43:38 [4748] [0] INFO: Set up send sms service at port 13013
> ****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 3
> (gw/smsbox.c:sendsms_thread)****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG:
> ----------------------------------------------****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Kannel smsbox version svn-r4985
> starting****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG:   dumping group (sendsms-user):****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG:     <group> = <sendsms-user>****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG:     <username> = <test>****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG:     <password> = <hostin>****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 4
> (gw/smsbox.c:obey_request_thread)****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 5
> (gw/smsbox.c:url_result_thread)****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 6
> (gw/smsbox.c:http_queue_thread)****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Connecting to <127.0.0.1>****
>
> 2012-08-06 11:43:38 [4748] [2] DEBUG: Thread 2
> (gwlib/http.c:server_thread) maps to pid 4748.****
>
> 2012-08-06 11:43:38 [4748] [2] DEBUG: HTTP: Including port 13013, fd 9 for
> polling in server thread****
>
> 2012-08-06 11:43:38 [4748] [3] DEBUG: Thread 3
> (gw/smsbox.c:sendsms_thread) maps to pid 4748.****
>
> 2012-08-06 11:43:38 [4748] [1] DEBUG: Thread 1 (gwlib/fdset.c:poller) maps
> to pid 4748.****
>
> 2012-08-06 11:43:38 [4748] [0] INFO: Connected to bearerbox at localhost
> port 13001.****
>
> 2012-08-06 11:43:38 [4748] [0] DEBUG: Started thread 7
> (gw/heartbeat.c:heartbeat_thread)****
>
> 2012-08-06 11:43:38 [4748] [6] DEBUG: Thread 6
> (gw/smsbox.c:http_queue_thread) maps to pid 4748.****
>
> 2012-08-06 11:43:38 [4748] [5] DEBUG: Thread 5
> (gw/smsbox.c:url_result_thread) maps to pid 4748.****
>
> 2012-08-06 11:43:38 [4748] [7] DEBUG: Thread 7
> (gw/heartbeat.c:heartbeat_thread) maps to pid 4748.****
>
> 2012-08-06 11:43:38 [4748] [4] DEBUG: Thread 4
> (gw/smsbox.c:obey_request_thread) maps to pid 4748.****
>
> 2012-08-06 11:54:22 [4748] [4] DEBUG: no match found: ^[ ]*(nop)[ ]*****
>
> 2012-08-06 11:54:22 [4748] [4] DEBUG: no match found: ^[ ]*(black)[ ]*****
>
> 2012-08-06 11:54:22 [4748] [4] INFO: Starting to service <smegateway to
> smsc to kannel via smpp message 1> from <17577230835> to <19038120026>****
>
> 2012-08-06 11:54:22 [4748] [4] DEBUG: formatted text answer: <No service
> specified. >****
>
> 2012-08-06 11:54:22 [4748] [4] DEBUG: message length 22, sending 1 messages
> ****
>
> 2012-08-06 11:54:22 [4748] [0] DEBUG: Got ACK (0) of
> 90f49a53-d296-48c2-a345-20f89563f325****
>
> 2012-08-06 11:54:22 [4748] [0] DEBUG: No client - multi-send or ACK to
> pull-reply****
>
> 2012-08-06 11:54:25 [4748] [4] DEBUG: no match found: ^[ ]*(nop)[ ]*****
>
> 2012-08-06 11:54:25 [4748] [4] DEBUG: no match found: ^[ ]*(black)[ ]*****
>
> 2012-08-06 11:54:25 [4748] [4] INFO: Starting to service <smegateway to
> smsc to kannel via smpp message 2> from <11111111111> to <19038120026>****
>
> 2012-08-06 11:54:25 [4748] [4] DEBUG: formatted text answer: <No service
> specified. >****
>
> 2012-08-06 11:54:25 [4748] [4] DEBUG: message length 22, sending 1 messages
> ****
>
> 2012-08-06 11:54:25 [4748] [0] DEBUG: Got ACK (0) of
> 83f37359-1749-4be2-8367-bdf36af8125f****
>
> 2012-08-06 11:54:25 [4748] [0] DEBUG: No client - multi-send or ACK to
> pull-reply****
>
> 2012-08-06 11:54:28 [4748] [4] DEBUG: no match found: ^[ ]*(nop)[ ]*****
>
> 2012-08-06 11:54:28 [4748] [4] DEBUG: no match found: ^[ ]*(black)[ ]*****
>
> 2012-08-06 11:54:28 [4748] [4] INFO: Starting to service <smegateway to
> smsc to kannel via smpp message 3> from <12222222222> to <19038120026>****
>
> 2012-08-06 11:54:28 [4748] [4] DEBUG: formatted text answer: <No service
> specified. >****
>
> 2012-08-06 11:54:28 [4748] [4] DEBUG: message length 22, sending 1 messages
> ****
>
> 2012-08-06 11:54:28 [4748] [0] DEBUG: Got ACK (0) of
> 74e5fd35-5227-4393-b740-85733efbc025****
>
> 2012-08-06 11:54:28 [4748] [0] DEBUG: No client - multi-send or ACK to
> pull-reply****
>
> 2012-08-06 11:54:32 [4748] [4] DEBUG: no match found: ^[ ]*(nop)[ ]*****
>
> 2012-08-06 11:54:32 [4748] [4] DEBUG: no match found: ^[ ]*(black)[ ]*****
>
> 2012-08-06 11:54:32 [4748] [4] INFO: Starting to service <smegateway to
> smsc to kannel via smpp message 4> from <13333333333> to <19038120026>****
>
> 2012-08-06 11:54:32 [4748] [4] DEBUG: formatted text answer: <No service
> specified. >****
>
> 2012-08-06 11:54:32 [4748] [4] DEBUG: message length 22, sending 1 messages
> ****
>
> 2012-08-06 11:54:32 [4748] [0] DEBUG: Got ACK (0) of
> 2da940ff-ba5d-4753-91d1-02b829ccf2d9****
>
> 2012-08-06 11:54:32 [4748] [0] DEBUG: No client - multi-send or ACK to
> pull-reply****
>
> 2012-08-06 11:54:35 [4748] [4] DEBUG: no match found: ^[ ]*(nop)[ ]*****
>
> 2012-08-06 11:54:35 [4748] [4] DEBUG: no match found: ^[ ]*(black)[ ]*****
>
> 2012-08-06 11:54:35 [4748] [4] INFO: Starting to service <smegateway to
> smsc to kannel via smpp message 5> from <14444444444> to <19038120026>****
>
> 2012-08-06 11:54:35 [4748] [4] DEBUG: formatted text answer: <No service
> specified. >****
>
> 2012-08-06 11:54:35 [4748] [4] DEBUG: message length 22, sending 1 messages
> ****
>
> 2012-08-06 11:54:35 [4748] [0] DEBUG: Got ACK (0) of
> 3ba6db01-d8e2-43eb-a927-57de74f99a47****
>
> 2012-08-06 11:54:35 [4748] [0] DEBUG: No client - multi-send or ACK to
> pull-reply****
>
>
> ______________________________________________________________________________________________________________________________________________________
> ****
>
> Sqlbox.log has****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: Added logfile
> `/var/log/kannel/kannel-sqlbox.log' with level `0'.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: Connected to server at
> localhost.****
>
> 2012-08-06 11:43:44 [4760] [0] INFO: MYSQL: server version 5.1.61, client
> version 5.1.61.****
>
> 2012-08-06 11:43:44 [4760] [0] DEBUG: Started thread 1
> (sqlbox.c:sql_to_bearerbox)****
>
> 2012-08-06 11:43:44 [4760] [1] DEBUG: Thread 1 (sqlbox.c:sql_to_bearerbox)
> maps to pid 4760.****
>
> 2012-08-06 11:43:44 [4760] [1] DEBUG: Connecting to <127.0.0.1>****
>
> 2012-08-06 11:43:44 [4760] [1] INFO: Connected to bearerbox at localhost
> port 13001.****
>
> 2012-08-06 11:43:44 [4760] [1] DEBUG: Started thread 2
> (sqlbox.c:bearerbox_to_sql)****
>
> 2012-08-06 11:43:44 [4760] [2] DEBUG: Thread 2 (sqlbox.c:bearerbox_to_sql)
> maps to pid 4760.****
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>