PHPal

http://phpal.sourceforge.net/engineforallanbilling

1) Manual rewrited for Ubuntu users

2) New (minimized)  variant of the raw table (storage for billing data) in ulog database with new keys!

3) Modified script /root/flows/rota

Right now folder with langs moved to separated package.

This lang used in all Billings.

http://sourceforge.net/project/showfiles.php?group_id=201020&package_id=311740

Right now i’m testing new project alSUMBilling.

As you remember, this is complete solution for accouting all traffic (proxy+NAT)  in you bussines.

This is alSAMSBilling + alLANBilling.

alSUMBilling will be released after adding multilang and some fixes for another people (right now its configured only for my databases).

Also new version of the alLANBilling and alSAMSBilling coming with fixed month selection (select any month, not just currently and prevous) .

New planned project: alTelbookLDAP. This is simply PHP-script to show contents (Name, Phone Number, email) of the your Microsoft LDAP Server (Active Directory). Like a phone book for your bussines with storage in Active Directory. May be it will be merged with “alStaff” (Telephone book for small organizations (old project name - “alPhoneBook”) integrated with base of inventory.)

I recomend to add this indexes to speedup processing:

ALTER TABLE raw ADD INDEX ( `unix_secs` );

ALTER TABLE raw ADD INDEX ( `srcaddr` );

ALTER TABLE raw ADD INDEX ( `dstaddr` );

In the alLANBilling version 20080928 added textbox where you can type date.

All records BEFORE entered date will be deleted from database.

After delete database will optimize and check.

Don’t forget to grant delete privilege to the user!

grant delete on DATABSE.TABLE to USER;

UPD: After mistake in the code…i’ve lost all records in my database…. After that I added protection:

You will be unable to delete records with date > -2 mont (this means, that you can delete only recods thats old more than 2 month)

Oh… Happy new year!

Today me have woken and have informed, that it is impossible to see internet-statistics (in the alLANBilling) of work in December 2007…

Fixed! Right now you can select 2007 or 2008 year.

Month selection also fixed (”december bug”).

Some code and UI fixes.

If not working web, check this:

1) if mysql extension for php is installed (php-mysql-*.rpm must be installed)

2)when you are filling “Your local network address” you should enter 192.168.1.0 (if computers in your network has addreses like 192.168.1.1, 192.168.1.5), and so on..

(Instructions for Ubuntu)

1. I hope that the NAT, routing, httpd, php, mysql,fprobe-ulog, flow-tools packages already installed
2. Configure /etc/default/fprobe-ulog:
   INTERFACE=”eth:100,ppp:200″
   FLOW_COLLECTOR=”localhost:555″
   OTHER_ARGS=”"

   Where FLOW_COLLECTOR is an your host, where running flow-capture

3. Configure /etc/flow-tools/flow-capture.conf:
   -R /root/flows/rota -e 2 -w /var/flows/acct -n287 -S5 -V5 -N0 127.0.0.1/127.0.0.1/555
4. Add iptables rule to which all packages will be NAT-pass through ULOG

   This is only EXAMPLE:

         *filter
   
         :INPUT ACCEPT [0:0]
   
         :FORWARD DROP [0:0]
   
         :OUTPUT ACCEPT [0:0]
   
         :NETFLOW - [0:0]
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -m tcp --dport 25 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -m tcp --dport 110 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -m tcp --dport 4444 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 2123.123.123.123/32 -p tcp -m tcp --dport 25 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -m tcp --dport 110 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p udp -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p udp -j NETFLOW
   
         -A FORWARD -s 192.168.0.5/32 -j NETFLOW
   
         -A FORWARD -s 192.168.0.200/32 -p udp -m udp --dport 123 -j NETFLOW
   
         -A FORWARD -s 192.168.0.200/32 -p udp -m udp --dport 53 -j NETFLOW
   
         -A FORWARD -d 192.168.0.0/24 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -m tcp --dport 110 -j NETFLOW
   
         -A FORWARD -s 192.168.0.0/24 -d 123.123.123.123/32 -p tcp -m tcp --dport 25 -j NETFLOW
   
         -A NETFLOW -j ULOG
   
         -A NETFLOW -j ACCEPT
   
         COMMIT

5. Create a folder /var/flows/acct/ (storage flows)
6. Create a folder /root/flows/
7. In the folder /root/flows/ create executable file:

   Script for exporting data to MySQL database: /root/flows/rota

   #!/bin/sh
   /usr/bin/flow-export -f3 -mUNIX_SECS,DOCTETS,SRCADDR,DSTADDR,INPUT,OUTPUT,SRCPORT,DSTPORT,PROT -u “root:pass:localhost:3306:ulog:raw” < $1

8. In the file /root/flows/rota change “root:pass:localhost:3306:ulog:raw” to the your “login:password:mysqlhostname:port:databasename:tablename” to access MySQL

   (the user must have write access to the database!)!

9. Login to the MySQL and create ulog database:
   

   create database ulog

10. Create a table raw to store the data:
    

    CREATE TABLE IF NOT EXISTS `raw` (
    `unix_secs` int(11) unsigned NOT NULL default ‘0′,
    `dpkts` int(11) unsigned NOT NULL default ‘0′,
    `doctets` int(11) unsigned NOT NULL default ‘0′,
    `srcaddr` varchar(45) NOT NULL default ‘0′,
    `dstaddr` varchar(45) NOT NULL default ‘0′,
    `input` smallint(5) unsigned NOT NULL default ‘0′,
    `output` smallint(5) unsigned NOT NULL default ‘0′,
    `srcport` smallint(5) unsigned NOT NULL default ‘0′,
    `dstport` smallint(5) unsigned NOT NULL default ‘0′,
    `prot` tinyint(3) unsigned NOT NULL default ‘0′,
    KEY `unix_secs` (`unix_secs`),
    KEY `srcaddr` (`srcaddr`),
    KEY `dstaddr` (`dstaddr`),
    KEY `input` (`input`),
    KEY `output` (`output`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

11. Add new user to the MySQL:
    

    GRANT ALL PRIVILEGES ON ulog.* TO USER@localhost IDENTIFIED BY "PASSWORD";

    You must change user and password!

12. Start services:
    

    service fprobe-ulog start

    service flow-capture start

13. After 15-20 minutes watch the mysql database:
    

    select * from raw limit 5;

    You should see someting like this:

     unix_secs            doctets            srcaddr                 dstaddr               srcport            dstport
    1919283189     46934        123.123.123.123    192.168.1.123        443                 1120

    If you don's see data in the any column (like 0), than export-script working incorrectly.

14. Install the web-interface alLANBilling to view statistics

With release of the alSAMSBilling “lang” folder will be one for all projects (files with langs will contain traslation for both projects)

Added English translation of Engine (NetFlow, flow-tools) installation.

Original version avaible at opennet.ru.

Visit alLANBilling for links.