Report and log files

Report and log files SearchSearch
Author Message
olivier
New member
Username: My64

Post Number: 1
Registered: 04-2011
Posted on Monday, April 11, 2011 - 03:03 pm:   

About "DaysToKeepLogFile" option, the documentation says: "This setting specifies the number of days to keep log files. The default value is 90 days. "

Could you please confirm that all old log files are automatically deleted ?

Regarding "REPORTS\LOGDB" files, is there any automatic old file deletion after one month ? If not, is there any recommendation to automatically remove those files ?
Thanks
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 3091
Registered: 08-2008
Posted on Monday, April 11, 2011 - 09:01 pm:   

Hi Olivier,

Yes, old log files are automatically deleted after this time period expires (and the default is 90 days).

The reports (REPORTS\TEXT and REPORTS\XML) are not automatically deleted. (And they are relatively small files.)

However, the databases used to generate the reports are automatically deleted consistent with the "DaysToKeepLogFile" option. They take slightly longer than the "DaysToKeepLogFile" setting, however, because each database is for an entire month. It gets automatically deleted "DaysToKeepLogFile" days after the end of the month.

--
Des
NowSMS Support
olivier
New member
Username: My64

Post Number: 2
Registered: 04-2011
Posted on Wednesday, April 13, 2011 - 04:55 pm:   

Thanks.
Is there a way to limit data volume stored in LOGDB ?
Is there an API to access LOGDB datas and if yes, what informations can be retrieved ?
Best Regards
Des - NowSMS Support
Board Administrator
Username: Desosms

Post Number: 3098
Registered: 08-2008
Posted on Wednesday, April 13, 2011 - 10:18 pm:   

Hi Olivier,

There is no way to limit the size. If they are too large for your installation, the only option is to disable the reporting, or set a lower DaysToKeepLogFile value.

The LOGDB files contain a record of all transactions, similar to the LOG files. Basically every record that was in a LOG file gets duplicated in the DB file.

As I understand it, this is an SQLITE database, and sqlite3.exe gets installed with NowWAP.

So you can run sqlite3 201103.db to open the database and send queries.

Unfortunately, I don't have any information on the schema and indexes. I think it is easy to query the database schema of one of these databases, but I'm not an XML expert.

I will try to get one of my colleagues to give me some additional information to post later this week.

--
Des
NowSMS Support
olivier
New member
Username: My64

Post Number: 3
Registered: 04-2011
Posted on Thursday, April 28, 2011 - 01:41 pm:   

Hi,
Any news about the schema and indexes for LOGDB ?
Best regards
Bryce Norwood - NowSMS Support
Board Administrator
Username: Bryce

Post Number: 7951
Registered: 10-2002
Posted on Friday, April 29, 2011 - 12:40 am:   

Hi Olivier,

Unfortunately Des has been very busy testing new updates.

I will look into this for you tomorrow. At a minimum, I should be able to get you the queries that we use to generate the usage summary reports, which should be a good starting point,

-bn
Bryce Norwood - NowSMS Support
Board Administrator
Username: Bryce

Post Number: 7954
Registered: 10-2002
Posted on Friday, April 29, 2011 - 01:58 am:   

Hi Olivier,

A quicker follow-up than expected this time. I managed to get my hands on the code that converts the text log files into a database.

You can query these databases using SQLITE 3.


Commands used to create database:

CREATE TABLE WAPGWLOG (AUTOID INTEGER PRIMARY KEY AUTOINCREMENT, RequestID text, RequestIP text, MSISDN text, RequestDate text, RequestTime text, RequestType text, RequestServer text, RequestDomain text, RequestURL text, HTTPStatus text, BytesOut integer, BytesIn integer, UserAgent text);

create index RequestID on WAPGWLOG (RequestID);
create index RequestDate on WAPGWLOG (RequestDate);
create index MSISDN on WAPGWLOG (MSISDN);
create index RequestDomain on WAPGWLOG (RequestDomain);
create index RequestServer on WAPGWLOG (RequestServer);
create index RequestType on WAPGWLOG (RequestType);
create index MSISDNByDate on WAPGWLOG (RequestDate,MSISDN);
create index RequestDomainByDate on WAPGWLOG (RequestDate,RequestDomain);
create index RequestServerByDate on WAPGWLOG (RequestDate,RequestServer);
create index RequestTypeByDate on WAPGWLOG (RequestDate,RequestType);

More information about database fields:

RequestID = auto generated ID for request
RequestIP = IP address of device
MSISDN = MSISDN of device
RequestDate format = YYYYMMDD
RequestTime format = HHMMSS
RequestType - GET, POST, CONNECT, DISCONNECT, STARTSERVICE, STOPSERVICE
RequestServer - Host name part of URL
RequestDomain - Host name parsed to higher level domain name
HTTPStatus - HTTP Status code of request (e.g, 200=OK, 301=Redirect, 304=Not Modified, 404=Not Found, etc.)
BytesOut, BytesIn - Number of bytes transferred to/from device
UserAgent - User-agent header from device making request

SQL Commands used to generate information in summary report:

Total Unique MSISDN
select count(*) from (select MSISDN from WAPGWLOG where (RequestDate = '%s') and (MSISDN != '') group by MSISDN);

Total Sessions
select count(*) from WAPGWLOG where (RequestDate = '%s') and (RequestType = 'CONNECT') and (RequestURL = '');

Total Requests
select count(*) from WAPGWLOG where (RequestDate = '%s') and (RequestType != 'STARTSERVICE') and (RequestType != 'STOPSERVICE');

Top Active Users
select MSISDN,count(*) from WAPGWLOG where (RequestDate = '%s') and (MSISDN != '') group by MSISDN order by count(*) DESC limit 100;

Top Content Domains
select RequestDomain,count(*) from WAPGWLOG where (RequestDate = '%s') and (RequestDomain != '') group by RequestDomain order by count(*) DESC limit 100;

Top Content Servers
select RequestServer,count(*) from WAPGWLOG where (RequestDate = '%s') and (RequestServer != '') group by RequestServer order by count(*) DESC limit 100;

-bn

Add Your Message Here, or click here to start a new topic.
Post:
Bold text Italics Underline Create a hyperlink Insert a clipart image
Options: Automatically activate URLs in message
Action: