Support forum
Add Reply
Add Topic9:57 am
May 28, 2013
Offline
QuoteYes, that is the correct data for the MessageLog table.
Do you already receive this data in the MessageLog table of the SQL database? If not then I would like to see the configuration file of Diafaan SMS Server (menu items 'File-Export-Configuration') and the event log file (menu items 'File-Export-Event log'). You can send these files to email address dms@diafaan.com.
The Microsoft SQL Server database is easier to add to Diafaan SMS Server than SQL databases with an ODBC driver because for Microsoft SQL Server you can let Diafaan SMS Server create the database and fill in all the database field names automatically. This is not possible when you use a generic ODBC connection, for generic ODBC connections you have to create the database manually and also fill in the database field names manually.
Regards, Henk
6:35 pm
Hi Henk,
I apologize for multiple messages. I was just surprised that it was easier to setup MS SQL. Hahahaha! I get MessageIn, and I have content in MessageLog, as I have sent a text. This should be correct on MessageLog Table right?
Id SendTime ReceiveTime StatusCode StatusText MessageTo MessageFrom MessageText MessageType MessageId ErrorCode ErrorText Gateway MessageParts MessagePDU UserId UserInfo MessageGuid MessageInfo Connector
[* CONTENT REMOVED *]
5:10 pm
I already have created the tables and it seems only MessageIn is the only one getting data. It's weird that I don't get the MessageLog.
4:24 pm
May 28, 2013
Offline
On a MySQL or MariaDB database you have to create the database manually, you can find a description here. After setting up the database connection you can fill in additional database field names manually in the advanced settings of the SQL Connector.
The send log is stored in the MessageLog database table. There are a few more fields in the internal SQLite send log that are not stored in the external SQL database but these are mainly for internal use by Diafaan SMS Server.
Regards, Henk
1:51 pm
Hi Henk,
One more thing, i have now enabled the Log to rmeote DB, the question is, will this automatically create a new database there? or will it use this tables? or will create a new DB for all the new logs? I have set my db as diafaan_db with this set of tables. Because looking at the sqlite file, there are other columns that is not part of this set in the website. Thank you.
CREATE DATABASE SMSServer DEFAULT CHARACTER SET utf8mb4;
CREATE TABLE MessageOut (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
MessageTo VARCHAR(80),
MessageFrom VARCHAR(80),
MessageText TEXT,
MessageType VARCHAR(80),
MessageGuid TEXT,
MessageInfo TEXT,
Gateway VARCHAR(80),
UserId VARCHAR(80),
UserInfo TEXT,
Priority INT,
Scheduled DATETIME,
ValidityPeriod INT,
TLVList TEXT,
IsSent TINYINT NOT NULL DEFAULT 0,
IsRead TINYINT NOT NULL DEFAULT 0) CHARACTER SET utf8mb4;
CREATE INDEX IDX_IsRead ON MessageOut (IsRead);
CREATE TABLE MessageIn (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SendTime DATETIME,
ReceiveTime DATETIME,
MessageFrom VARCHAR(80),
MessageTo VARCHAR(80),
SMSC VARCHAR(80),
MessageText TEXT,
MessageType VARCHAR(80),
MessageParts INT,
MessagePDU TEXT,
Gateway VARCHAR(80),
UserId VARCHAR(80)) CHARACTER SET utf8mb4;
CREATE TABLE MessageLog (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SendTime DATETIME,
ReceiveTime DATETIME,
StatusCode INT,
StatusText VARCHAR(80),
MessageTo VARCHAR(80),
MessageFrom VARCHAR(80),
MessageText TEXT,
MessageType VARCHAR(80),
MessageGuid TEXT,
MessageInfo TEXT,
MessageId VARCHAR(80),
ErrorCode VARCHAR(80),
ErrorText TEXT,
Gateway VARCHAR(80),
MessageParts INT,
MessagePDU TEXT,
Connector VARCHAR(80),
UserId VARCHAR(80),
UserInfo TEXT) CHARACTER SET utf8mb4;
CREATE INDEX IDX_MessageId ON MessageLog (MessageId, SendTime);
11:14 am
May 28, 2013
Offline
Diafaan SMS Server only sends all of the logs to the SQL database when the 'Log all messages to SQL/Excel Connector ...' option in the advanced settings of the general option is set to the new SQL Connector. If this option is not set, then only the messages that originate from the SQL Connector will be logged in the SQL database.
MariaDB will work well with Diafaan SMS Server. I do not know if MySQL is easier or more difficult to set up with Diafaan SMS Server but I have good experience with the MariaDB SQL driver in combination with MySQL.
Regards, Henk
9:57 am
Hi Henk,
I now understood when you said General Options hahahahaha. I was thinking options of the Connector only. Hahahahah I have enabled it now and will let you know the status once I observe the changes are happening. Thank you very much.
9:48 am
Hi Henk,
Database is now setup and I am getting the MessageIn data coming in. I was wondering if all the other data from the other connectors can be logged by this database server. Is that possible? Can i redirect all database data to my remote server instead of the local sqlite db? All the help is appreciated. Thank you very much.
7:33 am
Hi Henk,
One last question, can I use mariadb or it would be more preferrable to use mySQL if i want to have less headache? Thank you.
6:37 am
Thank you very much Henk. will proceed in creating the connector.
9:52 am
May 28, 2013
Offline
Hello Em,
Yes, you can use an external SQL database to store all the logs of Diafaan SMS Server. First you have to create an SQL Connector and then you can set the 'Log all messages to SQL/Excel Connector ...' option in the advanced settings of the general options to the correct SQL Connector that stores all the log records.
Your observation that using the SQLite database to store all the logs will slow down Diafaan SMS Server is correct. I recommend to leave the setting of the maximum number of records in the log to the default value of 10000 records per log. Setting this value too high on a busy system can lead to delays or database corruption.
Regards, Henk
8:38 am
Hi Hank,
Good day. My Company's requirements have changed right now, and our MIS team would like to access the SQLite database. Unfortunately, since the SQLite database has no external port connections and is just file-based, is it possible to direct all logs to an external mySQL, PostGReSQL, MSSQL database? I have performance issues as the sqllite db file as it grows, the server tends to slow down. If possible how do I go about it? Thank you very much.
I have a full diafaan sms server license by the way. Thanks.
1 Guest(s)