How to query the SMS traffic of a user
This guide explains how to set the SQL reporting function and route
tables of Ozeki NG SMS Gateway to determine how many SMSs have been sent out by a user and
which service provider connection has been used for sending messages. Follow
the configuration guide below to setup this function successfully.
In Ozeki NG SMS Gateway you can assign credits to a user. In this way the amount
of messages that can be sent by one user is determined by the number of these credits.
However there are cases when messages are purchased from various service providers
with different prices. In these cases it is important to be able to follow
how many messages have been sent out and which service provider connections have
been used by users.
To determine how many messages are sent and which service provider connection is used
by users you need to do the follows: set the SQL reporting function and the outgoing
route tables in Ozeki NG SMS Gateway software.
For this purpose you need to specify the service provider connections exactly in the
outgoing route tables. It means that ANY cannot be used for determining the service
provider in the route table.
Service providers use various areacodes. In this way it is possible to specify
the route for SMS messages according to these areacodes. In the example below we
use two service provider connections and two users for demonstration. After configuration
we query how many messages have been sent via one service provider within
a specific period by a given user.
Configuration guide
It is assumed that you have already done the follows:
- Ozeki NG SMS Gateway has been installed to the PC
- A database server can be accessed from the PC or a database server is
installed on the PC on which Ozeki NG SMS Gateway runs. You have also set the
databases you wish to use (database of SQL report and the database for
Database user). In this example we use a MySQL server installed on the PC on which Ozeki
NG SMS Gateway runs
- The necessary ODBC driver through which you can connect to the database is
installed on the PC on which Ozeki NG runs
- You can connect to the service provider (you have already signed up for their service)
Step 1 - Start Ozeki NG SMS Gateway
To start configuration first start Ozeki NG SMS Gateway software (Figure 1).
Figure 1 - Start Ozeki NG
Log into Ozeki NG SMS Gateway with your
username and password (Figure 2).
Figure 2 - Login
Step 2 - Configure service provider connections
To add a new service provider connection click on
Add service provider (Figure 3). In this example we add two SMPP service provider connections
called "TMOBILE" and "VODAFONE" for demonstration.
Figure 3 - Add new service provider
To install an SMPP connection click on Install (Figure 4).
Figure 4 - Install
On the configuration panel of SMPP connection select SMPP settings tab
to specify the SMPP server settings. These parameters are provided by your service provider
(Figure 5). It is highly recommended to give an easily identifiable name for the created
service provider connections. It is important to be able to identify them in the
routing tables later.
Figure 5 - SMPP settings
You can check if the SMPP connection configuration is successful in
Events menu. If the configuration is correct you receive Connection online
response (Figure 6).
Figure 6 - Connection successful
On Figure 7 you can see the two installed service provider connections
called TMOBILE and VODAFONE. For a detailed guide on how to configure
SMPP service provider connection please visit SMPP configuration page.
Figure 7 - Sample service provider connections
Step 3 - Configure users you wish to use
Now we create two users (a standard user and a database user) to demonstrate how
this solution works in practice. To create a new user click on Add new user or application (Figure 8).
Figure 8 - Add new user or application
Click on Install next to the selected user (Figure 9).
Figure 9 - Install
Provide a name for the user (Figure 10).
Figure 10 - Provide a name
Specify a password for the standard user for login (Figure 11).
Figure 11 - Specify a password
Now we create a database user, as well, by clicking Add new user
or application (Figure 12).
Figure 12 - Add new user or application
Install the Database user (Figure 13).
Figure 13 - Install
Provide a name for this user. Please note: this name will also be
used to identify this user in the database. It is recommended to give an easily
identifiable name (Figure 14).
Figure 14 - Provide a name
On the Configuration panel of Database user select Database connection
tab. Select the Connection string type via which you want to connect to the database (e.g.: ODBC).
Then specify the Connection string (Figure 15).
Figure 15 - Database connection settings
If the configuration is correct you can see Database connection
successful response in Events (Figure 16). For more detailed guide on how to
configure Database user please visit Database user configuration page.
Figure 16 - Database connection successful
Step 4 - Configure outgoing route tables
For setting outgoing route tables click on Edit in the Outgoing
route section (Figure 17).
Figure 17 - Set outgoing routes
Delete the default route by clicking on Del (Figure 18).
This step is necessary because there cannot be any route where the service provider
is determined as ANY.
Figure 18 - Delete the default route
The program will ask if you are sure in deleting the default route.
Click on OK (Figure 19).
Figure 19 - Allow delete
Click on Add new route (Figure 20).
Figure 20 - Add new route
Now specify which service provider connection will be the destination (Figure 21). In this
example it is TMOBILE. With a regular expression you can also determine a condition for the
recipient phone number.
In this example we have specified a condition to send SMS messages
to the connection TMOBILE if the recipient phone number starts with +3630 or 0630 areacode. This areacode
belongs to TMOBILE Hungary.
In this way you can determine the areacode for the given service provider for sending
messages via this connection. Please note:
these areacodes are varied by countries and service providers. Please ask your service provider
about their areacodes!
Figure 21 - Specify the service provider
For adding another route click on Add new route (Figure 22).
Figure 22 - Add new route
In this example we set VODAFONE as the Destination of this outgoing route (Figure 23).
Here we also specify a condition to send messages to the connection VODAFONE if the recipient phone number starts with
+3670 or 0670 areacode. This areacode belongs to VODAFONE Hungary.
Please note:
these areacodes are varied by countries and service providers. Please ask your service provider
about their areacodes!
Figure 23 - Specify the service provider
Step 5 - Configure SQL reporting
To set SQL reporting go to Edit menu and select Server
preferences menu item (Figure 24).
Figure 24 - Server preferences
Click on Databases tab (Figure 25).
Figure 25 - Databases tab
Click on Logging database settings tab and check Enable SQL logging checkbox. Select the
Database connection type (in this example it is ODBC) and customize Database connection string (Figure 26).
For a more detailed guide on how to enable SQL logging please visit SQL logging page.
Figure 26 - Enable SQL logging
To check the database connection click on View menu and
select SQL logger events menu item (Figure 27).
Figure 27 - SQL logger events
If the configuration is correct you can see Database connection
successful response (Figure 28).
Figure 28 - Database connection successful
Now the configuration part is completed. In the follows you can
find a demonstration on how to query the sent messages from the system.
Figure 29 demonstrates that one user sent 5791 messages.
Figure 29 - Sent messages
Figure 30 demonstrates that another user sent 5690 messages.
Figure 30 - Sent messages
Step 6 - Use the respective commands in MySQL console to query data
As a next step open Run window. You can do so by pressing
the Windows button + R button on your keypad. Enter cmd and click on OK
(Figure 31).
Figure 31 - Run window
The Commandline of Windows starts. Now start MySQL console with the
following command (Figure 32):
-p is required if you use a password
ozekiuser is the username with which you connect to the database
Finally push Enter
Figure 32 - Start MySQL console
In case you use a password you need to type this password into the commandline
(Figure 33).
Figure 33 - Type your password
MySQL console starts. To use Database Report use the
following command (Figure 34):
Here you need to replace
the name of the database in which the report has been created.
In this example, the report has been created in ozekingreport database. In this way
we use the command:
use ozekingreport
It responds Database changed.
Figure 34 - Database report
First we query the total number of sent messages with the following
SQL command (Figure 35):
| select count(id) as �SMS count� from outbox; |
Figure 35 - Total number of sent messages
To query the total number of sent messages via one given service provider connection use
the following SQL command (Figure 36):
|
select count(id) as �SMS count� from outbox where operator=�operator_name�;
|
Figure 36 - Sent messages via one service provider connection
To query the total number of messages that have been sent by one
given user use the following SQL command (Figure 37):
|
select count(id) as �SMS count� from outbox where username=�user_name�;
|
Figure 37 - Sent messages by one given user
To query the total number of sent messages via one given service
provider connection by one given user, use the following SQL command (Figure 38):
|
select count(id) as �SMS count� from outbox where username=�user_name� and operator=�operator_name�;
|
Figure 38 - Sent messages via one connection by one user
To query the total number of sent messages via one given service
provider connection by one given user within a specific period, use
the following SQL command (Figure 39):
|
select count(id) as �SMS count� from outbox where username=�user_name� and
operator=�operator_name� and (acceptedfordeliverytime between �2011-03-09 16:25�
and �2011-03-09 17:00�;)
|
Figure 39 - Sent SMS via one connection by one user within a specific period
In conclusion...
With this solution you can follow the total number of sent messages
via a given service provider connection by a given user within a specific period
of time. In this way it is possible to use this solution for billing purposes. You can
create invoices based on the total number of sent messages within a given time
period. It is useful even in cases if the prices of sent SMS messages are varied
according to the price plans of the various service providers. If you
have any question please contact us at info@ozekisms.com
Dig deeper!
People who read this also read...
SQL logging
SMPP service provider configuration
Database user configuration
Next page:
Source routing
| 
