1 -- Copyright (C) 1996-2017 The Squid Software Foundation and contributors
3 -- Squid software is distributed under GPLv2+ license and includes
4 -- contributions from numerous individuals and organizations.
5 -- Please see the COPYING and CONTRIBUTORS files for details.
12 -- ip address of hosts accessing the cache
13 CREATE OR REPLACE VIEW cache_clients
AS
14 SELECT DISTINCT ip_client
FROM access_log
ORDER BY 1;
16 -- this index helps view 'cache_clients'
17 CREATE INDEX client_ip_idx
ON access_log(ip_client
);
21 CREATE OR REPLACE VIEW traffic_by_client
AS
24 SUM(http_reply_size
) AS total_bytes
,
25 SUM(http_reply_size
)/1024 AS total_kilobytes
,
26 SUM(http_reply_size
)/1048576 AS total_megabytes
31 -- most active clients
32 -- same as before, but sorted by traffic;
33 -- show only the 10 most active clients
34 CREATE OR REPLACE VIEW most_active_clients
AS
37 SUM(http_reply_size
) AS total_bytes
,
38 SUM(http_reply_size
)/1024 AS total_kilobytes
,
39 SUM(http_reply_size
)/1048576 AS total_megabytes
47 CREATE OR REPLACE VIEW traffic_per_day
AS
50 SUM(http_reply_size
) AS total_bytes
,
51 SUM(http_reply_size
)/1024 AS total_kilobytes
,
52 SUM(http_reply_size
)/1048576 AS total_megabytes
57 -- traffic by client per day
58 CREATE OR REPLACE VIEW traffic_per_day_per_client
AS
62 SUM(http_reply_size
) AS total_bytes
,
63 SUM(http_reply_size
)/1024 AS total_kilobytes
,
64 SUM(http_reply_size
)/1048576 AS total_megabytes
69 CREATE OR REPLACE VIEW traffic_per_month_per_client
AS
71 YEAR(date_day
) AS date_year
,
72 MONTH(date_day
) AS date_month
,
74 SUM(http_reply_size
) AS total_bytes
,
75 SUM(http_reply_size
)/1024 AS total_kilobytes
,
76 SUM(http_reply_size
)/1048576 AS total_megabytes
81 -- list of clients with some stats
82 CREATE OR REPLACE VIEW cache_clients_with_infos
AS
85 COUNT(*) AS total_requests
,
86 (COUNT(*)/(SELECT COUNT(*) FROM access_log
))*100 AS requests_perc
,
87 SUM(a.http_reply_size
) AS total_traffic
,
88 (SUM(a.http_reply_size
)/(SELECT SUM(http_reply_size
) FROM access_log
))*100 AS traffic_perc
,
89 (SELECT COUNT(*) FROM access_log a1
WHERE a1.ip_client
=a.ip_client
AND squid_request_status
LIKE '%HIT%')
91 (SELECT COUNT(*) FROM access_log
)
93 (SELECT COUNT(*) FROM access_log a1
WHERE a1.ip_client
=a.ip_client
AND squid_request_status
LIKE '%MISS%')
95 (SELECT COUNT(*) FROM access_log
)
97 MIN(date_day
) AS first_access_date
,
98 MIN(date_time
) AS first_access_time
,
99 MAX(date_day
) AS last_access_date
,
100 MAX(date_time
) AS last_access_time
105 -- this index helps view 'cache_clients_with_infos'
106 CREATE INDEX client_req_status_idx
ON access_log(ip_client
, squid_request_status
);
109 -- number of requests per day
110 CREATE OR REPLACE VIEW requests_per_day
AS
112 DATE(FROM_UNIXTIME(time_since_epoch
)) AS date_day
,
113 COUNT(*) AS num_of_requests
118 -- number of requests per minute
119 CREATE OR REPLACE VIEW requests_per_minute
AS
121 DATE(FROM_UNIXTIME(time_since_epoch
)) AS date_day
,
122 HOUR(FROM_UNIXTIME(time_since_epoch
)) AS date_hour
,
123 MINUTE(FROM_UNIXTIME(time_since_epoch
)) AS date_minute
,
124 COUNT(*) AS num_of_requests
129 -- number of requests per day of each cache client
130 CREATE OR REPLACE VIEW requests_per_day_per_client
AS
132 DATE(FROM_UNIXTIME(time_since_epoch
)) AS date_day
,
134 COUNT(*) AS num_of_requests
139 -- percentage of each request status
140 CREATE OR REPLACE VIEW requests_status_perc
AS
142 squid_request_status
,
143 (COUNT(*)/(SELECT COUNT(*) FROM access_log
)*100) AS percentage
145 GROUP BY squid_request_status
148 -- this index helps view 'requests_status_perc'
149 CREATE INDEX req_status_idx
ON access_log(squid_request_status
);
151 -- request hits and misses, in percentage
152 CREATE OR REPLACE VIEW hits_misses_perc
AS
155 (SELECT COUNT(*) FROM access_log
WHERE squid_request_status
LIKE '%HIT%')
157 (SELECT COUNT(*) FROM access_log
)*100
162 (SELECT COUNT(*) FROM access_log
WHERE squid_request_status
LIKE '%MISS%')
164 (SELECT COUNT(*) FROM access_log
)*100
168 CREATE OR REPLACE VIEW time_response_ranges
AS
171 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
173 WHERE time_response
>= 0 AND time_response
< 500
177 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
179 WHERE time_response
>= 500 AND time_response
< 1000
183 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
185 WHERE time_response
>= 1000 AND time_response
< 2000
189 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
191 WHERE time_response
>= 2000;
193 -- this index helps view 'time_response_ranges'
194 CREATE INDEX time_response_idx
ON access_log(time_response
);
196 -- response time graph
197 CREATE OR REPLACE VIEW time_response_graph
AS
205 -- traffic by mime type
206 CREATE OR REPLACE VIEW traffic_by_http_mime_type
AS
209 SUM(http_reply_size
) as total_bytes
211 GROUP BY http_mime_type
215 CREATE OR REPLACE VIEW last_10_queries
AS
219 id > (SELECT MAX(id) FROM access_log
) - 10
222 -- id of the last query of each client
223 -- this view is required by the "last n queries by ip" view
224 CREATE OR REPLACE VIEW last_query_by_client
AS
227 MAX(id) AS last_query_id
232 -- last 10 queries of each client
233 -- NOTE: this query is conceptually wrong because it assumes that no holes exist
234 -- in the values of column 'id'.
235 -- This can be false if e.g. some access_log entries get deleted...
236 CREATE OR REPLACE VIEW last_10_queries_by_client
AS
241 SELECT l.last_query_id
242 FROM last_query_by_client l
243 WHERE l.ip_client
= a.ip_client
245 ORDER BY a.ip_client
, a.
id DESC;
247 -- this index helps the "last_10_queries_by_client" view
248 CREATE INDEX client_ip_record_id_idx
ON access_log(ip_client
, id);
251 -- number of HIT requests per day
252 CREATE OR REPLACE VIEW hits_per_day
AS
257 WHERE squid_request_status
LIKE '%HIT%'
260 -- HIT requests per day, percent (100% = total number of requests that day)
261 CREATE OR REPLACE VIEW hits_per_day_perc
AS
264 h.num_hits
/r.num_of_requests
*100 AS hits_per_day_perc
265 FROM requests_per_day r
268 ON r.date_day
= h.date_day
;
271 -- request methods (count)
272 CREATE OR REPLACE VIEW http_methods
AS
280 -- request methods by percent
281 CREATE OR REPLACE VIEW http_methods_perc
AS
284 COUNT(*) / (SELECT COUNT(*) FROM access_log
) * 100 AS perc
291 CREATE OR REPLACE VIEW slowest_requests
AS
294 ORDER BY time_response
DESC
298 CREATE OR REPLACE VIEW slowest_request_by_method
AS
302 ORDER BY http_method
, time_response
DESC;
305 -- requests with the biggest reply size
306 CREATE OR REPLACE VIEW biggest_requests
AS
309 ORDER BY http_reply_size
DESC
314 -- list each day which has at least one request, with some statistics
315 CREATE OR REPLACE VIEW days_with_infos
AS
318 MIN(date_time
) AS first_req_time
,
319 MAX(date_time
) AS last_req_time
,
320 COUNT(*) AS number_of_requests
,
321 SUM(http_reply_size
) AS total_traffic_bytes
,
322 SUM(http_reply_size
) / 1048576 AS total_traffic_megabytes
,
323 COUNT(DISTINCT ip_client
) AS number_of_clients
,
324 AVG(time_response
) AS avg_time_response
,
325 MAX(time_response
) AS max_time_response
,
327 -- client that has made the highest number of requests that day
330 FROM requests_per_day_per_client r
331 WHERE r.date_day
= a.date_day
332 ORDER BY r.num_of_requests
DESC LIMIT 1
333 ) AS most_active_client_r
,
335 -- the number of requests that client actually made
337 SELECT r.num_of_requests
338 FROM requests_per_day_per_client r
339 WHERE r.date_day
= a.date_day
340 ORDER BY r.num_of_requests
DESC LIMIT 1
341 ) AS most_active_client_r_nr
,
343 -- same info but as percentage on the total number of requests that day
344 -- we have to repeat the whole query because we cannot reference aliases
345 -- defined in previous columns
346 -- a date_day column with an index on it would help here; a view would probably help too...
349 SELECT r.num_of_requests
350 FROM requests_per_day_per_client r
351 WHERE r.date_day
= a.date_day
352 ORDER BY 1 DESC LIMIT 1
356 WHERE a.date_day
= a1.date_day
358 ) AS most_active_client_r_pc
,
360 -- client that has generated the highest traffic that day
363 FROM traffic_per_day_per_client t
364 WHERE t.date_day
= a.date_day
365 ORDER BY t.total_bytes
DESC LIMIT 1
366 ) AS most_active_client_t
,
368 -- the actual traffic generated by that client
371 FROM traffic_per_day_per_client t
372 WHERE t.date_day
= a.date_day
373 ORDER BY t.total_bytes
DESC LIMIT 1
374 ) AS most_active_client_t_b
,
376 -- same info expressed in megabytes
379 FROM traffic_per_day_per_client t
380 WHERE t.date_day
= a.date_day
381 ORDER BY t.total_bytes
DESC LIMIT 1
382 ) / 1048576 AS most_active_client_t_mb
,
384 -- same info in percentage on the total traffic that day
385 -- see previous comments
389 FROM traffic_per_day_per_client t
390 WHERE t.date_day
= a.date_day
391 ORDER BY t.total_bytes
DESC LIMIT 1
393 SELECT SUM(http_reply_size
)
395 WHERE a.date_day
= a1.date_day
397 ) AS most_active_client_t_pc
403 -- this index helps the "days_with_info" view
404 CREATE INDEX date_day_idx
ON access_log(date_day
);
407 CREATE OR REPLACE VIEW requests_in_last_minute
AS
408 select * from access_log
where time_since_epoch
>= ( (select max(time_since_epoch
) from access_log
) - 60);
411 CREATE OR REPLACE VIEW avg_req_per_minute
AS
412 SELECT COUNT(*) FROM requests_in_last_minute
;