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.