1 -- MySQL dump 10.13 Distrib 5.7.28, for Linux (x86_64)
3 -- Host: localhost Database: squid
4 -- ------------------------------------------------------
5 -- Server version 5.7.28-0ubuntu0.18.04.4
7 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 /*!40101 SET NAMES utf8 */;
11 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12 /*!40103 SET TIME_ZONE='+00:00' */;
13 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19 -- Table structure for table `access_log`
22 DROP TABLE IF EXISTS `access_log`;
23 /*!40101 SET @saved_cs_client = @@character_set_client */;
24 /*!40101 SET character_set_client = utf8 */;
25 CREATE TABLE `access_log` (
26 `id` int(11) NOT NULL AUTO_INCREMENT,
27 `access_date` datetime NOT NULL,
28 `process_time` int(11) DEFAULT NULL,
29 `host_id` int(11) NOT NULL,
30 `code_id` int(11) NOT NULL,
31 `bytes` int(11) DEFAULT NULL,
32 `method_id` int(11) NOT NULL,
33 `site` varchar(1024) DEFAULT NULL,
34 `user_id` int(11) NOT NULL,
35 `mime_id` int(11) NOT NULL,
37 KEY `KEY` (`access_date`,`host_id`,`bytes`,`user_id`)
38 ) ENGINE=InnoDB AUTO_INCREMENT=112422 DEFAULT CHARSET=utf8;
39 /*!40101 SET character_set_client = @saved_cs_client */;
42 -- Table structure for table `column_names`
45 DROP TABLE IF EXISTS `column_names`;
46 /*!40101 SET @saved_cs_client = @@character_set_client */;
47 /*!40101 SET character_set_client = utf8 */;
48 CREATE TABLE `column_names` (
49 `id` int(11) NOT NULL AUTO_INCREMENT,
50 `name` varchar(45) NOT NULL,
51 `alias` varchar(45) NOT NULL,
52 `template` varchar(1024) DEFAULT NULL,
53 `template_name` varchar(45) DEFAULT NULL,
55 ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
56 /*!40101 SET character_set_client = @saved_cs_client */;
59 -- Dumping data for table `column_names`
62 LOCK TABLES `column_names` WRITE;
63 /*!40000 ALTER TABLE `column_names` DISABLE KEYS */;
64 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>','');
65 /*!40000 ALTER TABLE `column_names` ENABLE KEYS */;
69 -- Table structure for table `hosts`
72 DROP TABLE IF EXISTS `hosts`;
73 /*!40101 SET @saved_cs_client = @@character_set_client */;
74 /*!40101 SET character_set_client = utf8 */;
75 CREATE TABLE `hosts` (
76 `id` int(11) NOT NULL AUTO_INCREMENT,
77 `hostname` varchar(45) NOT NULL,
78 `alias` varchar(45) DEFAULT NULL,
80 UNIQUE KEY `hostname_UNIQUE` (`hostname`),
81 UNIQUE KEY `alias_UNIQUE` (`alias`)
82 ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
83 /*!40101 SET character_set_client = @saved_cs_client */;
86 -- Table structure for table `http_codes`
89 DROP TABLE IF EXISTS `http_codes`;
90 /*!40101 SET @saved_cs_client = @@character_set_client */;
91 /*!40101 SET character_set_client = utf8 */;
92 CREATE TABLE `http_codes` (
93 `id` int(11) NOT NULL AUTO_INCREMENT,
94 `http_code` varchar(45) DEFAULT NULL,
96 UNIQUE KEY `http_code_UNIQUE` (`http_code`)
97 ) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8;
98 /*!40101 SET character_set_client = @saved_cs_client */;
101 -- Table structure for table `http_methods`
104 DROP TABLE IF EXISTS `http_methods`;
105 /*!40101 SET @saved_cs_client = @@character_set_client */;
106 /*!40101 SET character_set_client = utf8 */;
107 CREATE TABLE `http_methods` (
108 `id` int(11) NOT NULL AUTO_INCREMENT,
109 `method` varchar(45) DEFAULT NULL,
111 UNIQUE KEY `method_UNIQUE` (`method`)
112 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
113 /*!40101 SET character_set_client = @saved_cs_client */;
116 -- Table structure for table `mime_types`
119 DROP TABLE IF EXISTS `mime_types`;
120 /*!40101 SET @saved_cs_client = @@character_set_client */;
121 /*!40101 SET character_set_client = utf8 */;
122 CREATE TABLE `mime_types` (
123 `id` int(11) NOT NULL AUTO_INCREMENT,
124 `mime_type` varchar(45) NOT NULL,
125 `alias` varchar(45) DEFAULT NULL,
127 UNIQUE KEY `mime_type_UNIQUE` (`mime_type`),
128 UNIQUE KEY `alias_UNIQUE` (`alias`)
129 ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;
130 /*!40101 SET character_set_client = @saved_cs_client */;
133 -- Table structure for table `rep`
136 DROP TABLE IF EXISTS `rep`;
137 /*!40101 SET @saved_cs_client = @@character_set_client */;
138 /*!40101 SET character_set_client = utf8 */;
140 `id` int(11) NOT NULL AUTO_INCREMENT,
141 `cat_mnemo` varchar(45) NOT NULL,
142 `mnemo` varchar(16) DEFAULT NULL,
143 `name` varchar(45) DEFAULT NULL,
144 `description` varchar(1024) DEFAULT NULL,
145 `query` varchar(1024) DEFAULT NULL,
146 `has_total` varchar(45) DEFAULT NULL,
147 `graph_x` varchar(45) DEFAULT NULL,
148 `graph_series` varchar(45) DEFAULT NULL,
149 `graph_y` varchar(45) DEFAULT NULL,
151 UNIQUE KEY `name_UNIQUE` (`name`)
152 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
153 /*!40101 SET character_set_client = @saved_cs_client */;
156 -- Dumping data for table `rep`
159 LOCK TABLES `rep` WRITE;
160 /*!40000 ALTER TABLE `rep` DISABLE KEYS */;
161 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');
162 /*!40000 ALTER TABLE `rep` ENABLE KEYS */;
166 -- Table structure for table `rep_cat`
169 DROP TABLE IF EXISTS `rep_cat`;
170 /*!40101 SET @saved_cs_client = @@character_set_client */;
171 /*!40101 SET character_set_client = utf8 */;
172 CREATE TABLE `rep_cat` (
173 `id` int(11) NOT NULL AUTO_INCREMENT,
174 `mnemo` varchar(45) DEFAULT NULL,
175 `name` varchar(45) DEFAULT NULL,
176 `description` varchar(128) DEFAULT NULL,
178 UNIQUE KEY `name_UNIQUE` (`name`)
179 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
180 /*!40101 SET character_set_client = @saved_cs_client */;
183 -- Dumping data for table `rep_cat`
186 LOCK TABLES `rep_cat` WRITE;
187 /*!40000 ALTER TABLE `rep_cat` DISABLE KEYS */;
188 INSERT INTO `rep_cat` VALUES (1,'user','Пользователи','Отчеты по авторизованным пользователям'),(2,'host','Хосты','Отчеты по клиентским подключениям'),(3,'site','Сайты','Отчеты по посещаемым сайтам');
189 /*!40000 ALTER TABLE `rep_cat` ENABLE KEYS */;
193 -- Table structure for table `tmp_traffic`
196 DROP TABLE IF EXISTS `tmp_traffic`;
197 /*!40101 SET @saved_cs_client = @@character_set_client */;
198 /*!40101 SET character_set_client = utf8 */;
199 CREATE TABLE `tmp_traffic` (
200 `timestamp` decimal(38,3) DEFAULT NULL,
201 `process_time` int(11) DEFAULT NULL,
202 `client_host` varchar(128) DEFAULT NULL,
203 `http_code` varchar(45) DEFAULT NULL,
204 `bytes` int(11) DEFAULT NULL,
205 `http_method` varchar(45) DEFAULT NULL,
206 `server_host` varchar(128) DEFAULT NULL,
207 `username` varchar(45) DEFAULT NULL,
208 `mime_type` varchar(45) DEFAULT NULL
209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
210 /*!40101 SET character_set_client = @saved_cs_client */;
213 -- Table structure for table `users`
216 DROP TABLE IF EXISTS `users`;
217 /*!40101 SET @saved_cs_client = @@character_set_client */;
218 /*!40101 SET character_set_client = utf8 */;
219 CREATE TABLE `users` (
220 `id` int(11) NOT NULL AUTO_INCREMENT,
221 `username` varchar(45) NOT NULL,
222 `alias` varchar(45) DEFAULT NULL,
224 UNIQUE KEY `username_UNIQUE` (`username`),
225 UNIQUE KEY `alias_UNIQUE` (`alias`)
226 ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
227 /*!40101 SET character_set_client = @saved_cs_client */;
230 -- Table structure for table `web_templates`
233 DROP TABLE IF EXISTS `web_templates`;
234 /*!40101 SET @saved_cs_client = @@character_set_client */;
235 /*!40101 SET character_set_client = utf8 */;
236 CREATE TABLE `web_templates` (
237 `id` int(11) NOT NULL AUTO_INCREMENT,
238 `mnemo` varchar(32) DEFAULT NULL,
239 `description` varchar(128) DEFAULT NULL,
240 `body` varchar(8192) DEFAULT NULL,
242 UNIQUE KEY `mnemo_UNIQUE` (`mnemo`)
243 ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
244 /*!40101 SET character_set_client = @saved_cs_client */;
247 -- Dumping data for table `web_templates`
250 LOCK TABLES `web_templates` WRITE;
251 /*!40000 ALTER TABLE `web_templates` DISABLE KEYS */;
252 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>');
253 /*!40000 ALTER TABLE `web_templates` ENABLE KEYS */;
255 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
257 CREATE DEFINER=`admin`@`%` PROCEDURE `Process_Traffic`()
260 insert into users(username,alias)
261 select username,username from (
262 select distinct username from tmp_traffic
263 left join users using(username)
264 where users.username is null) t;
266 insert into hosts(hostname,alias)
267 select client_host,client_host from (
268 select distinct client_host from tmp_traffic
269 left join hosts on hosts.hostname=tmp_traffic.client_host
270 where hosts.hostname is null) t;
272 insert into http_methods(method)
273 select distinct http_method from tmp_traffic
274 left join http_methods on http_methods.method=tmp_traffic.http_method
275 where http_methods.method is null;
277 insert into http_codes(http_code)
278 select distinct http_code from tmp_traffic
279 left join http_codes using(http_code)
280 where http_codes.http_code is null;
282 insert into mime_types(mime_type,alias)
283 select mime_type,mime_type from (
284 select distinct mime_type from tmp_traffic
285 left join mime_types using(mime_type)
286 where mime_types.mime_type is null) t;
288 insert into access_log(access_date,process_time,host_id,code_id,bytes,method_id,site,user_id,mime_id)
289 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
292 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
293 from tmp_traffic t, users u,hosts h,http_methods m,http_codes c,mime_types mt
295 t.username=u.username and
296 t.client_host=h.hostname and
297 t.http_method=m.method and
298 t.http_code=c.http_code and
299 t.mime_type=mt.mime_type
301 left join access_log a
302 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
305 delete from tmp_traffic;
309 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
310 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
311 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
312 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
313 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
314 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
315 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
317 -- Dump completed on 2019-12-13 15:39:51