The log analysis db is an internal reporting tool, to analyze a newspaper website and give a statistics about kind of articles the site's readers like, using information from the website database.
This tool will connect automatically to a fixed database called news,
reading data mainly from 2 views (views_log, errors_log) then print in
plain text the following statistics:
1- The 3 most reading articles
2- The most popular article authors
3- The days did more than 1% of website requests lead to errors
The Views creation syntax as follows:
1- Firstly for more details about the current used database, please see the
How To run this tool/ the database section at the end of file.
2- Connect to the database from the database console by using \c followed
by the database name, for ex., \c news, or from the shell prompt by
using the command psql news.
3- Run the following two queries to create the needed Views by copy/ paste each one inside the database console then press Enter:
create view views_log as
select articles.id as article_l, articles.author as author_l
, log.path as path_l, log.ip as ip_l, log.method as method_l
, log.status as status_l, log.time as time_l, log.id as id_l
from log join articles
on log.path like '%' || articles.slug || '%'
where log.method like 'GET%' and log.status like '2%';
create view errors_log as
select articles.id as article_l, articles.author as author_l
, log.path as path_l, log.ip as ip_l, log.method as method_l
, log.status as status_l, log.time as time_l, log.id as id_l
from log left join articles
on log.path like '%' || articles.slug || '%'
where log.method like 'GET%'
and (log.status like '4%' or log.status like '5%');
How To run this tool:
1- Its a python program, needs the python standard libraries to be
installed on the pc, try this site https://www.python.prg/
2- The database connection to the news db is required, its a postgresql
database, created under a Linux VM, for more details:
https://youtu.be/djnqoEO2rLc
2- Finally the program can be run from the shell like (terminal on
Mac or Linux) using the command python3 loganalysisdb.py, from
git bash on Windows using python loganalysisdb.py, or just double
clicked on the file.