Script Hat

A new tool has been in the works for generating summary information from BlueCoat proxy logs. The BlueCoat proxy log tool is designed to take compressed BlueCoat proxy logs and put relevant information into a mysql database where it generates the Top x number of users, source ip’s, websites, denied sites, the list of categories visited and total page views. It will accept 1 or more logs per run and the limits are hardware related only. You can take a look at the demo that is set up here at http://www.scripthat.com/log_report/. Currently the tool supports the “main” Bluecoat logs with the W3C Extended Log File Format (ELFF). The field construction also needs to be set up as follows…

gmtime c-ip cs-username cs-uri-scheme cs-host cs-uri-path cs-uri-query rs(Content-Type) s-action sc-filter-result sc-filter-category s-ip sr-bytes s-sitename r-port r-ip rs-bytes cs(User-Agent)

Other formats and field constructions can be set up in the future, and anyone wanting to help with the code is more than welcome. This is the first version of the tool, even though it runs smoothly for my purposes others may find some things that need improvement and may suggest features not yet implemented…your feedback is welcome.

The graphs are generated using Open Flash Charts, a great project That is located at http://teethgrinder.co.uk/open-flash-chart-2/ with continued improvement at http://www.openflashchart.com, it is LGPL licensed.

Below are the requirements and installation guidelines included in the readme:

Features
- Generates some graphs and statistics based on username, source ip address, Top websites, Top Denied websites, Top Categories and total page views.
- Can process one or more logs per run.
- Takes about 1 minutes to generate a report (depending on system) against a 15mb compressed (150mb uncompressed) file.
Takes 30 minutes to generate a report on 700mb compressed files (about 7gigs uncompressed) uses about 2gigs for database.

Requirements

Linux/Unix (Tested on Red Hat Enterprise 5 and Fedora Core 9)
Mysql (Tested on 5.0.77) you will need a version that supports InnoDB
Apache web server
Perl (Tested on v5.10.0) with the modules Date::Manip and DBI; perl -MCPAN -e “install Date::Manip”
PHP (Tested on v5.2.9)
Not required but very helpful — phpMyAdmin

Log file formatting
This tool is used for the “main” logs generated by BlueCoat proxy devices.

The format needs to be W3C Extended Log File Format (ELFF) with the string set as follows:
gmtime c-ip cs-username cs-uri-scheme cs-host cs-uri-path cs-uri-query rs(Content-Type) s-action sc-filter-result sc-filter-category s-ip sr-bytes s-sitename r-port r-ip rs-bytes cs(User-Agent)

Have the bluecoat ftp the file in compressed (.gz) format. This is what we use because it saves diskspace (and bandwidth).
A log file that would be 132 megs is 13.8 megs compressed so your logs use just about 10% of the diskspace.

Installation

Create a new database called proxy_reports
create a user who has full access to the database
create a password for the user

upload proxy_reports.sql.gz (easy using phpmyadmin) to build the tables needed.

Enable caching in mysql

Be sure to enable caching in mysql so the results from previous queries will be stored. The
max_allowed_packet needs to be set larger as well so that the database inserts will work, otherwise
the 1 mb default will stop the database updates.

Open your mysql configuration file (for Red Hat it is located at /etc/my.cnf )

put the following lines in the config :

#################################################################
# Set up query caching for fast results (after it’s been cached)
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

# Max allowed packet needs to be bigger as well
max_allowed_packet=16M
#################################################################

And restart the mysql service

/sbin/service mysqld restart

Copy log_report folder to your web directory (typically /var/www/html)
Change ownership of all php files to your webserver (typically apache)
cd log_report
chown apache *
point your web browser to webserver/log_report/config.php
Edit the mysql variables to use the username and password for your database
Change the log directory to where your logs will be stored
Change the home directory to the home location (typically /var/www/html/log_report)

To generate a report change directories to log_report/perl change permissions and run bluecoat_reports.pl script
cd /log_report/perl
chmod 755 *
./bluecoat_reports.pl

log_report.tar.gz

Leads and Contacts Database is a PHP and Mysql system for keeping track of contacts and leads, it was setup for a Real Estate professional but can be used for anything.

With this PHP interface you can Add, Edit, Search, Delete, Sort, and flag individual entries. This tool is opensource, meaning you can add to or change anything you want with it. If you add a feature or make an improvement please post it here for others as well, since this is a functional version but could be built upon to add features. I started with this because a lot of the other ones I came across had too much extra junk for my purposes, so I tried to make a more streamlined version.

Here is a screenshot of the main interface with individual’s information blocked out.

Here is a screenshot of the Add an entry interface

And a screenshot of the View entry interface

*Instructions for Leads and Contacts Management*

This interface is meant for your typical LAMP system (Linux Apache Mysql and PHP), if you want to install it on something
else have fun, but you might have to do things a little different.

1. Download the contact_management.zip file from scripthat.com
2. uncompress it in a directory of your choice, making sure apache is aware of it (ex /var/www/html/)
3. Make sure php and mysql are installed, preferably more recent versions, you can check with commands:

php -v
PHP 5.2.8 (cli) (built: Dec 15 2008 19:32:14)
Copyright (c) 1997-2008 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2008 Zend Technologies
with the ionCube PHP Loader v3.1.32, Copyright (c) 2002-2007, by ionCube Ltd., and
with Zend Extension Manager v1.2.2, Copyright (c) 2003-2007, by Zend Technologies
with Zend Optimizer v3.3.3, Copyright (c) 1998-2007, by Zend Technologies

mysql –version
mysql Ver 14.12 Distrib 5.0.51a, for redhat-linux-gnu (i686) using readline 5.0

and should get results similar to the above (that’s what my system is running).

4. Change the variables to suit your system in the contact_db.pl file for accessing mysql.
5. Load the table fields for contacts into your database using phpmyadmin or commandline with the table_contacts.txt.
6. Point your webbrowser to the location of the contact_management.php script, if you wanted you could also rename it to
index.php.
7. You can also import some fields from outlook using the import.pl file if you’d like, you might want to modify the perl
script to your needs. Since everyone uses outlook contacts differently I didn’t spend much time to make the database like it and tried to make it just import important things like names, address, city state, phone #’s, emails etc.

8. At this point you should be able to add, edit, delete and view new entries in the database from the web interface.

If you have any questions, improvements, suggestions please post them.

I came across this and thought I would post it for reference.
If you ever need to grab the minimum value and maximum value from an array it’s actually fairly easy to do. PHP has a built in function called min and max. Say you have an array like so:

<?php
$values = array(6323,6030,5841,5547,5400,5091);
echo min($values),"\n";
echo max($values);
?>

This simple function will give you the following results:
5091
6323
This was helpful to me as I needed to grab the min and max from an array to set boundaries in a dynamic graph.

Here is an example of how to use php to communicate with a mysql database:

<?php
$user  = 'username';        // Database username
$pass  = 'password';        // Database password
$datab = 'database';        // Database you are working with.
$table = 'table';           // Table you are working with.
$dbc = mysql_connect('localhost', "$user", "$pass");
mysql_select_db("$datab");
$query="SELECT * from $table";
if ($r = mysql_query ($query)) { // Run the query.
   $i = 0;
   while ($row = mysql_fetch_array ($r)) {
      $i++;
      echo "$row[0] $row[1] $row[2] $row[3] $row[4]\n";
   }
}
?>

This query will output results for each row of the specified table in a database (in this particular case there are 5 fields).