From: Roman Bazalevsky <rvb@rvb.name>
Date: Fri, 13 Dec 2019 12:42:03 +0000 (+0300)
Subject: Squid report generator (MySQL storage, reports/graphs online).
X-Git-Url: https://git.rvb.name/squid-reports.git/commitdiff_plain/b6140f5887fbec2842858d934c99618b1bf447d2?ds=inline

Squid report generator (MySQL storage, reports/graphs online).
---

b6140f5887fbec2842858d934c99618b1bf447d2
diff --git a/fetch/fetch.pl b/fetch/fetch.pl
new file mode 100755
index 0000000..a3159ef
--- /dev/null
+++ b/fetch/fetch.pl
@@ -0,0 +1,126 @@
+#!/usr/bin/perl
+
+#build 20191105
+
+use DBI; # DBI  Perl!!!
+use URI;
+
+#=======================CONFIGURATION BEGIN============================
+
+#mysql default config
+$host = "localhost"; # host s DB
+$port = "3306"; # port DB
+$user = "squid"; # username k DB
+$pass = "ASxjC7Gftx8IvRfF"; # pasword k DB
+$db = "squid"; # name DB
+
+#==========================================================
+#Path to access.log. It could be full path, e.g. /var/log/squid/access.log
+#Put k access.log. Eto mozhet bit polnii put. Naprimer, /var/log/squid/access.log
+#Путь к файлу access.log(имя может другим). Это может быть и полный путь, например, /var/log/squid/access.log
+
+my $filetoparse="/var/log/squid/access.log";
+#==========================================================
+
+#How older data must be deleted. In example, older than 100 days from max date.
+#Period, starshe kotorogo dannie budut udaliatsia. Ukazivaetsia v dniah.
+#Период, старше которого данные будут удаляться. Указывается в днях.
+
+my $deleteperiod=120; #days
+#==========================================================
+#min bytes of traffic in one record to write into DB. By default - all data stored.
+
+my $minbytestoparse=-1; #bytes, default -1
+
+#=======================CONFIGURATION END==============================
+
+$count=0;
+$lastdate=0;
+$sqltext="";
+$sql_getlastdate="";
+
+$sqlbatch=100;
+$overlap=300;
+
+#datetime when parse started
+print $now=localtime;
+$startnow=time;
+
+$dbh = DBI->connect("DBI:mysql:$db:$host:$port",$user,$pass);
+
+$sth = $dbh->prepare("SELECT ifnull(unix_timestamp(max(access_date))-$overlap,0) from access_log");
+$sth->execute;
+@row=$sth->fetchrow_array;
+$lastdate=$row[0];
+
+if ($deleteperiod) {
+  $sth = $dbh->prepare("delete from access_log where access_date <  date_sub(now(), interval $deleteperiod day)");
+  $sth->execute;
+}
+
+#open log file for reading
+open(IN, "<$filetoparse"); 
+
+$countlines=0; 
+$countadded=0;
+
+print "\n";
+
+$sqlspool = 0;
+$sqltext = "";
+
+#loop for get strings from file one by one.
+while (my $line=<IN>) {
+
+    if(time > $seconds+1) {
+        $seconds=time;
+        $insertspeed=$countinsert;
+    }
+    $countinsert++;
+
+    @item = split " ", $line; 
+
+    $bytes = $item[4];
+   $time  = $item[0];
+    if (($bytes>0) && ($time>$lastdate)) {
+        $ms = $item[1];
+        $client_ip = $item[2];
+        $http_code = $item[3];
+        $method = $item[5];
+        $url = $item[6];
+        @matches = ($url=~ /(?:^[a-z][a-z0-9+\-.]*:\/\/)?(?:[a-z0-9\-._~%!$&'()*+,;=]+(?::.*)?@)?([a-z0-9\-._~%]+|\[[a-z0-9\-._~%!$&'()*+,;=:]+\])/i);
+        $host=$matches[0];
+        $user = $item[7];
+        $mime = $item[9];
+        if ($sqltext) {
+          $sqltext=$sqltext.",($time,$ms,'$client_ip','$http_code',$bytes,'$method','$host','$user','$mime')";
+        } else {
+          $sqltext="($time,$ms,'$client_ip','$http_code',$bytes,'$method','$host','$user','$mime')";
+        }
+        $sqlspool++;
+        if ($sqlspool > $sqlbatch) {
+          $sqltext  = "insert into tmp_traffic(timestamp,process_time,client_host,http_code,bytes,http_method,server_host,username,mime_type) values".$sqltext;
+          $sth = $dbh->prepare($sqltext);
+          $sth->execute;
+          $sqlspool = 0;
+          $sqltext = "";
+        }
+        print "Completed: ".$countlines." ".$insertspeed." lines/sec\r";
+        $countlines++;
+    }
+    
+}
+
+if ($sqltext) {
+  $sqltext  = "insert into tmp_traffic(timestamp,process_time,client_host,http_code,bytes,http_method,server_host,username,mime_type) values".$sqltext;
+  $sth = $dbh->prepare($sqltext);
+  $sth->execute;
+}
+
+print "\n";
+close(IN);
+
+$sth = $dbh->prepare("CALL Process_Traffic();");
+$sth->execute;
+
+$rc = $dbh->disconnect;
diff --git a/mysql/squid.sql b/mysql/squid.sql
new file mode 100644
index 0000000..ac8861c
--- /dev/null
+++ b/mysql/squid.sql
@@ -0,0 +1,317 @@
+-- MySQL dump 10.13  Distrib 5.7.28, for Linux (x86_64)
+--
+-- Host: localhost    Database: squid
+-- ------------------------------------------------------
+-- Server version	5.7.28-0ubuntu0.18.04.4
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `access_log`
+--
+
+DROP TABLE IF EXISTS `access_log`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `access_log` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `access_date` datetime NOT NULL,
+  `process_time` int(11) DEFAULT NULL,
+  `host_id` int(11) NOT NULL,
+  `code_id` int(11) NOT NULL,
+  `bytes` int(11) DEFAULT NULL,
+  `method_id` int(11) NOT NULL,
+  `site` varchar(1024) DEFAULT NULL,
+  `user_id` int(11) NOT NULL,
+  `mime_id` int(11) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `KEY` (`access_date`,`host_id`,`bytes`,`user_id`)
+) ENGINE=InnoDB AUTO_INCREMENT=112422 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `column_names`
+--
+
+DROP TABLE IF EXISTS `column_names`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `column_names` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `name` varchar(45) NOT NULL,
+  `alias` varchar(45) NOT NULL,
+  `template` varchar(1024) DEFAULT NULL,
+  `template_name` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `column_names`
+--
+
+LOCK TABLES `column_names` WRITE;
+/*!40000 ALTER TABLE `column_names` DISABLE KEYS */;
+INSERT INTO `column_names` VALUES (1,'username','Логин',NULL,''),(2,'useralias','Пользователь',NULL,NULL),(3,'mb','Трафик, Мб',NULL,'column-right'),(4,'avg_kbits','Средняя скорость, Кбит/c',NULL,'column-right'),(5,'hostname','Адрес',NULL,NULL),(6,'hostalias','Устройство','',NULL),(7,'site','Сайт',NULL,NULL),(8,'requests','Количество обращений','',NULL),(9,'_user_id','Пользователь',NULL,NULL),(10,'_host_id','Хост','',NULL),(11,'hosts','Хосты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-host\',{user_id:$_user_id;});\">$hosts;</td>',''),(12,'users','Пользователи','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-user\',{host_id:$_host_id;});\">$users;</td>',''),(13,'hosts_s','Хосты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-host\',{site:\'$site;\'});\">$hosts_s;</td>',''),(14,'users_s','Пользователи','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-user\',{site:\'$site;\'});\">$users_s;</td>',''),(15,'sites_u','Сайты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-site\',{user_id:$_user_id;});\">$sites_u;</td>',NULL),(16,'sites_h','Сайты','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-site\',{host_id:$_host_id;});\">$sites_h;</td>',NULL),(17,'username_hh','Логин','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-hour\',{user_id:$_user_id;});\">$username_hh;</td>',''),(18,'ip','Хост-клиент',NULL,''),(19,'uri','Запрос',NULL,''),(20,'bytes','Байт передано',NULL,NULL),(21,'seconds','Активен, с','',''),(22,'host','Хост-клиент','<td>$host;($_ip;)</td>',''),(23,'user','Логин','',''),(24,'hour','Час',NULL,NULL),(25,'hostname_hh','Хост','<td class=\"clickable-numeric\" onclick=\"ShowFilteredRep(\'by-hour-hosts\',{host_id:$_host_id;});\">$hostname_hh;</td>','');
+/*!40000 ALTER TABLE `column_names` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `hosts`
+--
+
+DROP TABLE IF EXISTS `hosts`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `hosts` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `hostname` varchar(45) NOT NULL,
+  `alias` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `hostname_UNIQUE` (`hostname`),
+  UNIQUE KEY `alias_UNIQUE` (`alias`)
+) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `http_codes`
+--
+
+DROP TABLE IF EXISTS `http_codes`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `http_codes` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `http_code` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `http_code_UNIQUE` (`http_code`)
+) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `http_methods`
+--
+
+DROP TABLE IF EXISTS `http_methods`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `http_methods` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `method` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `method_UNIQUE` (`method`)
+) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `mime_types`
+--
+
+DROP TABLE IF EXISTS `mime_types`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `mime_types` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `mime_type` varchar(45) NOT NULL,
+  `alias` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `mime_type_UNIQUE` (`mime_type`),
+  UNIQUE KEY `alias_UNIQUE` (`alias`)
+) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `rep`
+--
+
+DROP TABLE IF EXISTS `rep`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `rep` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `cat_mnemo` varchar(45) NOT NULL,
+  `mnemo` varchar(16) DEFAULT NULL,
+  `name` varchar(45) DEFAULT NULL,
+  `description` varchar(1024) DEFAULT NULL,
+  `query` varchar(1024) DEFAULT NULL,
+  `has_total` varchar(45) DEFAULT NULL,
+  `graph_x` varchar(45) DEFAULT NULL,
+  `graph_series` varchar(45) DEFAULT NULL,
+  `graph_y` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `name_UNIQUE` (`name`)
+) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `rep`
+--
+
+LOCK TABLES `rep` WRITE;
+/*!40000 ALTER TABLE `rep` DISABLE KEYS */;
+INSERT INTO `rep` VALUES (1,'user','by-user','Трафик по пользователям','Распределение трафика по авторизованным пользователям','select 0 as _ordr,t.user_id as _user_id,u.username,u.alias as useralias,round(t.bytes/(1024*1024),2) mb,hosts,sites_u,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct host_id) hosts,count(distinct site) sites_u from access_log where (1=1) $FILTER; group by user_id) t left join users u on t.user_id=u.id union all select 1,null,\'ИТОГО:\',\'\',round(SUM(bytes)/(1024*1024),2) mb,\'\',\'\',round((SUM(bytes)*8/SUM(process_time/1000))/1024) avg_kbits from access_log where (1=1) $FILTER; order by _ordr,mb desc\n','1',NULL,NULL,NULL),(2,'host','by-host','Трафик по хостам','Распределение трафика по клиентским хостам','select 0 as _ordr,t.host_id as _host_id,h.hostname,h.alias as hostalias,round(t.bytes/(1024*1024),2) mb,sites_h,users,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select host_id,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct user_id) users, count(distinct site) sites_h from access_log where (1=1) $FILTER; group by host_id) t left join hosts h on t.host_id=h.id union all select 1,null,\'ИТОГО:\',\'\',round(SUM(bytes)/(1024*1024),2) mb,\'\',\'\',round((SUM(bytes)*8/SUM(process_time/1000))/1024) avg_kbits from access_log where (1=1) $FILTER; order by _ordr,mb desc','1',NULL,NULL,NULL),(3,'site','top-sites','Сайты с наибольшим трафиком','Сайты с нибольшим объемом трафика за интервал времени','select site,round(sum(bytes)/(1024*1024),2) mb,count(*) requests,count(distinct user_id) as users_s,count(distinct host_id) as hosts_s from access_log where (1=1) $FILTER; group by site order by mb desc limit 20 ','0',NULL,NULL,NULL),(4,'site','top-requests','Часто используемые сайты','Сайты с наибольшим количеством запросов','select site,round(sum(bytes)/(1024*1024),2) mb,count(*) requests,count(distinct user_id) as users_s,count(distinct host_id) as hosts_s from access_log where (1=1) $FILTER; group by site order by requests desc limit 20 ','0',NULL,NULL,NULL),(7,'site','by-site','Трафик по сайтам','Распределение трафика по сайтам','select 0 as _ordr,site,round(t.bytes/(1024*1024),2) mb,hosts as hosts_s,users as users_s,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select site,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct host_id) hosts,count(distinct user_id) users from access_log where (1=1) $FILTER; group by site) t union all select 1,\'ИТОГО:\',round(SUM(bytes)/(1024*1024),2) mb,\'\',\'\',round((SUM(bytes)*8/SUM(process_time/1000))/1024) avg_kbits from access_log where (1=1) $FILTER; order by _ordr,mb desc','1',NULL,NULL,NULL),(8,'user','big-files','Большие файлы','Кто скачивает большие файлы','select 0 as _ordr,t.user_id as _user_id,u.username,u.alias as useralias,site,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,site,sum(bytes) bytes,sum(process_time/1000) seconds from access_log where bytes>10*1024*1024 $FILTER; group by user_id,site) t left join users u on t.user_id=u.id order by mb desc limit 20\n','0',NULL,NULL,NULL),(9,'user','by-hour','Трафик по времени суток','Распределеение трафика по пользователям в различные часы','select hour,t.user_id as _user_id,u.username as username_hh,u.alias as useralias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,date_format(access_date,\'%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by user_id,date_format(access_date,\'%H\')) t left join users u on t.user_id=u.id order by hour,mb desc','','hour','useralias','mb'),(10,'host','by-hour-hosts','Трафик по хостам по времени cуток','Распределение трафика по хостам по времени суток','select hour,t.host_id as _host_id,h.hostname as hostname_hh,h.alias as hostalias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select host_id,date_format(access_date,\'%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by host_id,date_format(access_date,\'%H\')) t left join hosts h on t.host_id=h.id order by hour,mb desc',NULL,'hour','hostalias','mb'),(11,'user','by-time','Трафик по времени','Распределеение трафика по пользователям в различные часы','select hour,t.user_id as _user_id,u.username as username_hh,u.alias as useralias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select user_id,date_format(access_date,\'%Y%m%d%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by user_id,date_format(access_date,\'%Y%m%d%H\')) t left join users u on t.user_id=u.id order by hour,mb desc','','hour','useralias','mb'),(12,'host','by-time-hosts','Трафик по хостам по времени','Распределение трафика по хостам по времени суток','select hour,t.host_id as _host_id,h.hostname as hostname_hh,h.alias as hostalias,round(t.bytes/(1024*1024),2) mb,round((t.bytes*8/t.seconds)/(1024)) avg_kbits from (select host_id,date_format(access_date,\'%Y%m%d%H\') hour,sum(bytes) bytes,sum(process_time)/1000 seconds,count(distinct site) sites from access_log where (1=1) $FILTER; group by host_id,date_format(access_date,\'%Y%m%d%H\')) t left join hosts h on t.host_id=h.id order by hour,mb desc','','hour','hostalias','mb');
+/*!40000 ALTER TABLE `rep` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `rep_cat`
+--
+
+DROP TABLE IF EXISTS `rep_cat`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `rep_cat` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `mnemo` varchar(45) DEFAULT NULL,
+  `name` varchar(45) DEFAULT NULL,
+  `description` varchar(128) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `name_UNIQUE` (`name`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `rep_cat`
+--
+
+LOCK TABLES `rep_cat` WRITE;
+/*!40000 ALTER TABLE `rep_cat` DISABLE KEYS */;
+INSERT INTO `rep_cat` VALUES (1,'user','Пользователи','Отчеты по авторизованным пользователям'),(2,'host','Хосты','Отчеты по клиентским подключениям'),(3,'site','Сайты','Отчеты по посещаемым сайтам');
+/*!40000 ALTER TABLE `rep_cat` ENABLE KEYS */;
+UNLOCK TABLES;
+
+--
+-- Table structure for table `tmp_traffic`
+--
+
+DROP TABLE IF EXISTS `tmp_traffic`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tmp_traffic` (
+  `timestamp` decimal(38,3) DEFAULT NULL,
+  `process_time` int(11) DEFAULT NULL,
+  `client_host` varchar(128) DEFAULT NULL,
+  `http_code` varchar(45) DEFAULT NULL,
+  `bytes` int(11) DEFAULT NULL,
+  `http_method` varchar(45) DEFAULT NULL,
+  `server_host` varchar(128) DEFAULT NULL,
+  `username` varchar(45) DEFAULT NULL,
+  `mime_type` varchar(45) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `users`
+--
+
+DROP TABLE IF EXISTS `users`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `users` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `username` varchar(45) NOT NULL,
+  `alias` varchar(45) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `username_UNIQUE` (`username`),
+  UNIQUE KEY `alias_UNIQUE` (`alias`)
+) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `web_templates`
+--
+
+DROP TABLE IF EXISTS `web_templates`;
+/*!40101 SET @saved_cs_client     = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `web_templates` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `mnemo` varchar(32) DEFAULT NULL,
+  `description` varchar(128) DEFAULT NULL,
+  `body` varchar(8192) DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `mnemo_UNIQUE` (`mnemo`)
+) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Dumping data for table `web_templates`
+--
+
+LOCK TABLES `web_templates` WRITE;
+/*!40000 ALTER TABLE `web_templates` DISABLE KEYS */;
+INSERT INTO `web_templates` VALUES (3,'menuitem','Вложенные пункты меню','<li class=\"menu-item\" onclick=\"ShowRep(\'$MNEMO;\');\">$NAME;</li>'),(4,'menugroup','Подменю','<div class=\"category\" id=\"$MNEMO;\" onclick=\"ShowHide(\'_li_$MNEMO;\');\">$NAME;</div><li id=\"_li_$MNEMO;\" class=\"slide\"><ul class=\"slide-menu\">$MENUITEM;</ul></li>'),(6,'menu','Верхний уровень меню','<ul class=\"nav\">$MENUGROUP;</ul>'),(9,'report-table','Общий шаблон таблицы','<table id=\"report\">$HEADER;$LINES;</table>'),(11,'report-table-total','Общий шаблон таблицы с итогом','<table id=\"report\">$HEADER;$LINES;$TOTAL;</table>'),(15,'column','Отображение столбца по умолчанию','<td>$VALUE;</td>'),(16,'header-column','Отображение заголовка по умолчанию','<th>$VALUE;</th>'),(17,'table-row','Строка таблицы','<tr>$DATA;</tr>'),(18,'header-row',NULL,'<tr>$DATA;</tr>'),(19,'total-row',NULL,'<tr class=\"total\">$DATA;</tr>'),(20,'column-right','','<td class=\"numeric\">$VALUE;</td>'),(21,'filter-display','','<span class=\"filter-display\">$NAME; : $VALUE;</span>'),(22,'clear-filter','','<button onclick=\"ClearFilter(\'$NAME;\')\">X</button>'),(23,'menuonline','','<div class=\"category\" onclick=\"Online();\">Онлайн</div>');
+/*!40000 ALTER TABLE `web_templates` ENABLE KEYS */;
+UNLOCK TABLES;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+CREATE DEFINER=`admin`@`%` PROCEDURE `Process_Traffic`()
+BEGIN
+
+  insert into users(username,alias)
+  select username,username from (
+    select distinct username from tmp_traffic
+    left join users using(username)
+    where users.username is null) t;
+   
+  insert into hosts(hostname,alias)
+  select client_host,client_host from (
+    select distinct client_host from tmp_traffic
+    left join hosts on hosts.hostname=tmp_traffic.client_host
+    where hosts.hostname is null) t;
+
+  insert into http_methods(method)
+  select distinct http_method from tmp_traffic
+  left join http_methods on http_methods.method=tmp_traffic.http_method
+  where http_methods.method is null;
+
+  insert into http_codes(http_code)
+  select distinct http_code from tmp_traffic
+  left join http_codes using(http_code)
+  where http_codes.http_code is null;
+
+  insert into mime_types(mime_type,alias)
+  select mime_type,mime_type from (
+  select distinct mime_type from tmp_traffic
+  left join mime_types using(mime_type)
+  where mime_types.mime_type is null) t;
+  
+  insert into access_log(access_date,process_time,host_id,code_id,bytes,method_id,site,user_id,mime_id)
+  select t.access_date,t.process_time,t.host_id,t.code_id,t.bytes,t.method,t.server_host,t.user_id,t.mime_id 
+  from
+  (
+    select from_unixtime(round(timestamp)) access_date,process_time,h.id host_id,c.id code_id,bytes,m.id method,t.server_host,u.id user_id,mt.id mime_id
+    from tmp_traffic t, users u,hosts h,http_methods m,http_codes c,mime_types mt
+    where 
+      t.username=u.username and 
+      t.client_host=h.hostname and
+      t.http_method=m.method and
+      t.http_code=c.http_code and
+      t.mime_type=mt.mime_type
+    ) t
+    left join access_log a
+    on t.access_date=a.access_date and t.host_id=a.host_id and t.code_id=a.code_id and t.bytes=a.bytes and t.method=a.method_id and t.server_host=a.site and t.user_id=a.user_id
+    where a.id is null;
+    
+  delete from tmp_traffic;
+  
+END;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2019-12-13 15:39:51
diff --git a/web/api.php b/web/api.php
new file mode 100644
index 0000000..2b700e2
--- /dev/null
+++ b/web/api.php
@@ -0,0 +1,196 @@
+<?
+
+  require_once("config.php");
+  
+  header("Access-Control-Allow-Orgin: *");
+  header("Access-Control-Allow-Methods: *");
+  header("Content-Type: application/json");
+
+  $requestUri = explode('/', trim($_SERVER['REQUEST_URI'],'/'));
+  $requestParams = $_REQUEST;
+
+  $method = $_SERVER['REQUEST_METHOD'];  
+
+  function requestStatus($code) {
+    $status = array(
+        200 => 'OK',
+        404 => 'Not Found',
+        405 => 'Method Not Allowed',
+        500 => 'Internal Server Error',
+      );
+    return ($status[$code])?$status[$code]:$status[500];
+  } 
+  
+  function response($data, $status = 500) {
+    header("HTTP/1.1 " . $status . " " . requestStatus($status));
+    print json_encode($data);
+  }
+
+  function connect_db() {
+  
+    global $db,$mysql_host,$mysql_port,$mysql_schema,$mysql_user,$mysql_pwd;
+  
+    if (! ($db = new PDO("mysql:host=$mysql_host;port=$mysql_port;dbname=$mysql_schema",$mysql_user,$mysql_pwd,array( PDO::ATTR_PERSISTENT => false)))) {
+      die($err);
+    }  
+    $db -> exec('SET CHARACTER SET utf8');
+
+    return $db;
+
+  }
+
+  function exec_query($sql) {
+
+    global $db;
+
+    $q = $db -> prepare( $sql );
+    $q -> bindParam(':s',$auth_token,PDO::PARAM_INT);
+    $q -> execute();
+
+    return $q -> fetchall(PDO::FETCH_ASSOC);
+
+  }
+
+  $api = $requestParams["method"];
+
+  $data = Array();
+  $code = 200;
+  switch ($api) {
+    case "get-base-config":
+
+      $data["site-header"] = $site_header;
+      
+      connect_db();   
+      
+      $cats = exec_query("select mnemo,name,description from rep_cat");
+
+      foreach ($cats as $cat => $value) {
+        
+        $cmnemo = $cats[$cat]["mnemo"];
+        $reps = exec_query("select mnemo,name,description,graph_x,graph_y,graph_series from rep where cat_mnemo='$cmnemo'");
+        $cats[$cat]["reps"] = $reps;
+      
+      }
+      
+      $data["cats"] = $cats;
+      $data["templates"] = exec_query("select mnemo,body from web_templates");
+      $data["columns"] = exec_query("select * from column_names");
+      $users = exec_query("select id,username as name,alias from users");
+      $hosts = exec_query("select id,hostname as name,alias from hosts");
+      $data["dictionaries"] = Array( "user_id" => $users, "host_id" => $hosts);
+
+      break;
+
+    case "report":
+    
+      $mnemo = $requestParams["mnemo"];
+      
+      connect_db();    
+      
+      $rec = exec_query("select has_total,query from rep where mnemo='$mnemo'")[0];
+      $sql = $rec["query"];
+      $data["has_total"] = $rec["has_total"];
+
+      $filter_str = "";
+      $filter = Array();
+      
+      foreach ($requestParams as $name => $value) {
+      
+        if (($name == "mnemo") || ($name == "method")) { continue; }
+        if ($name == "date_from") { $filter_str = $filter_str." and access_date>='$value'"; }
+        elseif ($name == "date_to") { $filter_str = $filter_str." and access_date<date_add('$value',interval 1 day)"; }
+        else { $filter_str = $filter_str." and $name = '$value'"; $filter[$name] = $value; }
+      }
+
+      if ($filter_str) {
+        $sql = preg_replace('/\$FILTER;/',$filter_str,$sql);
+      }
+
+      if ($filter) {
+        $data["filter"] = $filter;
+      }
+      $result = exec_query($sql);
+      if ($result) {
+        $data["dictionary"] = array_keys($result[0]);
+        $data["data"] = $result;
+      } else {
+        $data["sql"] = $sql;
+      }
+    
+      break;
+
+    case "online":
+    
+      if($squid_passwd != "") { $pwd.="cachemgr:$cachemgr_passwd@"; } else { $pwd = ""; }
+      $url = "http://".$pwd.$squid_host.":".$squid_port."/squid-internal-mgr/active_requests";
+
+      $ch = curl_init($url);
+  
+      $options = array(
+        CURLOPT_RETURNTRANSFER => true,   // return web page
+        CURLOPT_HEADER         => false,  // don't return headers
+        CURLOPT_FOLLOWLOCATION => false,   // follow redirects
+        CURLOPT_MAXREDIRS      => 0,     // stop after 10 redirects
+        CURLOPT_ENCODING       => "",     // handle compressed
+        CURLOPT_USERAGENT      => "web", // name of client
+        CURLOPT_AUTOREFERER    => true,   // set referrer on redirect
+        CURLOPT_CONNECTTIMEOUT => 3,    // time-out on connect
+        CURLOPT_TIMEOUT        => 5,    // time-out on response
+      );
+
+      curl_setopt_array($ch, $options);
+
+      $reply = curl_exec($ch);
+      $retcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
+
+      // 400 means not found, 200 means found.
+      curl_close($ch);
+
+      if($retcode == 200) {
+
+        $data["active"] = Array();
+        
+        if(preg_match("/HTTP/1.0 200 OK/",$ptmp)){
+          $code = 500;
+          $data=Array("error" => "No connection to Squid");
+        } else {
+          preg_match_all("/username(.+)/",$reply,$user);
+          preg_match_all("/(peer|remote):(.+)/",$reply,$remote);
+          preg_match_all("/uri(.+)/",$reply,$uri);
+          preg_match_all("/out\.size(.+)/",$reply,$size);
+          preg_match_all("/\((.+)seconds/",$reply,$sec);
+          $sess = Array();
+          for ($i=0; $i< count($user[1]); $i++) {
+            $ip=trim($remote[2][$i]);
+            $reversedParts = explode(':', strrev($ip), 2);
+            $ip = strrev($reversedParts[1]);
+            $port = strrev($reversedParts[0]);
+            if (preg_match('/\[(.*)\]/',$ip,$matches)) {
+              $ip = $matches[1];
+            }
+            $host = gethostbyaddr($ip);
+            if (!$host) { $host = $ip; } 
+            $username=trim($user[1][$i]);
+            $site=trim($uri[1][$i]);
+            $datasize=trim($size[1][$i]);
+            $seconds = trim($sec[1][$i]);
+            $record = Array("_user" => $username, "_ip" => $ip, "_port"=> $port, "host" => $host, "uri" => $site, "bytes" => $datasize, "seconds" => $seconds);
+            $sess[] = $record;
+          }
+          $data["data"] = $sess;
+          $data["dictionary"] = Array("_user","_ip","_port","host","uri","bytes","seconds");
+        } 
+      
+      }
+    
+      break;
+
+    default: 
+      $data["error"] = "Method not found";
+      $data["method"] = $api;
+      $code = 404;
+  }
+  
+  response($data,$code);
+   
+?>
\ No newline at end of file
diff --git a/web/index.html b/web/index.html
new file mode 100644
index 0000000..39266c4
--- /dev/null
+++ b/web/index.html
@@ -0,0 +1,42 @@
+<!DOCTYPE html>
+<html lang="ru">
+<head>
+        <meta charset="utf-8">
+        <title>Squid reports</title>
+        <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport"/>
+        <link rel="stylesheet" href="squid.css">
+        <link rel="shortcut icon" href="squid.png">
+        <script src="squid.js"></script>
+        <script src="../js/Chart.bundle.min.js"></script>
+</head>
+<body onload="OnLoad();">
+<header>
+<div class="fill">
+<div class="container">
+<a id="logo" href="http://www.squid-cache.org/"><img src="squid.png" alt="Squid"></a>
+<a id="brand" href="/squid/"></a>
+</div>
+</div>
+</header>
+<div class="main">
+<div id="menu">
+</div>
+<div class="main-right">
+<div id="report-name"></div>
+<div id="report-dates">
+Отчет с: <input id="date-from" type="date" onchange="UpdateDates();"> 
+по: <input id="date-to" type="date" onchange="UpdateDates();">
+<span id="filter">
+Фильтр:
+</span>
+<button id="report-button" type="button" disabled onclick="Rerun();">Выполнить</button>
+</div>
+<div id="page-selector">
+<div class="selector" onclick="Display('report-body');">Статистика</div><div class="selector" onclick="Display('report-graph')">График</div>
+</div>
+<div class="report-block" id="report-body">Выберите отчет из списка слева...</div>
+<div class="report-block" id="report-graph"><canvas id="canvas"></canvas></div>
+</div>
+</div>
+</body>
+</html>
diff --git a/web/squid.css b/web/squid.css
new file mode 100644
index 0000000..fa2e0cb
--- /dev/null
+++ b/web/squid.css
@@ -0,0 +1,216 @@
+body {
+ font-size:.8rem;
+ background-color:#eee
+}
+
+html, body {
+ height:100%;
+ margin:0;
+ padding:0
+}
+
+header, .main {
+ position:absolute;
+ width:100%
+}
+
+header {
+ position:fixed;
+ z-index:2000;
+ float:left;
+ height:5rem;
+ transition:box-shadow .2s;
+ box-shadow:0 2px 5px rgba(0,0,0,.26);
+ color:white;
+ background:deepskyblue
+}
+
+footer {
+ font-size:.8rem;
+ overflow:hidden;
+ text-align:right;
+ white-space:nowrap;
+ color:#aaa;
+ text-shadow:0 0 2px #bbb;
+ padding:1rem
+}
+
+footer > a {
+ text-decoration:none;
+ color:#aaa
+}
+
+.main {
+ position:relative;
+ top:5rem;
+ bottom:0;
+ overflow-y:auto;
+ height:calc(100% - 5rem)
+}
+
+#menu {
+ position:fixed;
+ top:5rem;
+ float:left;
+ overflow-x:auto;
+ width:calc(0% + 15rem);
+ height:calc(100% - 4rem);
+ background-color:white
+}
+
+.main-right {
+ float:right;
+ width:calc(100% - 15rem);
+ height:calc(100% - 1rem);
+ background-color:#eee
+}
+
+header > .fill > .container {
+ margin-top:.5rem;
+ -webkit-user-select:none;
+ -moz-user-select:none;
+ -ms-user-select:none;
+ user-select:none;
+ padding:.5rem 1rem 0
+}
+
+header > .fill > .container > #logo {
+ margin:0 3.5rem 0 1.5rem
+}
+
+header > .fill > .container > #logo > img {
+ width: 3rem;
+ margin-top:-.1rem
+}
+
+header > .fill > .container > #brand {
+ font-size:1.4rem;
+ position:absolute;
+ cursor:default;
+ vertical-align:text-bottom;
+ text-decoration:none;
+ color:white;
+ padding-top: 0.5rem;
+}
+
+.nav {
+ font-size: 1.15rem;
+ margin-top: 0.5rem;
+ padding: 0;
+}
+
+.slide {
+ list-style-type: none;
+ padding: 0;
+ user-select: none;
+ display: none;
+}
+
+.category {
+ padding: 0.4rem 1rem;
+ cursor: pointer;
+}
+
+.slide-menu {
+ padding: 0.4rem 2rem;
+ list-style-type: none;
+ cursor: pointer;
+ user-select: none;
+}
+
+.menu-item {
+ padding: 0.2rem;
+ list-style-type: none;
+ cursor: pointer;
+ user-select: none;
+}
+
+.menu-item:hover, .category:hover {
+ background-color: lightgray;
+}
+
+#report-name {
+ padding: 1rem;
+ font-size: 2rem;
+}
+
+.selector {
+ display: inline-block;
+ cursor: pointer;
+ font-size: 1rem;
+ margin: 0 0.5rem 0 0.5rem;
+ padding: 0.5rem 2rem 0.5rem 2rem;
+}
+
+.selector:hover {
+ background-color: lightgray;
+}
+
+.report-block, #report-dates, #filter, #page-selector {
+ margin: 0 1rem 0 1rem;
+ background-color: white;
+}
+
+.report-block {
+ min-height: 50%;
+ padding: 0.2rem 1rem 1rem 1rem;
+}
+
+#report-dates {
+ font-size: 1rem;
+ padding: 0.5rem 1rem 0.5rem 1rem;
+ display: none;
+}
+
+#report-button {
+ float: right;
+}
+
+#filter {
+ display: none;
+ margin: 0;
+ padding: 0 2rem 0 2rem;
+}
+
+.fllter-button {
+ padding-right: 2rem;
+}
+
+#report {
+ width: 100%;
+}
+
+#report th {
+ font-size: 1.4rem
+}
+
+#report tr {
+ font-size: 1rem
+}
+
+#report tr:nth-child(2n+1) {
+ background-color: lightgray;
+}
+
+#report tr:nth-child(2n) {
+ background-color: white;
+}
+
+#report-graph {
+ display: none;
+ height: calc(100% - 10rem);
+}
+
+.numeric, .clickable-numeric {
+ text-align: right; 
+}
+
+.total td {
+ font-size: 1.2rem;
+ padding: 0.4rem 0 0.4rem 2rem;
+}
+
+.clickable, .clickable-numeric {
+ cursor: pointer;
+ text-decoration: underline;
+}
diff --git a/web/squid.js b/web/squid.js
new file mode 100644
index 0000000..dbcf25b
--- /dev/null
+++ b/web/squid.js
@@ -0,0 +1,638 @@
+urlbase="./api.php"
+
+refresh = 500
+online_history = 60
+
+graph_colors = [
+"salmon", "lightcoral", "crimson", "red", "darkred", "orangered",
+"gold", "orange", "yellow", "darkkhaki", "lime", "green", "greenyellow",
+"springgreen", "olive", "cyan", "turquoise", "teal", "lightskyblue",
+"dodgerblue", "royalblue", "blue", "navy", "violet", "fuchsia", 
+"darkviolet", "purple", "deeppink", "gray", "darkslategray", "sandybrown",
+"goldenrod", "chocolate", "saddlebrown", "maroon", "rosybrown", "sienna", 
+"brown", "dimgray", "mediumvioletred", "indigo", "orchid", "mediumpurple", 
+"steelblue", "powderblue", "lightseagreen", "cadetblue", "aquamarine", 
+"olivedrab", "chartreuse", "darkolivegreen", "tomato", "firebrick" 
+]
+
+currentState=""
+
+templates = {}
+columns = {}
+
+cats = {}
+reps = {}
+
+data = []
+
+current_filter = {}
+
+dictionaries = {}
+
+current_rep = ''
+
+online_traffic = null
+online_connections = null
+online_hosts = []
+
+timer = null
+
+assigned_colors = []
+
+var d = new Date();
+var curr_day = d.getDate();
+var curr_month = d.getMonth() + 1;
+var curr_year = d.getFullYear();
+today = curr_year + "-" + curr_month + "-" + curr_day;
+
+date_from = today
+date_to = today
+
+function UrlParams(params) {
+
+  var out = [];
+
+  for (var key in params) {
+    if (params.hasOwnProperty(key)) {
+      out.push(key + '=' + encodeURIComponent(params[key]));
+    }
+  }
+
+  return out.join('&');
+
+}
+
+function Macro(template, values) {
+  return templates[template].replace(/\$(\w+)\;/g,function (s,name) {
+      return values[name]
+    })
+}
+
+function ColumnMacro(column, values, macro = "column"){
+  var columnrec = columns[column]
+  var templatestr
+  if (columnrec) {
+    if (columnrec.template_name) {
+      macro = columnrec.template_name
+    }
+    templatestr = columnrec.template
+  }
+  if (templatestr) {
+    return templatestr.replace(/\$(\w+)\;/g,function (s,name) {
+      return values[name]
+    })
+  } else {
+    return Macro(macro,{VALUE:values[column]})
+  }
+}
+
+function HeaderMacro(column,macro = "header-column"){
+  var columnrec = columns[column]
+  if (columnrec) {
+    var alias = columns[column].alias;
+  } else {
+    var alias = ''
+  }
+  if (!(alias)) { alias = column }
+  return Macro(macro,{VALUE:alias})
+}
+
+function GetApi(onfinish,method,params) {
+
+  var req = new XMLHttpRequest();
+
+  req.onreadystatechange = function () {
+    if (this.readyState != 4) return; 
+    if (this.status != 200) {
+      setTimeout(OnLoad,30000);
+      return;
+    }
+    res = JSON.parse(this.responseText);
+    onfinish(res);
+  };
+
+  var url  = urlbase+"?method="+method;
+
+  if (params) {
+  
+    url = url + '&' + UrlParams(params)
+  
+  }
+
+  req.open("GET", url, true);
+  req.withCredentials = true;
+  req.send();
+
+}
+
+function UpdatePageProps(props) {
+
+  var logo = document.getElementById("brand");
+  logo.innerText = props["site-header"];
+
+  cats = props["cats"]
+
+  for (var i in res["columns"]) { 
+    columns[res["columns"][i]["name"]] = {alias:res["columns"][i]["alias"],template:res["columns"][i]["template"],template_name:res["columns"][i]["template_name"]}
+  }
+
+  for (var i in res["templates"]) { 
+    var mnemo = res["templates"][i]["mnemo"]
+    var body = res["templates"][i]["body"]
+    templates[mnemo] = body
+  }
+
+  dictionaries = res["dictionaries"]
+ 
+  menuInnerHTML = ""
+  for (var cat in cats) {
+    category = cats[cat]
+    innerHTML = ""
+    for (rep in category["reps"]) {
+      report = category["reps"][rep]
+      reps[report["mnemo"]] = report
+      reptext = Macro("menuitem",{ MNEMO:report["mnemo"], NAME:report["name"], DESCR:report["description"] })
+      innerHTML = innerHTML + reptext
+    }
+    grouptxt = Macro("menugroup",{ MNEMO:category["mnemo"], NAME:category["name"], DESCR:category["description"], MENUITEM: innerHTML})
+    menuInnerHTML = menuInnerHTML + grouptxt
+  }
+  menuInnerHTML = Macro("menuonline",{}) + menuInnerHTML
+  menuHTML = Macro("menu", { MENUGROUP: menuInnerHTML} )
+  var Left = document.getElementById("menu");
+  Left.innerHTML = menuHTML
+
+  Online();
+  
+}
+
+function SetDates() {
+  var inp = document.getElementById("date-from")
+  inp.value = date_from
+  inp.max = date_to
+
+  var inp = document.getElementById("date-to")
+  inp.value = date_to
+  inp.min = date_from
+
+  var inp = document.getElementById("report-button")
+  if (current_rep) {
+    inp.disabled = false
+  } else {
+    inp.disabled = true
+  }
+}
+
+function OnLoad() {
+
+  GetApi(UpdatePageProps,"get-base-config",null)
+  SetDates();
+
+}
+
+function ShowHide(id) {
+  var content = document.getElementById(id);
+  if (content.style.display === "block") {
+    content.style.display = "none";
+  } else {
+    content.style.display = "block";
+  }
+}
+
+function MergeTR(tr,th, macro = "column") {
+  var str = ""
+  for (i in th) {
+    if (!(th[i].startsWith('_'))) {
+      str = str + ColumnMacro(th[i],tr,macro) 
+    }
+  }
+  return str
+}
+
+function MergeTH(th, macro = "header-column") {
+  var str = ""
+  for (i in th) {
+    if (!(th[i].startsWith('_'))) {
+      str = str + HeaderMacro(th[i])  
+    }
+  }
+  return str
+}
+
+function ProduceRep(res) {
+
+  current_filter = res["filter"]
+  if (!current_filter) { current_filter = {} }
+  dictionary = res["dictionary"]
+
+  header_template = res["header"]
+  has_total = res["has_total"]
+
+  innerHTML = ""
+  data = res["data"]
+  ii=0
+
+  if (has_total == "1") {
+    total = data.pop()
+  }
+  
+  for (i in data) {
+    row_data = data[i]
+    table_row = Macro("table-row",{DATA:MergeTR(row_data,dictionary)})
+    innerHTML = innerHTML + table_row
+  }
+
+  headerHTML = Macro("header-row",{DATA:MergeTH(dictionary)})
+  
+  if (has_total == "1") {
+    totalHTML = Macro("total-row",{DATA:MergeTR(total,dictionary)})
+    reportHTML = Macro("report-table-total",{HEADER:headerHTML,LINES:innerHTML,TOTAL:totalHTML})
+  } else {
+    reportHTML = Macro("report-table",{HEADER:headerHTML,LINES:innerHTML})
+  }
+  var body = document.getElementById("report-body") 
+  body.innerHTML = reportHTML;
+
+  if (reps[current_rep].graph_x) {
+    DisplayGraph(true)
+    var config = PrepareGraphDataset(data,reps[current_rep].graph_x,reps[current_rep].graph_y,reps[current_rep].graph_series)
+    config.options.responsive = true
+    
+    config.options.scales = {
+      xAxes: [{
+        scaleLabel: {
+          display: true,
+        }
+      }],
+      yAxes: [{
+        stacked: true,
+        scaleLabel: {
+          display: true,
+        },
+        ticks: {
+          suggestedMin: 0,    // minimum will be 0, unless there is a lower value.
+        }
+      }]
+    }
+    DrawGraph(config)
+  } else {
+    DisplayGraph(false)
+  }
+
+}
+
+function DrawGraph(config) {
+  var ctx = document.getElementById('canvas').getContext('2d')
+  var div = document.getElementById("report-graph");
+  canvas.width = div.style.width;
+  canvas.height = div.style.height;
+  window.Graph = new Chart(ctx, config);
+}
+
+function AssignColor(key) {
+  if (assigned_colors[key]) {
+    return assigned_colors[key]
+  }
+  var rand_color 
+  if (graph_colors.length) { 
+    var idx = Math.floor(Math.random() * graph_colors.length)
+    rand_color = graph_colors[idx]
+    graph_colors.splice(idx,1)
+  } else {
+    rand_color = "darkgray"
+  }
+  assigned_colors[key] = rand_color
+  return rand_color
+}
+
+function PrepareGraphDataset(data,graph_x,graph_y,graph_series) {
+  
+  var xvals = []
+  var series = []
+
+  var values = []
+
+  for (i in data) {
+    rec = data[i]
+    for (key in rec) {
+      if (key == graph_x) {
+        if (!xvals.includes(Number(rec[key]))) {
+          xvals.push(Number(rec[key]))
+        }
+      }
+      if (key == graph_series) {
+        if (!series.includes(rec[key])) {
+          series.push(rec[key])
+        }
+      }
+    }
+  }
+  
+  xvals.sort(function(a,b) { return a-b; })
+  series.sort()
+
+  for (var i in series) {
+    values[i] = {}
+    values[i].data = []
+    values[i].label = series[i]
+    values[i].fill = true
+    values[i].borderColor = AssignColor(values[i].label)
+    values[i].backgroundColor = AssignColor(values[i].label)
+    for (var j in xvals) {
+      values[i].data[j] = 0
+    } 
+  }
+
+  for (var k in data) {
+    rec = data[k]
+    xval = null
+    yval = null
+    dataset = null
+    for (key in rec) {
+      if (key == graph_x) {
+        xval = Number(rec[key])
+      }
+      if (key == graph_y) {
+        yval = Number(rec[key])
+      }
+      if (key == graph_series) {
+        dataset = rec[key]
+      }
+    }
+    var j = xvals.indexOf(xval)
+    var i = series.indexOf(dataset)
+    values[i].data[j] = yval 
+  }
+
+  return  {
+      type: 'line',
+      data: {
+        labels: xvals,
+        datasets: values,
+      },
+      options: {
+      }
+    }
+
+  
+}
+
+function AliasByName(dict,name) {
+  for (i in dict) {
+    if (dict[i].name == name) { return dict[i].alias }
+  }
+  return name
+}
+
+function AddTraffic(label,b) {
+  var rec
+  for (i in online_traffic) {
+    if (online_traffic[i].label == label) {
+      rec = online_traffic[i]
+      break
+    }
+  }
+  if (!rec) {
+    rec = { label: label, borderColor: AssignColor(label), backgroundColor: AssignColor(label), data: new Array(online_history).fill(0) }
+    online_traffic.push(rec)
+  }
+  rec.data[rec.data.length-1] += b
+}
+
+function ProduceOnline(res) {
+
+  if (!online_traffic) {
+    online_traffic = []
+    config = {
+      type: 'line',
+      data: {
+        labels: new Array(online_history).fill('*'),
+        datasets: online_traffic,
+      },
+      options: {
+        animation: {
+          duration: 0
+        },
+        responsive: true,
+        scales: {
+          xAxes: [{
+            scaleLabel: {
+              display: true,
+            }
+          }],
+          yAxes: [{
+            stacked: true,
+            ticks: {
+                suggestedMin: 0,    // minimum will be 0, unless there is a lower value.
+            },
+            scaleLabel: {
+              display: true,
+            }
+          }]
+        }
+      }
+    }
+    DrawGraph(config) 
+  }
+  
+  for (i = online_traffic.length-1; i>=0; i--) {
+    online_traffic[i].data.shift()
+    online_traffic[i].data.push(0)
+    if (Math.max.apply(null,online_traffic[i].data) == 0) {
+      online_traffic.splice(i,1)
+    }
+  }
+  
+  dictionary = res["dictionary"]
+
+  header_template = res["header"]
+
+  innerHTML = ""
+  data = res["data"]
+  ii=0
+
+  var new_online_connections = []
+
+  dictionary.unshift("useralias")
+  for (i in data) {
+    row_data = data[i]
+    user = row_data["_user"]
+    username = AliasByName(dictionaries['user_id'],user)
+    row_data["useralias"] = username
+    
+    host = row_data["host"]
+    hostname = AliasByName(dictionaries['host_id'],host)
+    row_data["host"] = hostname
+    
+    table_row = Macro("table-row",{DATA:MergeTR(row_data,dictionary)})
+    innerHTML = innerHTML + table_row
+
+    var bytes = Number(row_data['bytes'])
+    
+    var idx = row_data["_ip"]+':'+row_data["_port"]  
+    var last_bytes 
+    
+    if (online_connections) {
+      last_bytes = online_connections[idx]
+      if (!last_bytes) {
+        last_bytes = 0
+      }
+    } else {
+      last_bytes = bytes
+    }
+    
+    new_online_connections[idx] = bytes
+    
+    AddTraffic(username,bytes-last_bytes)
+  }
+  
+  online_connections = new_online_connections
+
+  headerHTML = Macro("header-row",{DATA:MergeTH(dictionary)})
+  
+  reportHTML = Macro("report-table",{HEADER:headerHTML,LINES:innerHTML})
+
+  var body = document.getElementById("report-body") 
+  body.innerHTML = reportHTML;
+
+  timer = setTimeout(Online,refresh)
+ 
+  window.Graph.update()
+
+}
+
+function CancelRefresh() {
+  if (timer) {
+    clearTimeout(timer)
+    timer = null
+  }
+}
+
+function ShowRep(id) {
+  CancelRefresh()
+  current_filter = {}
+  var header = document.getElementById("report-name") 
+  header.innerText = reps[id]["name"] 
+  var body = document.getElementById("report-body") 
+  body.innerText = "Отчет загружается..." 
+  current_rep = id
+  UpdateDates()
+  Rerun()
+}
+
+function ShowFilteredRep(id,filter) {
+  CancelRefresh()
+  for (i in filter) {
+    current_filter[i] = filter[i]
+  }
+  var header = document.getElementById("report-name") 
+  header.innerText = reps[id]["name"] 
+  var body = document.getElementById("report-body") 
+  body.innerText = "Отчет загружается..." 
+  current_rep = id
+  UpdateDates()
+  Rerun()
+}
+
+function FilterSelect(name,dict,value) {
+  var str = '<select onchange="SetFilter(\''+name+'\',this);">'
+  for (key in dict) {
+    if (dict[key].id==value) {
+      str = str + '<option selected value="'+dict[key].id+'">'+dict[key].name+"</option>"
+    } else {
+      str = str + '<option value="'+dict[key].id+'">'+dict[key].name+"</option>"
+    }
+  }
+  str = str + "</select>" + Macro("clear-filter",{NAME:name})
+  return str
+}
+
+function Filter() {
+  var str = ""
+  for (key in current_filter) {
+    var dict = dictionaries[key]
+    if(dict) {
+      str = str + FilterSelect(key,dict,current_filter[key])
+    } else {
+      str = str + Macro("filter-display",{NAME:HeaderMacro(key),VALUE:current_filter[key]}) + Macro("clear-filter",{NAME:key})
+    }
+  }
+  return str
+}
+
+function RefreshFilterPane() {
+  var filter = document.getElementById("report-dates")
+  if (current_rep) {
+    filter.style.display = "block";
+  } else {
+    filter.style.display = "none";
+  } 
+  var filter = document.getElementById("filter") 
+  if (Object.keys(current_filter).length === 0) {
+    filter.style.display = "none";
+    filter.innerHTML = ''
+  } else {
+    filter.style.display = "inline-block";
+    filter.innerHTML = Filter()
+  }
+}
+
+function Rerun() {
+  online_traffic = null
+  var parameters = JSON.parse(JSON.stringify(current_filter))
+  parameters["mnemo"] = current_rep
+  parameters["date_from"] = date_from
+  parameters["date_to"] = date_to
+  GetApi(ProduceRep,"report",parameters);
+  var filter = document.getElementById("filter") 
+  RefreshFilterPane();
+}
+
+function Online() {
+  timer = null
+  RefreshFilterPane()
+  var header = document.getElementById("report-name") 
+  header.innerText = "Активные соединения" 
+  GetApi(ProduceOnline,"online",{});
+  DisplayGraph(true)
+}
+
+function UpdateDates() {
+  var inp = document.getElementById("date-from")
+  date_from = inp.value
+  var inp = document.getElementById("date-to")
+  date_to = inp.value
+
+  SetDates();
+
+}
+
+function SetFilter(name,select) {
+  current_filter[name] = select.value;
+}
+
+function ClearFilter(name) {
+  delete current_filter[name];
+  RefreshFilterPane();
+}
+
+function DisplayGraph(on) {
+  var selector = document.getElementById("page-selector") 
+  if (on) {
+    selector.style.display="block"
+  } else {
+    selector.style.display="none"
+    Display('report-body')
+  }
+}
+
+function Display(id) {
+  var elements = document.getElementsByClassName('report-block');
+  for (i=0; i<elements.length; i++) {
+    if (elements[i].id == id) {
+      elements[i].style.display = "block"
+    } else {
+      elements[i].style.display = "none"
+    }
+  }
+}
\ No newline at end of file
diff --git a/web/squid.png b/web/squid.png
new file mode 100644
index 0000000..f9e4298
Binary files /dev/null and b/web/squid.png differ