5 -- ip address of hosts accessing the cache
6 CREATE OR REPLACE VIEW cache_clients
AS
7 SELECT DISTINCT ip_client
FROM access_log
ORDER BY 1;
9 -- this index helps view 'cache_clients'
10 CREATE INDEX client_ip_idx
ON access_log(ip_client
);
14 CREATE OR REPLACE VIEW traffic_by_client
AS
17 SUM(http_reply_size
) AS total_bytes
,
18 SUM(http_reply_size
)/1024 AS total_kilobytes
,
19 SUM(http_reply_size
)/1048576 AS total_megabytes
24 -- most active clients
25 -- same as before, but sorted by traffic;
26 -- show only the 10 most active clients
27 CREATE OR REPLACE VIEW most_active_clients
AS
30 SUM(http_reply_size
) AS total_bytes
,
31 SUM(http_reply_size
)/1024 AS total_kilobytes
,
32 SUM(http_reply_size
)/1048576 AS total_megabytes
40 CREATE OR REPLACE VIEW traffic_per_day
AS
43 SUM(http_reply_size
) AS total_bytes
,
44 SUM(http_reply_size
)/1024 AS total_kilobytes
,
45 SUM(http_reply_size
)/1048576 AS total_megabytes
50 -- traffic by client per day
51 CREATE OR REPLACE VIEW traffic_per_day_per_client
AS
55 SUM(http_reply_size
) AS total_bytes
,
56 SUM(http_reply_size
)/1024 AS total_kilobytes
,
57 SUM(http_reply_size
)/1048576 AS total_megabytes
62 CREATE OR REPLACE VIEW traffic_per_month_per_client
AS
64 YEAR(date_day
) AS date_year
,
65 MONTH(date_day
) AS date_month
,
67 SUM(http_reply_size
) AS total_bytes
,
68 SUM(http_reply_size
)/1024 AS total_kilobytes
,
69 SUM(http_reply_size
)/1048576 AS total_megabytes
74 -- list of clients with some stats
75 CREATE OR REPLACE VIEW cache_clients_with_infos
AS
78 COUNT(*) AS total_requests
,
79 (COUNT(*)/(SELECT COUNT(*) FROM access_log
))*100 AS requests_perc
,
80 SUM(a.http_reply_size
) AS total_traffic
,
81 (SUM(a.http_reply_size
)/(SELECT SUM(http_reply_size
) FROM access_log
))*100 AS traffic_perc
,
82 (SELECT COUNT(*) FROM access_log a1
WHERE a1.ip_client
=a.ip_client
AND squid_request_status
LIKE '%HIT%')
84 (SELECT COUNT(*) FROM access_log
)
86 (SELECT COUNT(*) FROM access_log a1
WHERE a1.ip_client
=a.ip_client
AND squid_request_status
LIKE '%MISS%')
88 (SELECT COUNT(*) FROM access_log
)
90 MIN(date_day
) AS first_access_date
,
91 MIN(date_time
) AS first_access_time
,
92 MAX(date_day
) AS last_access_date
,
93 MAX(date_time
) AS last_access_time
98 -- this index helps view 'cache_clients_with_infos'
99 CREATE INDEX client_req_status_idx
ON access_log(ip_client
, squid_request_status
);
102 -- number of requests per day
103 CREATE OR REPLACE VIEW requests_per_day
AS
105 DATE(FROM_UNIXTIME(time_since_epoch
)) AS date_day
,
106 COUNT(*) AS num_of_requests
111 -- number of requests per minute
112 CREATE OR REPLACE VIEW requests_per_minute
AS
114 DATE(FROM_UNIXTIME(time_since_epoch
)) AS date_day
,
115 HOUR(FROM_UNIXTIME(time_since_epoch
)) AS date_hour
,
116 MINUTE(FROM_UNIXTIME(time_since_epoch
)) AS date_minute
,
117 COUNT(*) AS num_of_requests
122 -- number of requests per day of each cache client
123 CREATE OR REPLACE VIEW requests_per_day_per_client
AS
125 DATE(FROM_UNIXTIME(time_since_epoch
)) AS date_day
,
127 COUNT(*) AS num_of_requests
132 -- percentage of each request status
133 CREATE OR REPLACE VIEW requests_status_perc
AS
135 squid_request_status
,
136 (COUNT(*)/(SELECT COUNT(*) FROM access_log
)*100) AS percentage
138 GROUP BY squid_request_status
141 -- this index helps view 'requests_status_perc'
142 CREATE INDEX req_status_idx
ON access_log(squid_request_status
);
144 -- request hits and misses, in percentage
145 CREATE OR REPLACE VIEW hits_misses_perc
AS
148 (SELECT COUNT(*) FROM access_log
WHERE squid_request_status
LIKE '%HIT%')
150 (SELECT COUNT(*) FROM access_log
)*100
155 (SELECT COUNT(*) FROM access_log
WHERE squid_request_status
LIKE '%MISS%')
157 (SELECT COUNT(*) FROM access_log
)*100
161 CREATE OR REPLACE VIEW time_response_ranges
AS
164 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
166 WHERE time_response
>= 0 AND time_response
< 500
170 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
172 WHERE time_response
>= 500 AND time_response
< 1000
176 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
178 WHERE time_response
>= 1000 AND time_response
< 2000
182 COUNT(*) / (SELECT COUNT(*) FROM access_log
)*100 AS percentage
184 WHERE time_response
>= 2000;
186 -- this index helps view 'time_response_ranges'
187 CREATE INDEX time_response_idx
ON access_log(time_response
);
189 -- response time graph
190 CREATE OR REPLACE VIEW time_response_graph
AS
198 -- traffic by mime type
199 CREATE OR REPLACE VIEW traffic_by_http_mime_type
AS
202 SUM(http_reply_size
) as total_bytes
204 GROUP BY http_mime_type
208 CREATE OR REPLACE VIEW last_10_queries
AS
212 id > (SELECT MAX(id) FROM access_log
) - 10
215 -- id of the last query of each client
216 -- this view is required by the "last n queries by ip" view
217 CREATE OR REPLACE VIEW last_query_by_client
AS
220 MAX(id) AS last_query_id
225 -- last 10 queries of each client
226 -- NOTE: this query is conceptually wrong because it assumes that no holes exist
227 -- in the values of column 'id'.
228 -- This can be false if e.g. some access_log entries get deleted...
229 CREATE OR REPLACE VIEW last_10_queries_by_client
AS
234 SELECT l.last_query_id
235 FROM last_query_by_client l
236 WHERE l.ip_client
= a.ip_client
238 ORDER BY a.ip_client
, a.
id DESC;
240 -- this index helps the "last_10_queries_by_client" view
241 CREATE INDEX client_ip_record_id_idx
ON access_log(ip_client
, id);
244 -- number of HIT requests per day
245 CREATE OR REPLACE VIEW hits_per_day
AS
250 WHERE squid_request_status
LIKE '%HIT%'
253 -- HIT requests per day, percent (100% = total number of requests that day)
254 CREATE OR REPLACE VIEW hits_per_day_perc
AS
257 h.num_hits
/r.num_of_requests
*100 AS hits_per_day_perc
258 FROM requests_per_day r
261 ON r.date_day
= h.date_day
;
264 -- request methods (count)
265 CREATE OR REPLACE VIEW http_methods
AS
273 -- request methods by percent
274 CREATE OR REPLACE VIEW http_methods_perc
AS
277 COUNT(*) / (SELECT COUNT(*) FROM access_log
) * 100 AS perc
284 CREATE OR REPLACE VIEW slowest_requests
AS
287 ORDER BY time_response
DESC
291 CREATE OR REPLACE VIEW slowest_request_by_method
AS
295 ORDER BY http_method
, time_response
DESC;
298 -- requests with the biggest reply size
299 CREATE OR REPLACE VIEW biggest_requests
AS
302 ORDER BY http_reply_size
DESC
307 -- list each day which has at least one request, with some statistics
308 CREATE OR REPLACE VIEW days_with_infos
AS
311 MIN(date_time
) AS first_req_time
,
312 MAX(date_time
) AS last_req_time
,
313 COUNT(*) AS number_of_requests
,
314 SUM(http_reply_size
) AS total_traffic_bytes
,
315 SUM(http_reply_size
) / 1048576 AS total_traffic_megabytes
,
316 COUNT(DISTINCT ip_client
) AS number_of_clients
,
317 AVG(time_response
) AS avg_time_response
,
318 MAX(time_response
) AS max_time_response
,
320 -- client that has made the highest number of requests that day
323 FROM requests_per_day_per_client r
324 WHERE r.date_day
= a.date_day
325 ORDER BY r.num_of_requests
DESC LIMIT 1
326 ) AS most_active_client_r
,
328 -- the number of requests that client actually made
330 SELECT r.num_of_requests
331 FROM requests_per_day_per_client r
332 WHERE r.date_day
= a.date_day
333 ORDER BY r.num_of_requests
DESC LIMIT 1
334 ) AS most_active_client_r_nr
,
336 -- same info but as percentage on the total number of requests that day
337 -- we have to repeat the whole query because we cannot reference aliases
338 -- defined in previous columns
339 -- a date_day column with an index on it would help here; a view would probably help too...
342 SELECT r.num_of_requests
343 FROM requests_per_day_per_client r
344 WHERE r.date_day
= a.date_day
345 ORDER BY 1 DESC LIMIT 1
349 WHERE a.date_day
= a1.date_day
351 ) AS most_active_client_r_pc
,
353 -- client that has generated the highest traffic that day
356 FROM traffic_per_day_per_client t
357 WHERE t.date_day
= a.date_day
358 ORDER BY t.total_bytes
DESC LIMIT 1
359 ) AS most_active_client_t
,
361 -- the actual traffic generated by that client
364 FROM traffic_per_day_per_client t
365 WHERE t.date_day
= a.date_day
366 ORDER BY t.total_bytes
DESC LIMIT 1
367 ) AS most_active_client_t_b
,
369 -- same info expressed in megabytes
372 FROM traffic_per_day_per_client t
373 WHERE t.date_day
= a.date_day
374 ORDER BY t.total_bytes
DESC LIMIT 1
375 ) / 1048576 AS most_active_client_t_mb
,
377 -- same info in percentage on the total traffic that day
378 -- see previous comments
382 FROM traffic_per_day_per_client t
383 WHERE t.date_day
= a.date_day
384 ORDER BY t.total_bytes
DESC LIMIT 1
386 SELECT SUM(http_reply_size
)
388 WHERE a.date_day
= a1.date_day
390 ) AS most_active_client_t_pc
396 -- this index helps the "days_with_info" view
397 CREATE INDEX date_day_idx
ON access_log(date_day
);
400 CREATE OR REPLACE VIEW requests_in_last_minute
AS
401 select * from access_log
where time_since_epoch
>= ( (select max(time_since_epoch
) from access_log
) - 60);
404 CREATE OR REPLACE VIEW avg_req_per_minute
AS
405 SELECT COUNT(*) FROM requests_in_last_minute
;