Log analysers: Sqstat configuration

Sqstat Configuration

Objectives:
1. Questions?
2. Concept
3. System requirements
4. Scenario
5. Configuration
6. Log files location
7. Troubleshooting
8. Reference
9. Real life-Script
10. Follow our vedio tutorial at the youtube link

Questions?
Q. What is Sqstat?
A: Sqstat is a web based real-time browsing Monitoring tool.

Q. Why we need it?
A. we need to monitor the current user's browsing logs (bandwidth, browsing site etc.) from the web server using web browser.

Q. How many packages are there like this?
A. There are many packages which genarete web log report but not in real-time report. At least I'm not aware of!!!

Concept
Sometimes we need to monitor the current browsing logs from the web server using web browser. Then one of our great solution is Sqstat. And trust me when I tell you this is a awsome package.

System requirements
. PHP (you need PHP 4.1.0 or newer) with installed pcre extension
. (optional) PHP session extension if you want to see current/average CPS
. Squid proxy server
. a web-browser

Scenario
Here my proxy server's ip address is 192.168.1.11 (10.0.0.1) and the local network is 10.0.0.0/24. And it is also transparent squid which listening on 8080 port. So, lets get start. And Don't afraid. Just walk with me, you will be smilling later.

Sqstat configuration
1. Sqstat is php package. So, we need to install php5 with apache2 to read it.
root@ns2:~# apt-get install apache2
root@ns2:~# apt-get install php5
root@ns2:~# apt-get install apache2-mpm-prefork
root@ns2:~# apt-get install libapache2-mod-php5
root@ns2:~# apt-get install apache2-mpm-worker

2. Install and configure squid. Here, we need to allow cachemgr protocol from localhost or from the server's ip address.
root@ns2:~# apt-get install squid
root@ns2:~# vim /etc/squid/squid.conf

# Squid normally listens to port 3128
http_port 8080 transparent

#Recommended minimum configuration:
acl all src all
acl manager proto cache_object
acl localhost src 127.0.0.1/32
acl to_localhost dst 127.0.0.0/8 0.0.0.0/32
acl mynetwork src 10.0.0.0/24
acl webserver src 192.168.1.11/255.255.255.255

# Only allow cachemgr access from localhost
http_access allow manager localhost
http_access allow manager webserver
http_access deny manager

# from where browsing should be allowed
#http_access allow localnet
http_access allow localhost
http_access allow mynetwork

3. Firewall (iptables) configuration.
root@ns2:~# vim /etc/init.d/iptables

# Assigning our local network
our_network="10.0.0.0/24"

# Redirect http to SQUID.
iptables -t nat -A PREROUTING -p TCP -s "$our_network" --dport 80 -j REDIRECT --to-port 8080
iptables -t nat -A PREROUTING -p TCP --dport 8080 -j REDIRECT --to-port 8080

4. sqstat installation and configuration from tar file.
root@ns2:~# wget http://samm.kiev.ua/sqstat/sqstat-1.20.tar.gz
root@ns2:~# tar -zxvf sqstat-1.20.tar.gz
root@ns2:~# mv sqstat-1.20 /var/www/
root@ns2:~# cd /var/www/
root@ns2:/var/www# mv sqstat-1.20 sqstat
root@ns2:/var/www# mv config.inc.php.defaults config.inc.php
root@ns2:/var/www# vim /var/www/sqstat/config.inc.php

<?php
/* global settings */
$use_js=true; // use javascript for the HTML toolkits

//===================== Change the following settings =====================//
// Maximum URL length to display in URI table column
DEFINE("SQSTAT_SHOWLEN",100);

/* proxy settings */
/* Squid proxy server ip address or host name */
#$squidhost[0]="127.0.0.1";
$squidhost[0]="192.168.1.11";

/* Squid proxy server port */
#$squidport[0]=3128;
$squidport[0]=8080;
//========================================================================//

/* cachemgr_passwd in squid.conf. Leave blank to disable authorisation */
$cachemgr_passwd[0]="";
/* Resolve user IP addresses or print them as numbers only [true|false] */
$resolveip[0]=false;
/* uncomment next line if you want to use hosts-like file.
   See hosts.txt.dist. */
// $hosts_file[0]="hosts.txt"
/* Group users by hostname - "host" or by User - "username". Username work only
   with squid 2.6+ */
$group_by[0]="host";
/* you can specify more than one proxy in the configuration file, e.g.: */
// $squidhost[1]="192.168.0.2";
// $squidport[1]=3129;
// $cachemgr_passwd[1]="secret";
// $resolveip[1]=true;
// $hosts_file[1]="otherhosts.txt"

?>

5. restart the squid and the web server.
root@ns2:~# /etc/init.d/squid restart
root@ns2:~# /etc/init.d/apache2 restart

6. find the log reports.
root@ns2:~# tail -f /var/log/syslog

7. Now, open the firefox browser and hit the following link. Set the auto refresh time as per your requirements. Oh! please replace the ip 192.168.1.11 with your server's ip address.
http://192.168.1.11/sqstat/sqstat.php

Troubleshooting:
If you hit http://192.168.1.11/sqstat/sqstat.php and found your browser is tring to download the sqstat.php insted running the web page then run apt-get install apache2-mpm-prefork. First try it from another pc because sometimes browser hanged still if you close and reopen it.


Real life-Script:
root@ns2:~# apt-get install apache2
root@ns2:~# apt-get install php5
root@ns2:~# apt-get install apache2-mpm-prefork
root@ns2:~# apt-get install squid
root@ns2:~# vim /etc/squid/squid.conf

# Squid normally listens to port 3128
http_port 8080 transparent

#Recommended minimum configuration:
acl all src all
acl manager proto cache_object
acl localhost src 127.0.0.1/32
acl to_localhost dst 127.0.0.0/8 0.0.0.0/32
acl mynetwork src 10.0.0.0/24
acl webserver src 192.168.1.11/255.255.255.255

# Only allow cachemgr access from localhost
http_access allow manager localhost
http_access allow manager webserver
http_access deny manager

# from where browsing should be allowed
#http_access allow localnet
http_access allow localhost
http_access allow mynetwork

root@ns2:~# vim /etc/init.d/iptables
# Assigning our local network
our_network="10.0.0.0/24"

# Redirect http to SQUID.
iptables -t nat -A PREROUTING -p TCP -s "$our_network" --dport 80 -j REDIRECT --to-port 8080
iptables -t nat -A PREROUTING -p TCP --dport 8080 -j REDIRECT --to-port 8080

root@ns2:~# wget http://samm.kiev.ua/sqstat/sqstat-1.20.tar.gz
root@ns2:~# tar -zxvf sqstat-1.20.tar.gz
root@ns2:~# mv sqstat-1.20 /var/www/
root@ns2:~# cd /var/www/
root@ns2:/var/www# mv sqstat-1.20 sqstat
root@ns2:/var/www# mv config.inc.php.defaults config.inc.php
root@ns2:/var/www# vim /var/www/sqstat/config.inc.php

//===================== Change the following settings =====================//
// Maximum URL length to display in URI table column
DEFINE("SQSTAT_SHOWLEN",100);

/* proxy settings */
/* Squid proxy server ip address or host name */
#$squidhost[0]="127.0.0.1";
$squidhost[0]="192.168.1.11";

/* Squid proxy server port */
#$squidport[0]=3128;
$squidport[0]=8080;

root@ns2:/var/www# /etc/init.d/squid restart
root@ns2:/var/www# /etc/init.d/apache2 restart