Ozeki NG SMS Gateway logo OZEKI NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozeki.hu
SMS Gateway to send SMS via GSM modem or IP SMS connection Overview SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Quick start SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Download SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Manual SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection How to buy SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection FAQ SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Contact Us SMS Gateway to send SMS via GSM modem or IP SMS connection
OZEKI NG SMS Gateway - Product Guide

Keywords Contents | SearchOutlook Spy

Home > Product Manual > User Guide > Configuration > SQL logging > Advanced traffic queries

SMS Gateway SMS Gateway Home

  Product Manual
  Introduction
  SMS technology
  Installation Guide
  User Guide
  Quick Start Guide
  User Interface
  Configuration
  Inbound Routing
  Outbound routing
  SQL logging
  MySQL table layout
  Oracle table layout
  MSSQL table layout
  SQL templates
  SQL log tracking
  Keywords
  Advanced traffic queries
  Source routing
  Sender address
  Content manipulation
  Charset handling
  List Management
  Client software
  Administrator's guide
  Developers Guide
  Examples and Solutions
  Appendix
  FAQ
  Feature list
  Commercial Information
  Search
 


Automated phone calls?
Ozeki Phone System XE VoIP PBX software is an advanced PBX built for automated voice calls and 2 way SMS messaging. It has outstanding APIs for software developers. It can be used for:

Phone calls from SQL
Phone calls from HTTP
Voice and SMS applications

Callcenter developers
If you are working on telephone solutions, please check out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software.

Contact Us!
If you wish to get further information, do not hesitate to contact us!

E-mail: info@ozekisms.com

If you have a technical question, please submit a support request on-line.

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.

Video: How to query the SMS traffic of a 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 VODEFONE. 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):

mysql –u ozekiuser –p

-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):

use + database name

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: Outlook Spy

Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Product Manual > User Guide > Configuration > SQL logging > Advanced traffic queries
Legal | Privacy policy | Terms of use
Page: 2712 | Login | 18.205.109.82