CartoWeb Accounting

Accounting is available since CartoWeb from date 2020-02-23.

See documentation here: http://www.cartoweb.org/doc_head/docbook/xhtml/user.accounting.html

Patch to Store IP Addresses for CartoWeb Versions Before 2020-03-23

Available here:

accounting_ip.diff

Instructions for applying it:

cd cartoweb3
wget -O accounting_ip.diff 'http://www.cartoweb.org/cwiki/Accounting?action=AttachFile&do=get&target=accounting_ip.diff'
patch -p0    < accounting_ip.diff

What information is stored

log name	description	since version
General plugin version:1 (2020-02-23:0, 2020-03-23:1)
export_plugin	Name of the export plugin used, for instance exportHtml, exportPdf	0
browser_info	Information about the browser, like Java support, window size, number of colors, ...	0
mapid	Mapfile identifier, for instance test_main.test	0
time	Unix time when the request is made	0
ip	Ip address of client (works behind proxy / Security Proxy)	1
ua	User agent string of the client browser	0
sessid	Session identifier	0
direct_access	1 if direct access mode is enabled	0
request_id	Internal use	0
security.user	Current logged in user identifier	0

elapsed_time	Time taken for request on server	0
cache_id	Internal use for cache management	0
cache_hit	Internal use for cache management	0
ExportPdf plugin version:1 (2020-02-23:1)
format	Format name, for instance A4	1
resolution	Resolution, for example 72	1
Images plugin version:0 (2020-02-23:0)
mainmap.width	Mainmap width	0
mainmap.height	Mainmap height	0
Layers plugin (server) version:1 (2020-02-23:1)
layers	Coma sepearated list of selected layers	0
switch_id	The selected switch id	1
Layers plugin (client) version:0 (2020-02-23:0)
visible_layers	Coma sepearated list of visible layers (the selected layers witch are not out-of-scale)	0
Location plugin version:0 (2020-02-23:0)
bbox	Current bbox of request	0
scale	Scale of the map	0
Query plugin version:1 (2020-02-23:1)
results_count	Number of results found	1
results_table_count	Number of results for each layers, for instance POLYGON1=1;some_rectangles=4"	1

Importing the log files into the database

Use the manage_logs.py script from the scripts/accounting directory in CartoWeb.

Usage example:

1) First create the tables:

DSN="dbname=accounting user=my_user password=my_password" python manage_logs.py -gen-schema

2) Then, populate the tables with the log files:

DSN="dbname=accounting user=my_user password=my_password" python manage_logs.py -import /path/to/accounting

DSN contains the information for connecting to the database. In the database, three table will be created: stats, stats_client and stats_server. The resulting data will be stored in the stats table.

/path/to/accounting must be a directory where you can find the "project.mapid/*log" files. Inside of CartoWeb, it can be found in "cartoweb3/www-data/accounting".

You can have some basic statistics by invoking:

DSN="dbname=accounting user=my_user password=my_password" python manage_logs.py -raw_stats

Performance information:

on a P4 2.8Ghz, a 39697 lines log file is processed in 1m52. That means ~350 lines/seconds.

Random notes: getting the percentage of incorrect values (because of the cached entry present in a previous log file):

select (select count (1)::float from stats where images_mainmap_height is null) / (select count (1)::float from stats);

SQL commands for analysing stats

Logs from date A to date B : SELECT min (general_time), max (general_time) FROM stats;

Number of maps diplayed by month: SELECT extract(month from general_time), count(*) FROM stats GROUP BY extract(month from general_time);

Count maps in different mapsizes: SELECT images_mainmap_height, images_mainmap_width, COUNT(*) AS count FROM stats GROUP BY images_mainmap_height, images_mainmap_width HAVING count(*) > 100 ORDER BY images_mainmap_height ASC;

Count maps in different mapsize for january: SELECT images_mainmap_height, images_mainmap_width, COUNT(*) AS count FROM stats GROUP BY images_mainmap_height, images_mainmap_width, extract(month from general_time) HAVING count(*) > 100 AND extract(month from general_time) = 1 ORDER BY images_mainmap_height ASC;

Count maps in different mapsizes per month for 2007: SELECT extract(month from general_time), images_mainmap_height AS height, images_mainmap_width AS width, images_mainmap_height * images_mainmap_width AS pixels, COUNT(*) AS count FROM stats GROUP BY images_mainmap_height, images_mainmap_width, extract(month from general_time), extract(year from general_time) HAVING count(*) > 100 AND extract(year from general_time) = 2007 ORDER BY date_part, images_mainmap_height ASC;

Count maps and pixels per month for 2007: SELECT substr(date(general_time),0,8) AS month, images_mainmap_height AS height,images_mainmap_width AS width, images_mainmap_height * images_mainmap_width AS pixels_per_map , COUNT(*) AS count_maps , images_mainmap_height * images_mainmap_width * COUNT(*) AS total_pixels FROM stats GROUP BY images_mainmap_height, images_mainmap_width, month , extract(year from general_time) HAVING count(*) > 10 AND extract(year from general_time) = 2007 ORDER BY month ASC, images_mainmap_height ASC, images_mainmap_width ASC;

Count maps and pixels per day for may 2007: SELECT extract(day from general_time) AS day, images_mainmap_height AS height, images_mainmap_width AS width, images_mainmap_height * images_mainmap_width AS pixels_per_map, COUNT(*) AS count_maps, images_mainmap_height * images_mainmap_width * COUNT(*) AS total_pixels FROM stats GROUP BY images_mainmap_height, images_mainmap_width, extract(day from general_time), extract(month from general_time), extract(year from general_time) HAVING count(*) > 10 AND extract(year from general_time) = 2007 AND extract(month from general_time) = 05 ORDER BY day ASC, images_mainmap_height ASC, images_mainmap_width ASC;

How many PDF exports? SELECT exportpdf_format, exportpdf_resolution, COUNT(*) FROM stats GROUP BY exportpdf_format, exportpdf_resolution;

How many pdf exports per month, size and resolution: SELECT extract(month from general_time) as month, exportpdf_format AS pdf_format, exportpdf_resolution AS pdf_resolution, COUNT(*) AS count_pdf FROM stats WHERE exportpdf_format is not null GROUP BY exportpdf_format, exportpdf_resolution, extract(month from general_time), extract(year from general_time) HAVING extract(year from general_time) = 2007 ORDER BY month ASC, exportpdf_format ASC, exportpdf_resolution ASC;