Учет интернет-трафика

Материал из Lblss.ru
Перейти к: навигация, поиск

Содержание

Преамбула

Итак, самарская прокатная компания ALEF, где я имел честь когда то работать постоянно, а теперь только как приходящий админ.

Имеем:

  • несколько машин в Windows-домене, где у большинства пользователей профиль перемещаем (собственно, юзеров больше чем машин). На воркстейшн установлена Windows XP SP2 Pro, контроллер домена Windows Server 2003, автоматическое обновление отключено.
  • шлюз под FreeBSD 4.11 - вне домена. Отдельное спасибо lissyara за помощь в установке и настройке! На шлюзе помимо прочего имеются: trafd, apache, mysql, php.

Собственно, первоначальная считалка трафика была установлена по статье, опубликованной на старом сайте lissyara. Каждую ночь присылались письма с количеством принятых байт из инета и отправленных байт клиенту. Впоследствии от писем отказался за ненадобностью.

Разумеется, программ учета интернет-трафика существует великое множество. Однако, в данном случае, не было ни желания, ни необходимости привязывать шлюз к win-домену. Так же хотелось оставить прозрачное проксирование без всякой авторизации пользователей.

Задача

  • Собирать статистику потребления интернет-трафика непосредственно по доменным windows-пользователям (а не по машинам).
  • Вывести полученную статистику в веб-интерфейсе.

Идея

Нужно отследить и зафиксировать логон пользователя, чтоб затем относить трафик на его счет. Фиксация делается из логон-скрипта (он назначается через групповую политику) путем отправки GET-запроса с параметрами к вебсерверу. Там этот запрос обрабатывается и делается соответствующая запись в базу данных. Аналогично фиксируется и логофф.

Раз в минуту-две-пять, запускаемый по крону скрипт, снимает статистику, собранную trafd и тоже делает запись в базу.

Около полуночи запускается скрипт обработки этих данных, обрабатывает и записывает суммарную статистику за сутки.

Файлы и настройки

Файлы логон и логофф скриптов расположены в соответствующих папках контроллера домена - при настройке политик logon и logoff скриптов разберетесь где именно. Остальные находится на шлюзе.

IP-адрес шлюза: 192.168.0.5
IP-адрес контроллера домена: 192.168.0.10

Об установке и настройке апача, мускуля и пыхпыха говорено много, и здесь повторять нет смысла. На сервере должна быть установлена русская локаль cp1251 на тот случай, если в домене есть пользователи с русскими именами, например, "Администратор". Все файлы и база так же имеют кодировку cp1251.

Теперь подробно...

Веб-сервер

Apache/1.3.33, PHP/4.3.11 установлен модулем, впрочем, это не принципиально.

Так как вебсервер изначально ставился всего лишь для тестирования скриптов, то на нем не предусматривались виртхосты. В силу традиций и природной лени для трафикосчиталки непосредственно в документруте была создана папка /_trafd/, которой оказалось вполне достаточно. Все файлы *.php лежат именно там.

База данных

Используется MySQL сервер версии 4.1.11.

Для проекта создан пользователь 'trafdusr' с паролем 'trafdpassword'.

База данных 'trafd' имеет таблицы: `users`, `userlog`, `traffic_tmp`, `traf_itog` и со временем обрастает кучей таблиц вида `имяИнтерфейса_год_месяц`. Ручками создавались следующие три таблицы:

Структура таблицы `users`

На самом деле, это компьютеры :-) Имя таблицы досталось по наследству от скриптов lissyara.

CREATE TABLE IF NOT EXISTS `users` (
  `ip` INT(3) NOT NULL DEFAULT '0' COMMENT 'последние цифры IP-адреса',
  `name` VARCHAR(30) NOT NULL COMMENT 'понятное имя машины',
  PRIMARY KEY  (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Структура таблицы `userlog`

Сюда пишется информация о логонах пользователей

CREATE TABLE `userlog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `d` DATE NOT NULL DEFAULT '0000-00-00',
  `t` TIME NOT NULL DEFAULT '00:00:00',
  `username` VARCHAR(90) NOT NULL DEFAULT '',
  `userip` VARCHAR(15) NOT NULL DEFAULT '',
  `compname` VARCHAR(15) NOT NULL DEFAULT '',
  `action` VARCHAR(5) NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ;

Структура таблицы `traf_itog`

В эту таблицу пишутся итоги.

CREATE TABLE `traf_itog` (
  `id` INT(8) NOT NULL AUTO_INCREMENT,
  `date` DATE NOT NULL DEFAULT '0000-00-00',
  `user` VARCHAR(15) NOT NULL DEFAULT '',
  `comp` VARCHAR(15) NOT NULL DEFAULT '',
  `traf` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ;

Логон и Логофф скрипты - logonUser.wsf и logoffUser.wsf

При логоне пользователя отсылается запрос на вебсервер. Через несколько секунд показывается страничка с суммарным трафиком этого пользователя. В качестве браузера используется ИЕ6.

Файл logonUser.wsf

<Job>
<Script language="VBScript">
' на объекте с юзерами политика: 
' Конфигурация пользователя -> Конфигурация Windows -> Сценарии (вход/выход) -> 
' вход в систему -> Добавить -> просто указан этот файл logonUser.wsf
'

  Function GetIPAddress (strComputer)
    Set objWMIService = GetObject("winmgmts:" & "!\\" & strComputer & "\root\cimv2")
 
    Set colAdapters = objWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
 
    For Each objAdapter in colAdapters
      If Not IsNull(objAdapter.IPAddress) Then
        For i = LBound(objAdapter.IPAddress) To UBound(objAdapter.IPAddress)
          GetIPAddress = objAdapter.IPAddress(i)
        Next
      End If
    Next
  End Function
 
 
  Const ForReading = 1, ForWriting = 2, ForAppending = 8 ' константы

  Dim path, dbName, tmpFN, msAccess, scrFN, userNm, compNm, strLog, tmpFile
  Dim fLog, tOpen, tClose, tS, tM, tH, resp
 
  Set WshShell = WScript.CreateObject("WScript.Shell")
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set wshProcEnv = WSHShell.environment("process")
  Set wshNetwork = WScript.CreateObject("WScript.Network")
  Set objArgs = WScript.Arguments
 
  userNm = LCase(wshNetwork.Username)
  compNm = wshNetwork.Computername
  ipadr = GetIPAddress(".")
 
  tOff = Now()
  Set fLog = fso.OpenTextFile("\\asrv\profiles\scripts\users.log", ForAppending) ' открыть лог для записи
  fLog.Write tOff & " " & userNm & " " &  ipadr & " " & compNm & " logON " & vbCrLf
  fLog.Close ' закрыть файл лога

' отправка в БД
  Set objExplorer = WScript.CreateObject("InternetExplorer.Application")
'  objExplorer.Visible = true ' видимость - раскомментировать для отладки
  objExplorer.Navigate "http://192.168.0.5/_trafd/userlog.php?username=" & userNm & "&userip=" & ipadr & "&compname=" & compNm & "&action=on" ' URL

  While objExplorer.Busy
  Wend
  WScript.Sleep 10000 ' временно сделаем задержку 10 секунд, а так одной хватает 
  objExplorer.quit
  WScript.Sleep 1000
  Set objExplorer = Nothing
 
' СЛЕДУЮЩИЕ СТРОКИ НУЖНЫ ТОЛЬКО ПРИ ЛОГОНЕ!!! Да и то, только если юзеров постращать :-)
' показать статистику инета
  Set objExplorer = WScript.CreateObject("InternetExplorer.Application")
  objExplorer.Visible = true ' видимость - для показа статистики
  objExplorer.Navigate "http://192.168.0.5/_trafd/show.php?username=" & userNm
  While objExplorer.Busy
  Wend
</Script>
</Job>

Второй файл logoffUser.wsf похож на первый, только в строке запроса изменено "&action=on" на "&action=off", ну и в соответствующей политике указать его.

Измененная строка:

  objExplorer.Navigate "http://192.168.0.5/_trafd/userlog.php?username=" & userNm & "&userip=" & ipadr & "&compname=" & compNm & "&action=off" ' URL

Строки показа статистики разумеется тоже следует исключить.

Снятие статистики с trafd - trafd_count

Файл trafd_count Опять же, скрипт Лиссяры, практически без изменений (таблицы по интерфейсам побиты по месяцам а не по годам).

Запуск по крону раз в минуту или несколько. При большой нагрузке (порядка 40 интенсивно работающих пользователей) он откровенно не успевает делать запись в базу, потому в одной из версий переписал его на Perl. Для десятка машин годится и этот.

Для запуска этого скрипта в крон прописана строка:

*/2 * * * * /usr/local/bin/trafd_count 2>/dev/null

Собственно, код:

#!/bin/sh
#
#
# Вводим данные для подключения к MySQL серверу
# IP адрес MySQL сервера
IP_MySQL_servera="localhost"
# Имя пользователя для доступа к БД в которой храниться траффик
username="trafd"
# Пароль пользователя MySQL
user_passw="trafd"
# Имя базы данных
db_name="trafd"
 
# поехали
 
# Сегодяшний день
day="`date +%Y-%m-%d`"
# Текущий год
year="`date +%Y`"
# Текущий месяц
month="`date +%m`"
# Текущее время (секунды специально сделаны 00 - иногда cron запускает скрипт не
# в 00 секунд а позже (максимум что я видел - в 13), если машина очень загружена -
# как итог в логах начинает фигурировать разное число секунд.
# Мне это непонравилось :)
curr_time="`date +%H:%M:00`"
# Директория в которой будут храниться текстовые файлы с логами trafd
NewDir="/var/traffic/${year}/${month}"
# Пытаемся создать эту самую директорию на случай если это первый запуск
# или произошла смена месяца (года)
mkdir -p ${NewDir}
# Ну и топаем туда
cd ${NewDir}
 
# Местоположение исполняемого файла клиента MySQL
mysql="/usr/local/bin/mysql"
# Префикс для команд (лень же каждый раз набивать параметры подключения)
sql_preffix="${mysql} --host=${IP_MySQL_servera} \
--user=${username} --password=${user_passw} --database=${db_name}"
 
# Считываем все переменные из файла /etc/rc.conf с целью извлечь оттуда
# строчку с названиями интерфейсов по которым работает trafd
# (У меня три сетевых платы и lo0 - просто интереса ради)
. /etc/rc.conf
 
# Для всех интерфейсов выковырнутых из rc.conf (висят в ${trafd_ifaces})
# выполняем один и тот же набор действий по разбору логов и запихиванию
# их в базу данных
for iface in ${trafd_ifaces}
do
# Сохраняем статистику по текущему интерфейсу
/usr/local/bin/trafsave ${iface}
# Преобразуем логи из двоичного в текстовый формат. Сохраняются они в
# папке /tmp в виде файлов summary.* c расширением по имени интерфейса
/usr/local/bin/traflog -i ${iface} -a -n -s > /tmp/summary.${iface} 2>/dev/null
# Очищаем файл с логами в двоичном формате
cat /dev/null > /usr/local/var/trafd/trafd.${iface}
# Дозаписываем логи в текстовый файл (пусть лежат на всякий случай...)
cat /tmp/summary.${iface} >> ${NewDir}/summary.${iface}
# Далее - загоняем траффик в БД
#
${sql_preffix} --execute="CREATE TABLE \`traffic_tmp\` \
(\`date\` DATE NOT NULL, \`time\` TIME NOT NULL, \
\`from_IP\` CHAR(16) NOT NULL, \`port_from_IP\` CHAR(8) NOT NULL, \
\`to_IP\` CHAR(16) NOT NULL, \`port_to_IP\` CHAR(8) NOT NULL, \
\`protocol\` ENUM('icmp','tcp','udp') NOT NULL, \`bytes\` int(16) NOT NULL, \
\`all_bytes\` int(16) NOT NULL) TYPE=MyISAM COMMENT='tmp_table'" 2>/dev/null
 
# Лопатим данные для интерфейса ${iface}
# Очищаем временную таблицу
${sql_preffix} --execute="DELETE FROM \`traffic_tmp\`"
# Построчно превращаем файл со статистикой в набор переменных
grep -v "^ " /tmp/summary.${iface} |
{
while read stroka
do
from_IP=`echo "${stroka}" | awk '{print $1}'`
port_from_IP=`echo "${stroka}" | awk '{print $2}'`
to_IP=`echo "${stroka}" | awk '{print $3}'`
port_to_IP=`echo "${stroka}" | awk '{print $4}'`
protocol=`echo "${stroka}" | awk '{print $5}'`
bytes=`echo "${stroka}" | awk '{print $6}'`
all_bytes=`echo "${stroka}" | awk '{print $7}'`
# Загоняем полученный набор во временную таблицу
${sql_preffix} --execute="INSERT INTO \`traffic_tmp\` (\`date\`, \
\`time\`, \`from_IP\`, \`port_from_IP\`, \`to_IP\`, \`port_to_IP\`, \
\`protocol\`, \`bytes\`, \`all_bytes\`) \
values ('${day}', '${curr_time}', '${from_IP}', \
'${port_from_IP}', '${to_IP}', '${port_to_IP}', \
'${protocol}', '${bytes}', '${all_bytes}')"
done
}
# Стираем пустые строки (а вот откуда они вылазиют я так и непонял....)
${sql_preffix} --execute="DELETE FROM \`traffic_tmp\` WHERE from_IP='' AND \
port_from_IP='' AND to_IP='' AND port_to_IP='' AND protocol=''"
# Стираем строки в которых полное число байт (вместе с технической инфой)
# равно нулю (тоже непойми откуда берутся - раз в статистику trafd попали -
# значит соединение было и байты должны были б быть...)
${sql_preffix} --execute="DELETE FROM \`traffic_tmp\` WHERE all_bytes='0'"
# Создаём таблицу для окончательного хранения траффика
${sql_preffix} --execute="CREATE TABLE \`${iface}_${year}_${month}\` \
(\`unic_id\` INT(16) NOT NULL AUTO_INCREMENT, \
\`date\` DATE NOT NULL, \`time\` TIME NOT NULL, \
\`from_IP\` CHAR(16) NOT NULL, \`port_from_IP\` CHAR(8) NOT NULL, \
\`to_IP\` CHAR(16) NOT NULL, \`port_to_IP\` CHAR(8) NOT NULL, \
\`protocol\` ENUM('icmp','tcp','udp') NOT NULL, \`bytes\` int(16) NOT NULL, \
\`all_bytes\` int(16) NOT NULL, \
PRIMARY KEY (\`unic_id\`), \
KEY \`date\`(\`date\`) \
) TYPE=MyISAM COMMENT='(${iface}) ${year}_${month}'" 2>/dev/null
# Перекидываем траффик из временной таблицы в окончательную, при этом
# объединяем строки в которых совпадает ВСЁ кроме числа байт.
${sql_preffix} --execute="INSERT INTO \`${iface}_${year}_${month}\`\
(\`date\`, \`time\`, \`from_IP\`, \`port_from_IP\`, \`to_IP\`,\
\`port_to_IP\`, \`protocol\`, \`bytes\`, \`all_bytes\`) \
SELECT \`date\`, \`time\`, \`from_IP\`, \`port_from_IP\`,\
\`to_IP\`, \`port_to_IP\`, \`protocol\`, sum(\`bytes\`) as \`bytes\`,\
sum(\`all_bytes\`) as \`all_bytes\` FROM \
\`traffic_tmp\` GROUP BY \`date\`, \`time\`, \`from_IP\`, \`port_from_IP\`,\
 \`to_IP\`, \`port_to_IP\`, \`protocol\`"
 
done
 
# Очищаем файл c логами о том когда и по какому интерфейсу сохранялась статистика
cat /dev/null > /var/log/traffic.log

Фиксация логона пользователей - userlog.php

Файл userlog.php

<?php
/*
Сей скриптег ловит данные, которые присылает GET-запрос с клиентской машины.
В запросе содержится имя юзера, айпишнег, имя компа и действие (вход|выход).
Данные пишуццо в таблицу.
Для точности время записи берется с локалхоста (веб-сервера) и
для упрощения обработки секунды обнуляются.
*/
  include_once "dbconnect.php" ;
  include_once "auth.php" ;  
  // прием и обработка переменных
  $username = strtolower(htmlentities(urldecode($_GET['username']))) ;
  $userip = strtolower(htmlentities($_GET['userip'])) ;
  $compname = strtolower(htmlentities(urldecode($_GET['compname']))) ;
  $action = strtolower(htmlentities($_GET['action'])) ;
 
  $date = date("Y-m-d") ;
  $time = date("H:i:00") ;
 
  // пишем в базу
  mysql_query("
    INSERT INTO
      `userlog` (`d`, `t`,`username`, `userip`, `compname`, `action`)
    VALUES
      ('$date', '$time', '$username', '$userip', '$compname', '$action')") ;
 
  echo '<hr>OK' ;
?>

Подключение к базе, тариф и локаль - dbconnect.php

Файл dbconnect.php

Укажите свои IP-адреса шлюза (внешний и внутренний), маску локалки и реквизиты подключения к базе - строки 3...11
Так же пропишите свои тарифы в строках 36, 37

<?php
  // IP хостов, интерфейсы,
  $ip_out_gw = "aaa.bbb.ccc.ddd"; // внешний IP шлюза
  $lan_mask = "192.168.0."; // маска по которой выбираются IP для подсчёта
  $ip_int_gw = $lan_mask . "5"; // внутренний IP шлюза
  $IF_out_gw = "wb0"; // имя внешнего интерфейса шлюза
  $IF_int_gw = "rl0"; // имя внутреннего интерфейса шлюза
  $DBHOST = 'localhost' ;
  $DBNAME = 'trafd' ;
  $DBUSER = 'trafdusr' ;
  $DBPASS = 'trafdpassword' ;
 
  // срубаем кеширование, насколько возможно
  header("Expires: Tue, 1 Jul 2003 05:00:00 GMT");
  header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
  header("Cache-Control: no-store, no-cache, must-revalidate");
  header("Pragma: no-cache");
 
  // коннект!
  mysql_connect($DBHOST, $DBUSER, $DBPASS) or die('<br><br><big><center>Не могу подключиться к MySQL-серверу!</center></big>') ;
  mysql_select_db($DBNAME) or die('<br><br><big><center>Не могу выбрать базу!</center></big>') ;
  // локаль подключения
  mysql_query("SET NAMES 'cp1251'") ;
 
  // локаль сервера
  setlocale(LC_ALL, "ru_RU.CP1251");
 
 
  // возвращает отформатированоецелое число (чиста для удобочитаемости)
  function fnum($num) {
    return number_format($num, 0, ",", "`") ;
  }
 
  // возвращает тариф при указанном трафике (цена за метр зависит от объема)
  function tarif($traf) {
    $price = array(3.5, 2.8, 2.55, 2.3, 2, 1.8) ; // цена за метр трафа http://www.samara.vt.ru/?id=9090
    $priceto = array(101, 301, 1001, 5001, 10001) ; // к-во трафа для тарифа /условие проверки < (меньше)/
      $i = 0 ;
      while(intval(1 + $traf) > $priceto[$i]) {
      $i++ ;
    }
    return $price[$i] ;
  }
?>

Аутентификация - auth.php

Файл auth.php

Не забыть прописать свои сеть, username и password - строки 4,6,7

<?php
session_start() ;
 
if(!strstr($_SERVER['REMOTE_ADDR'], $lan_mask) ) {
  // требуется аутентификация юзера
  if ( isset($_POST['user']) && $_POST['user']=='username' && 
       isset($_POST['pw']) && $_POST['pw']=='password' ) {
    $_SESSION['USER_AUTH'] = 'yes' ;
  }
  elseif(!isset($_SESSION['USER_AUTH'])) {
    echo <<<HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title>Аутентификация пользователя</title>
  <meta http-equiv=Content-Type content="text/html; charset=windows-1251">
  <style type="text/css">
  <!--
  body{overflow:auto; }
  -->
  </style>
</head>
<body text="#000000" bgcolor="#ece9d8">
<table width="100%" height="100%" border="0" cellspacing="0" cellpadding="0" align="center">
  <tr><td colspan="3">&nbsp;</td></tr>
  <tr><td>&nbsp;</td>
    <td height="290px" width="350px" align="center" valign="middle">
      <div height="20" style="background-color:#7A96DF; font-size:14px; color:white; font-family:system, tahoma, verdana, arial; padding-left: 5px;"><b>Введите имя и пароль</b></div>
      <div style="border:2px outset #f0aa00; background-color:#ffe9d8; padding:10px;">
	<form method="POST"><br />
	  User: <input type="text" name="user" /><br /><br />
	  Pass: <input type="password" name="pw" /><br /><br />
	  <input type="submit" value="OK">
	</form>
      </div>
    </td><td>&nbsp;</td>
    </tr>
  <tr><td colspan="3">&nbsp;</td></tr></table>
</body></html>
HTML;
    die();
  }
}
?>

Формирование статистики за сутки - daytraf.php

Файл daytraf.php

Его запускать по крону типа через минуту после полуночи. Ниже строка в crontab. Не забудьте указать правильно свой путь к файлу.

1 0 * * * /usr/local/bin/php /usr/local/www/httpdocs/_trafd/daytraf.php 1>/dev/null 2>&1

При необходимости можно ручками дописать случайно пропущеный день - строка 29

<?php
/*
Оный скриптег надлежит запускать по крону типа через минуту после полуночи:
1 0 * * * /usr/local/bin/php /usr/local/www/httpdocs/_trafd/daytraf.php 1>/dev/null 2>&1
Тогда он обработает и запишет в `traf_itog` траф по юзерам и машинам за истекшие сутки.
 
Хотя, можна и просто позырить, так например:
http://192.168.0.5/_trafd/daytraf.php?date=2007-05-03
Без параметра = вчера.
192.168.0.5 - адрес шлюза
*/
 
include "dbconnect.php" ;
if(isset($_SERVER['REMOTE_ADDR']) && !strstr($_SERVER['REMOTE_ADDR'], $lan_mask)) die("LAN only!");
 
echo '<html>
<head>
<title>Сборка пар логон/логофф и трафа</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">
</head>
<body>' ;
 
if(isset($_GET) && isset($_GET['date'])) { // сегодня или из $_GET
  $today = htmlentities($_GET['date']) ;
}
else{
  $today = strftime("%Y-%m-%d", strtotime("-1 day")) ;
}
 
//$today = "2007-05-14"; // на тот случай, если ручками надо будет добавить денек
 
$year = substr($today, 0, 4) ; // год
$month = substr($today, 5, 2) ; // месяц
$nextday = strftime("%Y-%m-%d", strtotime("+1 day $today")) ; // завтра (для особого случая добавления записи)
 
$tableLan = $IF_int_gw . "_" . $year . "_" . $month ; // имя таблицы с трафом
 
// список компов за сегодня
$sqlComp = "
  SELECT `compname`, `userip`
  FROM `userlog`
  WHERE `d` = '$today'
  GROUP BY `compname`
  ORDER BY `compname`
" ;
$comps = mysql_query($sqlComp) ;
while($rowComp = mysql_fetch_assoc($comps)) { // по компам
  // получаем список юзеров на компе за сегодня
  $sqlUsers = "
    SELECT `username`
    FROM `userlog`
    WHERE `d`='$today' AND `compname`='" . $rowComp['compname'] . "'
    GROUP BY `username`
  " ;
  $users = mysql_query($sqlUsers) ;
  while($rowUser = mysql_fetch_assoc($users)) { // по юзерам на компе
    $trafSumU = 0 ; // сумма трафа по юзеру на компе
    $sqlLogONs = "
      SELECT `t`
      FROM `userlog`
      WHERE
        `d`='$today' AND
        `compname`='" . $rowComp['compname'] . "' AND
        `username`='" . $rowUser['username'] . "' AND
        `action`='on'
      ORDER BY `t`
      " ;
    $logONs = mysql_query($sqlLogONs) ;
    while($rowLogONme = mysql_fetch_assoc($logONs)) { // по логонам юзера на компе
      $logONu = $rowLogONme['t'] ;
      // теперь ищем следующий логон любого юзера на этом компе
      $sqlLogONother = "
        SELECT `t`
        FROM `userlog`
        WHERE
          `d`='$today' AND
          `compname`='" . $rowComp['compname'] . "' AND
          `action`='on' AND
          `t`>'" . $rowLogONme['t'] . "'
        ORDER BY `t`
        LIMIT 0, 1
      " ;
      $res = mysql_query($sqlLogONother) ;
      if(mysql_num_rows($res) > 0) {
        $rowNextLogONother = mysql_fetch_assoc($res) ;
        $logOFFa = $rowNextLogONother['t'] ;
      }
      else { // следующий логон любого юзера на этом компе не зафиксирован
        $logOFFa = '23:59:59' ;
        $rowNextLogONother['t'] = '23:59:59' ;
      }
 
      // ищем логофф того же юзера на этом компе
      $sqlLogOFFme = "
        SELECT `t`, `id`
        FROM `userlog`
        WHERE
          `d`='$today' AND
          `compname`='" . $rowComp['compname'] . "' AND
          `username`='" . $rowUser['username'] . "' AND
          `action`='off' AND
          `t`>'" . $rowLogONme['t'] . "' AND
          `t`<='" . $rowNextLogONother['t'] . "'
        ORDER BY `t`
        LIMIT 0, 1
      " ;
      $res = mysql_query($sqlLogOFFme) ;
      if(mysql_num_rows($res) > 0) {
        $rowNextLogOFFme = mysql_fetch_assoc($res) ;
        $logOFFb = $rowNextLogOFFme['t'] ;
      }
      else { // логофф того же юзера на этом компе не зафиксирован
        $logOFFb = '23:59:59' ;
      }
      // в итоге, берем за логофф фактическое время или, если его нет,
      // время следующего логона любого юзера на этом компе
      // или без секунды полночь :-)
      $logOFFu = ($logOFFa < $logOFFb) ? $logOFFa : $logOFFb ;
 
      // если нет больше записей по этому компу
      // if ($logOFFb == '23:59:59' && $logOFFa == '23:59:59')
      // то за логофф берем 23:59:59 и пишем в таблицу 23:59:58 
      // (я не помню, зачем так решил сделать, да ну пусть будет, 
      // все равно, секунды не учитываются)
      // и дописываем логон того же юзера на 00:00:00 следующих суток
      // запись в таблицу пойдет только при запуске от крона, то есть if(!isset($_SERVER))
      if(!isset($_SERVER) && $logOFFb == '23:59:59' && $logOFFa == '23:59:59') {
        $q="
          INSERT INTO 
            `userlog`
          SET
            `d`='$today',
            `t`='23:59:58',
            `username`='" . $rowUser['username'] . "',
            `userip`='" . $rowComp['userip'] . "',
            `compname`='" . $rowComp['compname'] . "',
            `action`='off'
        " ;
        mysql_query ($q);
        $q="
          INSERT INTO 
            `userlog`
          SET
            `d`='$nextday',
            `t`='00:00:00',
            `username`='" . $rowUser['username'] . "',
            `userip`='" . $rowComp['userip'] . "',
            `compname`='" . $rowComp['compname'] . "',
            `action`='off'
        " ;
        mysql_query ($q);
      } // end of if ($logOFFb == '23:59:59' && $logOFFa == '23:59:59')
 
 
 
      // просто позырим че накачали
      $sqlTrafUser = "
        SELECT SUM(`all_bytes`) AS `sumb`
        FROM `$tableLan`
        WHERE
          `date`='$today' AND
          `time`>='$logONu' AND
          `time`<='$logOFFu' AND
          `to_IP`='" . $rowComp['userip'] . "'
      " ;
      $res = mysql_query($sqlTrafUser) ;
      if(mysql_num_rows($res) > 0) {
        $rowTrafU = mysql_fetch_assoc($res) ;
        $trafU = $rowTrafU['sumb'] ;
      }
      else {
        $trafU = 0 ;
      }
      if ($trafU > 0) {
        echo  "Comp: ". $rowComp['compname'] . ", User: " . $rowUser['username'] . ", logON: " . $logONu . ", logOFF: " . $logOFFu . ", Bytes: " . $trafU . "<br>" ;
        $trafSumU += $trafU ;
      }
    } // end of по логонам юзера на компе
    if($trafSumU > 0) {
      echo "Сумма за сутки по " . $rowUser['username'] . " на " .$rowComp['compname'] . " = " . $trafSumU . " Bytes<br>" ;
 
      // запись суммы трафа по юзеру на компе за сутки в таблицу `traf_itog`
      if($_SERVER["DOCUMENT_ROOT"] == "") { // запрос будет выполняться только при запуске по крону, а при позырить нефиг
        $q = "
          INSERT INTO
            `traf_itog`
          SET
            `date`='$today',
            `user`='" . $rowUser['username'] . "',
            `comp`='" .$rowComp['compname'] . "',
            `traf`=" . $trafSumU . "
        " ;
        mysql_query($q);
      } // end of if($_SERVER["DOCUMENT_ROOT"] == "")
    } // end of if($trafSumU > 0)
  } // end of по юзерам на компе
  echo "<br>" ;
} // end of по компам
 
mysql_close() ;
echo '</body></html>' ;
// в общем, с какого то хуана вываливаеццо вот это в конце работы скрипта
// php in free(): warning: junk pointer, too high to make sense
?>

Просмотр обработанных данных по юзерам - show.php

Файл show.php

<?php
/*
Скриптец для отображения окончательных данных
отправленного на клиентскую машину трафика.
Отдельно отображается траф по юзерам за месяц и
отдельно по юзерам и машинам за день.
*/
 
include "dbconnect.php" ;
include_once "auth.php" ;
 
// входные данные
if(isset($_GET['m'])) {
  $month = htmlentities($_GET['m']) ;
  if(isset($_GET['d'])) {
    htmlentities($day = $_GET['d']) ;
  }
}
else {
  $month = strftime("%Y-%m", strtotime("-1 day")) ;
  $day = strftime("%d", strtotime("-1 day")) ;
}
 
$date = $month . ((isset($day)) ? "-$day" : "") ;
 
if(isset($_GET['username'])) {
  $username = htmlentities($_GET['username']) ;
}
 
$header = "Трафик за $date " . ( (isset($username)) ? "для $username" : "по пользователям" ) ;
 
 
 
// вычисляем тариф в зависимости от входящего трафа (на внешней сетевухе)
$sql = "
SELECT
  SUM(`all_bytes`) AS `outtraf`
FROM
  `" . $IF_out_gw . "_" . str_replace("-","_",$month) . "`
WHERE
  `to_IP` ='" . $ip_out_gw . "' AND
  `date` LIKE '" . $month . "%'" ;
$traffic_curr = mysql_fetch_array( mysql_query( $sql ) );
$traffic_curr_meg = $traffic_curr["outtraf"] / 1048576; // считаем траффик на внешнем интерфейсе
$tarif = tarif($traffic_curr_meg) ;  // получаем тариф для этого трафа
 
 
 
// месяцы (список)
$sqlMonths = "
  SELECT
    LEFT(`date`, 7) AS `ym`
  FROM
    `traf_itog`
  GROUP BY
    `ym`
  ORDER BY
    `date`
" ;
$dates = mysql_query($sqlMonths) ;
$months .= "год-месяц<br>" ;
while($row = mysql_fetch_assoc($dates)) {
  if($row['ym'] == $month) {
    $months .= "<a href='?m=" . $row['ym'] . "' class='act'>" . $row['ym'] . "</a><br>\n" ;
  }
  else {
    $months .= "<a href='?m=" . $row['ym'] . "'>" . $row['ym'] . "</a><br>\n" ;
  }
}
 
 
// дни месяца (список)
$sqlDays = "
  SELECT
    RIGHT(`date`, 2) AS `day`
  FROM
    `traf_itog`
  WHERE
    `date` LIKE '$month%'
  GROUP BY
    `date`
  ORDER BY
    `date`
" ;
$dates = mysql_query($sqlDays) ;
$days .= "число<br>" ;
while($row = mysql_fetch_assoc($dates)) {
  if(isset($day) && $row['day'] == $day) {
    $days .= "<font class='act'>" . $row['day'] . "</font><br>\n" ;
  }
  else {
    $days .= "<a href='?m=$month&d=" . $row['day'] . "'>" . $row['day'] . "</a><br>\n" ;
  }
}
 
// непосредственно сборка трафа
if(isset($day)) { // по юзерам и компам за день
  if(isset($username)) {
    $sqlTrafDayU = "
      SELECT
        SUM(`traf`) AS `sumtr`, `user`
      FROM
        `traf_itog`
      WHERE
        `date`='$month-$day' AND
        `user`='$username'
      GROUP BY
        `user`
    " ;
  }
  else {
    $sqlTrafDayU = "
      SELECT
        SUM(`traf`) AS `sumtr`, `user`
      FROM
        `traf_itog`
      WHERE
        `date`='$month-$day'
      GROUP BY
        `user`
    " ;
  }
  $trafDayU = mysql_query($sqlTrafDayU) ;
  if(mysql_numrows($trafDayU)==0) {
    $rec = "<tr><td>Трафик не зафиксирован<br>Мож Вас не было на работе? ;-)</td></tr>" ;
  }
  else {
    $rec = "<tr><th width='25%'>Логин</th><th width='25%'>Комп</th>
      <th width='25%'>Трафик<br>(байт)</th><th width='25%'>Всего<br>(байт)</th></tr>\n" ;
    while($rowU = mysql_fetch_assoc($trafDayU)) { // тут по каждому юзеру вытаскиваем траф по компам
      $sqlTrafDayC = "
        SELECT
          `comp`, `traf`
        FROM
          `traf_itog`
        WHERE
          `date`='$month-$day' AND
          `user`='" . $rowU['user'] . "'
        ORDER BY
          `comp`
      " ;
      $trafDayC = mysql_query($sqlTrafDayC) ;
      $rowsC = mysql_num_rows($trafDayC) ; // количество компов, на которых был залогинен
      $rowC = mysql_fetch_assoc($trafDayC) ; // это в любом случае
      $rec .= "<tr><td rowspan='$rowsC' align='right'>" . $rowU['user'] . "</td>
        <td align='right'>" . $rowC['comp'] . "</td>
        <td align='right'>" . fnum($rowC['traf']) . "</td>
        <td rowspan='$rowsC' align='right'>" . fnum($rowU['sumtr']) . "</td></tr>\n" ;
      if($rowsC > 1) { // для каждого следующего компа по строке
        while($rowC = mysql_fetch_assoc($trafDayC)) {
          $rec .= "<tr><td align='right'>" . $rowC['comp'] . "</td>
            <td align='right'>" . fnum($rowC['traf']) . "</td></tr>\n" ;
        }
      }
    }
  }
  mysql_free_result($trafDayU) ; // юзер обработан - ресурс не нужен
} // end of if(isset($day))
else { // по дням и юзерам за месяц
  $sqlTrafMonth = "
    SELECT
      `user`, SUM(`traf`) AS `sumtr`
    FROM
      `traf_itog`
    WHERE
      `date` LIKE '$month%'
    GROUP BY
      `user`
    ORDER BY
      `user`
  " ;
 
  $trafMonth = mysql_query($sqlTrafMonth) ;
  $sumtr = 0 ;
  $rec = "<tr><th width='30%'>Логин</th><th width='40%'>Трафик<br>(байт)</th><th width='30%'>Денег<br>($tarif руб/MB)</th></tr>\n" ;
  while($row = mysql_fetch_assoc($trafMonth)) { // строим табличку в цикле
    $rec .= "<tr>
      <td align='right'>" . $row['user'] . "</td>
      <td align='right'>" . fnum($row['sumtr']) . "</td>
      <td align='right'>" . number_format($row['sumtr']/1024/1024*$tarif, 2, ",", "`") . "</td>
      </tr>\n" ;
    $sumtr += $row['sumtr'] ;
  }
    $rec .= "<tr>
      <td align='right'><b>Итоги:</b></td>
      <td align='right'><b>" . fnum($sumtr) . "</b></td>
      <td align='right'><b>" . number_format($sumtr/1024/1024*$tarif, 2, ",", "`") . "</b></td>
      </tr>\n" ;
} // end of else from if(isset($day))
 
 
// сборка контента <body>
$body = "
<table width='550px'>
  <tr><td colspan='3' align='center'><h2>$header</h2></td></tr>
  <tr>
    <td valign='top' width='15%' align='center'>
      $months
    </td>
    <td valign='top' width='5%' align='center'>
      $days
    </td>
    <td valign='top' width='80%' align='right'>
      <table border='1' cellpadding='4' cellspacing='0' width='380'>
        $rec
      </table>
    </td>
  </tr>
</table>
<a href='daytraf.php?date=" . strftime("%Y-%m-%d") . "' target='_blank'>Сырая сборка по компам и юзерам за сегодня</a><br>
<a href='stat1.php' target='_blank'>Общая статистика IP-трафика</a>
" ;
 
 
// вывод странички
echo '<html>
<head>
<title>' . $header . '</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">
<style>
a {color: #0000ff; text-decoration: none;}
.act {color: #ff0000; text-decoration: none; font-weight: bold;}
</style>
</head>
<body>
' . $body . '
</body>
</html>' ;
?>

Просмотр статистики по машинам - stat1.php

Файл stat1.php

<?php
/*
Отображение текущей статистики по машинам.
существенно доработаный скрипт Лисяры
*/
 
include_once "dbconnect.php" ;
include_once "auth.php" ;
 
// текущие даты
$c_month = date("Y-m") ;  // год-месяц (текущий)
$c_day = date("Y-m-d") ;  // год-месяц-число (сегодняшнее)
 
// Смотрим, что спрашивают
if ( !empty($_GET["mdate"]) && ( preg_match("/^[\d]{4}-[\d]{2}-[\d]{2}$/", $_GET["mdate"]) == 1 || preg_match("/^[\d]{4}-[\d]{2}$/", $_GET["mdate"]) == 1  || preg_match("/^[\d]{4}$/", $_GET["mdate"]) == 1 ) ) {
  $mdate = htmlentities($_GET["mdate"]) ; // дата из GET-запроса, если прально сформирована...
  $c_month = substr($mdate . date("-m"), 0, 7) ; // значение года-месяца из выбранной даты
}
else {
  $mdate = $c_month ; // ....иначе - тупо, текущая
}
$c_year = substr($mdate, 0, 4) ; // значение года из выбранной даты
$show_month = $c_year . "_" . substr($c_month, 5, 2) ; // отображаемый год_месяц
 
// давать ли подробную статистику (по портам)
$portfrom = ( intval($_GET["portfrom"]) == 1 );
 
// сортировка (кусок запроса)
if ( !empty($_GET["sort"]) ) {
	switch ($_GET["sort"]) {
	case "ip":
		$sort = 'RIGHT(CONCAT("00",SUBSTRING_INDEX(`to_IP`, ".", -1)), 3)' ;
	  break ;
	case "uname":
		$sort = "`name`" ;
	  break ;
	default:
	  $sort = "`bytes`" ;
          $sortd = "DESC" ;
  }
}
else {
  $sort = "`bytes`" ;
  $sortd = "DESC" ;
}
?>
<html>
<head>
<title>Страница статистики</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1251">
<style type="text/css">
  #main_table .hilightoff {background: white}
  #main_table .hilighton {background: #ccbbff}
  a {color: #0000ff; text-decoration: none;}
  .act {color: #ff0000; text-decoration: none; font-weight: bold;}
</style>
 
</head>
<body>
<big><center>СТАТИСТИКА ИНТЕРНЕТА</center></big><br><br>
<table border="0" cellspacing="0" cellpadding="0" width="100%">
<tbody>
	<tr>
		<td width="<?php echo ($portfrom) ? "700" : "600" ; ?>px" align="center" valign="top">
<table border="1" cellspacing="0" cellpadding="2" width="90%" id="main_table">
<tbody>
  <tr>
    <th align="center" colspan="<?php echo ($portfrom) ? 4 : 3 ; ?>"><?php echo $mdate ; ?></th>
  </tr>
  <tr>
    <th width="15%"><a href='?sort=ip&mdate=<?php echo $mdate ; ?>'>IP адрес</a></th>
    <th width="35%"><a href='?sort=uname&mdate=<?php echo $mdate ; ?>'>компьютер</a></th>
    <th width="20%"><a href='?sort=tr&mdate=<?php echo $mdate ; ?>'>скачано</a> МБ</th>
<?php
 
if ($portfrom) {
	echo "<th width='20%'>`port_from_IP`</th>" ;
}
echo "</tr>" ;
 
// вытаскиваем статистику по пользователям
$sql = "
  SELECT
  	`to_IP`, SUM(`bytes`) AS `bytes`,
  	`name`
  FROM
  	`" . $IF_int_gw . "_" . $show_month . "`
  	LEFT JOIN
  		`users`
  			ON `" . $IF_int_gw . "_" . $show_month . "`.`to_IP`=CONCAT(\"" . $lan_mask . "\", `users`.`ip`)
  WHERE
  	`date` LIKE '" . $mdate . "%' AND
  	`from_IP` != '" . $ip_out_gw . "' AND
  	`to_IP` != '" . $ip_out_gw . "' AND
  	`to_IP` != '" . $ip_int_gw . "' AND
  	`from_IP` != '" . $ip_int_gw . "' AND
  	`to_IP` LIKE '" . $lan_mask . "%'
  GROUP BY
  	`to_IP`
  ORDER BY 
    " . $sort . " " . $sortd ;
$traf = mysql_query( $sql ) ;
 
// Строим табличку по юзерам
while ($d = mysql_fetch_assoc($traf)) {
  $bytes = round($d['bytes'] / 1048576, 2);
	$sumbytes += $d['bytes'] ;
	if ($portfrom) { // если надо, выдаем по некоторым портам (список в запросе)
  	$sql = "
      SELECT
      	`port_from_IP`, SUM(`all_bytes`) AS `bytes`
      FROM
      	`" . $IF_int_gw . "_" . $show_month . "`
      WHERE
      	`date` LIKE '" . $mdate . "%' AND
      	`to_IP` = '" . $d['to_IP'] . "' AND
      	`port_from_IP` IN (21,22,25,53,80,110,119,143,443,3128,3306,5190,5222,8080,8081,'client')
      GROUP BY
      	`port_from_IP`
      ORDER BY
      	CONVERT(`port_from_IP`, UNSIGNED)" ;
		$trafp = mysql_query( $sql ) ;
	}
?>
  <tr class=hilightoff onmouseover="className='hilighton';" onmouseout="className='hilightoff';">
    <td><?php echo $d['to_IP'] ; ?></td>
    <td><?php echo $d['name'] ; ?></td>
    <td align="center"><?php echo $bytes ; ?></td>
<?php
	if ($portfrom) { // по портам
    echo "<td>" ;
    while ($p = mysql_fetch_array($trafp)) {
    	echo $p["port_from_IP"] . " = " . round($p['bytes'] / 1048576, 2) . "<br>" ;
    }
    echo "&nbsp;</td>" ;
	}
  echo "</tr>" ;
}
 
echo "</TBODY></TABLE>
  Всего: " . round( $sumbytes / 1048576, 2) . " мегабайт<br>
  </TD>
  <TD align='center' valign='top'>
  <a href='?sort=" . $_GET["sort"] . "&mdate=" . $mdate . "&portfrom=" . (($portfrom)?"0'>Кратко":"1'>По портам") . "</a><br /><br />" ;
 
// сцылки по месяцам
$sql = "SHOW TABLES LIKE '".$IF_int_gw."%'" ;
$tables = mysql_query( $sql ) ;
while ($t = mysql_fetch_assoc($tables)) { // цикл по дням текущего месяца
  $t = array_values($t) ; // чтоб не связываться с именем поля
  $ym = split('_', $t[0]);
  $class = ( $ym[1]."-".$ym[2] == substr($mdate, 0, 7) ) ? " class='act'" : "" ;
  echo "<a href='?sort=" . $_GET["sort"] . "&mdate=" . $ym[1]."-".$ym[2] . "&portfrom=" . $portfrom . "'" . $class . ">[ " . $ym[1]."-".$ym[2] . " ]</a><br>" ;
}
 
echo "<br>" ;
 
// ссылки по датам за месяц
$sql = "
  SELECT
  	`date`
  FROM
  	`" . $IF_int_gw . "_" . $show_month . "`
  WHERE
  	`date` LIKE '" . $c_month . "%'
  GROUP BY
  	`date`
  ORDER BY
  	`date` 
" ;
$dates = mysql_query( $sql ) ;
while ($d = mysql_fetch_assoc($dates)) { // цикл по дням текущего месяца
  $class = ( $d["date"] == $mdate ) ? " class='act'" : "" ;
  echo "<a href='?sort=" . $_GET["sort"] . "&mdate=" . $d["date"] . "&portfrom=" . $portfrom . "'" . $class . ">" . $d["date"] . "</a><br>" ;
}
 
echo "</TD></TR></TBODY></TABLE><br><br>" ;
 
// считаем траффик на внешнем интерфейсе
$sql = "
SELECT
	SUM(`all_bytes`) AS `outtraf`
FROM
	`" . $IF_out_gw . "_" . $show_month . "`
WHERE
	`to_IP` ='" . $ip_out_gw . "' AND
	`date` LIKE '" . $mdate . "%'" ;
 
$traffic_curr = mysql_fetch_array( mysql_query( $sql ) );
$traffic_curr_meg = $traffic_curr["outtraf"] / 1048576;
$traffic_curr_meg = round( $traffic_curr_meg, 2 );
 
echo "Траффик на внешнем интерфейсе за " . $mdate . ": <b>" . number_format( $traffic_curr["outtraf"], 0, ',', ' ' ) . "</b> байт<br></BODY></HTML>" ;
?>

Некоторые замечания

К сожалению, считается трафик, который отдает шлюз для клиента локалки. Таким образом, в подсчет попадает и локальный трафик. В данном конкретном случае он не велик.

Практически в неизменном виде эта программа работает немногим более года. Разок вылезли непонятные глюки, когда на пользователя был засчитан ну ооооооочень большой объем трафика, не соответствующий входящему. Причина пока не установлена

Персональные инструменты