]>
Commit | Line | Data |
---|---|---|
5b74111a | 1 | -- Copyright (C) 1996-2018 The Squid Software Foundation and contributors |
9a1b46cc AJ |
2 | -- |
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. | |
6 | -- | |
7 | ||
1c363fe7 AJ |
8 | -- |
9 | -- Sample views | |
10 | -- | |
11 | ||
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; | |
15 | ||
16 | -- this index helps view 'cache_clients' | |
17 | CREATE INDEX client_ip_idx ON access_log(ip_client); | |
18 | ||
19 | ||
20 | -- traffic by client | |
21 | CREATE OR REPLACE VIEW traffic_by_client AS | |
22 | SELECT | |
23 | ip_client, | |
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 | |
27 | FROM access_log | |
28 | GROUP BY 1 | |
29 | ORDER BY 1; | |
30 | ||
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 | |
35 | SELECT | |
36 | ip_client, | |
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 | |
40 | FROM access_log | |
41 | GROUP BY 1 | |
42 | ORDER BY 2 DESC | |
43 | LIMIT 10; | |
44 | ||
45 | ||
46 | -- traffic per day | |
47 | CREATE OR REPLACE VIEW traffic_per_day AS | |
48 | SELECT | |
49 | date_day, | |
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 | |
53 | FROM access_log | |
54 | GROUP BY 1 | |
55 | ORDER BY 1; | |
56 | ||
57 | -- traffic by client per day | |
58 | CREATE OR REPLACE VIEW traffic_per_day_per_client AS | |
59 | SELECT | |
60 | date_day, | |
61 | ip_client, | |
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 | |
65 | FROM access_log | |
66 | GROUP BY 1,2 | |
67 | ORDER BY 1,2 DESC; | |
68 | ||
69 | CREATE OR REPLACE VIEW traffic_per_month_per_client AS | |
70 | SELECT | |
71 | YEAR(date_day) AS date_year, | |
72 | MONTH(date_day) AS date_month, | |
73 | ip_client, | |
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 | |
77 | FROM access_log | |
78 | GROUP BY 2,3 | |
79 | ORDER BY 1,2,3; | |
80 | ||
81 | -- list of clients with some stats | |
82 | CREATE OR REPLACE VIEW cache_clients_with_infos AS | |
83 | SELECT | |
84 | a.ip_client, | |
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%') | |
90 | / | |
91 | (SELECT COUNT(*) FROM access_log) | |
92 | * 100 AS hit_perc, | |
93 | (SELECT COUNT(*) FROM access_log a1 WHERE a1.ip_client=a.ip_client AND squid_request_status LIKE '%MISS%') | |
94 | / | |
95 | (SELECT COUNT(*) FROM access_log) | |
96 | * 100 AS miss_perc, | |
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 | |
101 | FROM access_log a | |
102 | GROUP BY 1 | |
103 | ORDER BY 1; | |
104 | ||
105 | -- this index helps view 'cache_clients_with_infos' | |
106 | CREATE INDEX client_req_status_idx ON access_log(ip_client, squid_request_status); | |
107 | ||
108 | ||
109 | -- number of requests per day | |
110 | CREATE OR REPLACE VIEW requests_per_day AS | |
111 | SELECT | |
112 | DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day, | |
113 | COUNT(*) AS num_of_requests | |
114 | FROM access_log | |
115 | GROUP BY 1 | |
116 | ORDER BY 1; | |
117 | ||
118 | -- number of requests per minute | |
119 | CREATE OR REPLACE VIEW requests_per_minute AS | |
120 | SELECT | |
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 | |
125 | FROM access_log | |
126 | GROUP BY 1,2,3 | |
127 | ORDER BY 1,2,3; | |
128 | ||
129 | -- number of requests per day of each cache client | |
130 | CREATE OR REPLACE VIEW requests_per_day_per_client AS | |
131 | SELECT | |
132 | DATE(FROM_UNIXTIME(time_since_epoch)) AS date_day, | |
133 | ip_client, | |
134 | COUNT(*) AS num_of_requests | |
135 | FROM access_log | |
136 | GROUP BY 1,2 | |
137 | ORDER BY 1,2; | |
138 | ||
139 | -- percentage of each request status | |
140 | CREATE OR REPLACE VIEW requests_status_perc AS | |
141 | SELECT | |
142 | squid_request_status, | |
143 | (COUNT(*)/(SELECT COUNT(*) FROM access_log)*100) AS percentage | |
144 | FROM access_log | |
145 | GROUP BY squid_request_status | |
146 | ORDER BY 2 DESC; | |
147 | ||
148 | -- this index helps view 'requests_status_perc' | |
149 | CREATE INDEX req_status_idx ON access_log(squid_request_status); | |
150 | ||
151 | -- request hits and misses, in percentage | |
152 | CREATE OR REPLACE VIEW hits_misses_perc AS | |
153 | SELECT | |
154 | 'hits', | |
155 | (SELECT COUNT(*) FROM access_log WHERE squid_request_status LIKE '%HIT%') | |
156 | / | |
157 | (SELECT COUNT(*) FROM access_log)*100 | |
158 | AS percentage | |
159 | UNION | |
160 | SELECT | |
161 | 'misses', | |
162 | (SELECT COUNT(*) FROM access_log WHERE squid_request_status LIKE '%MISS%') | |
163 | / | |
164 | (SELECT COUNT(*) FROM access_log)*100 | |
165 | AS pecentage; | |
166 | ||
167 | -- response times | |
168 | CREATE OR REPLACE VIEW time_response_ranges AS | |
169 | SELECT | |
170 | '0..500', | |
171 | COUNT(*) / (SELECT COUNT(*) FROM access_log)*100 AS percentage | |
172 | FROM access_log | |
173 | WHERE time_response >= 0 AND time_response < 500 | |
174 | UNION | |
175 | SELECT | |
176 | '500..1000', | |
177 | COUNT(*) / (SELECT COUNT(*) FROM access_log)*100 AS percentage | |
178 | FROM access_log | |
179 | WHERE time_response >= 500 AND time_response < 1000 | |
180 | UNION | |
181 | SELECT | |
182 | '1000..2000', | |
183 | COUNT(*) / (SELECT COUNT(*) FROM access_log)*100 AS percentage | |
184 | FROM access_log | |
185 | WHERE time_response >= 1000 AND time_response < 2000 | |
186 | UNION | |
187 | SELECT | |
188 | '>= 2000', | |
189 | COUNT(*) / (SELECT COUNT(*) FROM access_log)*100 AS percentage | |
190 | FROM access_log | |
191 | WHERE time_response >= 2000; | |
192 | ||
193 | -- this index helps view 'time_response_ranges' | |
194 | CREATE INDEX time_response_idx ON access_log(time_response); | |
195 | ||
196 | -- response time graph | |
197 | CREATE OR REPLACE VIEW time_response_graph AS | |
198 | SELECT | |
199 | time_response, | |
200 | COUNT(*) AS num_req | |
201 | FROM access_log | |
202 | GROUP BY 1 | |
203 | ORDER BY 1; | |
204 | ||
205 | -- traffic by mime type | |
206 | CREATE OR REPLACE VIEW traffic_by_http_mime_type AS | |
207 | SELECT | |
208 | http_mime_type, | |
209 | SUM(http_reply_size) as total_bytes | |
210 | FROM access_log | |
211 | GROUP BY http_mime_type | |
212 | ORDER BY 2 DESC; | |
213 | ||
214 | -- last 10 queries | |
215 | CREATE OR REPLACE VIEW last_10_queries AS | |
216 | SELECT * | |
217 | FROM access_log | |
218 | WHERE | |
219 | id > (SELECT MAX(id) FROM access_log) - 10 | |
220 | ORDER BY id DESC; | |
221 | ||
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 | |
225 | SELECT | |
226 | ip_client, | |
227 | MAX(id) AS last_query_id | |
228 | FROM access_log | |
229 | GROUP BY ip_client; | |
230 | ||
231 | ||
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 | |
237 | SELECT * | |
238 | FROM access_log a | |
239 | WHERE | |
240 | id > ( | |
241 | SELECT l.last_query_id | |
242 | FROM last_query_by_client l | |
243 | WHERE l.ip_client = a.ip_client | |
244 | ) - 10 | |
245 | ORDER BY a.ip_client, a.id DESC; | |
246 | ||
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); | |
249 | ||
250 | ||
251 | -- number of HIT requests per day | |
252 | CREATE OR REPLACE VIEW hits_per_day AS | |
253 | SELECT | |
254 | date_day, | |
255 | COUNT(*) AS num_hits | |
256 | FROM access_log | |
257 | WHERE squid_request_status LIKE '%HIT%' | |
258 | GROUP BY 1; | |
259 | ||
260 | -- HIT requests per day, percent (100% = total number of requests that day) | |
261 | CREATE OR REPLACE VIEW hits_per_day_perc AS | |
262 | SELECT | |
263 | r.date_day, | |
264 | h.num_hits/r.num_of_requests*100 AS hits_per_day_perc | |
265 | FROM requests_per_day r | |
266 | JOIN | |
267 | hits_per_day h | |
268 | ON r.date_day = h.date_day; | |
269 | ||
270 | ||
271 | -- request methods (count) | |
272 | CREATE OR REPLACE VIEW http_methods AS | |
273 | SELECT | |
274 | http_method, | |
275 | COUNT(*) | |
276 | FROM access_log | |
277 | GROUP BY 1 | |
278 | ORDER BY 1; | |
279 | ||
280 | -- request methods by percent | |
281 | CREATE OR REPLACE VIEW http_methods_perc AS | |
282 | SELECT | |
283 | http_method, | |
284 | COUNT(*) / (SELECT COUNT(*) FROM access_log) * 100 AS perc | |
285 | FROM access_log | |
286 | GROUP BY 1 | |
287 | ORDER BY 2 DESC; | |
288 | ||
289 | ||
290 | -- slowest queries | |
291 | CREATE OR REPLACE VIEW slowest_requests AS | |
292 | SELECT * | |
293 | FROM access_log | |
294 | ORDER BY time_response DESC | |
295 | LIMIT 10; | |
296 | ||
297 | ||
298 | CREATE OR REPLACE VIEW slowest_request_by_method AS | |
299 | SELECT * | |
300 | FROM access_log | |
301 | GROUP BY http_method | |
302 | ORDER BY http_method, time_response DESC; | |
303 | ||
304 | ||
305 | -- requests with the biggest reply size | |
306 | CREATE OR REPLACE VIEW biggest_requests AS | |
307 | SELECT * | |
308 | FROM access_log | |
309 | ORDER BY http_reply_size DESC | |
310 | LIMIT 10; | |
311 | ||
312 | ||
313 | ||
314 | -- list each day which has at least one request, with some statistics | |
315 | CREATE OR REPLACE VIEW days_with_infos AS | |
316 | SELECT | |
317 | date_day, | |
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, | |
326 | ||
327 | -- client that has made the highest number of requests that day | |
328 | ( | |
329 | SELECT ip_client | |
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, | |
334 | ||
335 | -- the number of requests that client actually made | |
336 | ( | |
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, | |
342 | ||
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... | |
347 | ( | |
348 | ( | |
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 | |
353 | ) / ( | |
354 | SELECT COUNT(*) | |
355 | FROM access_log a1 | |
356 | WHERE a.date_day = a1.date_day | |
357 | ) * 100 | |
358 | ) AS most_active_client_r_pc, | |
359 | ||
360 | -- client that has generated the highest traffic that day | |
361 | ( | |
362 | SELECT t.ip_client | |
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, | |
367 | ||
368 | -- the actual traffic generated by that client | |
369 | ( | |
370 | SELECT t.total_bytes | |
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, | |
375 | ||
376 | -- same info expressed in megabytes | |
377 | ( | |
378 | SELECT t.total_bytes | |
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, | |
383 | ||
384 | -- same info in percentage on the total traffic that day | |
385 | -- see previous comments | |
386 | ( | |
387 | ( | |
388 | SELECT t.total_bytes | |
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 | |
392 | ) / ( | |
393 | SELECT SUM(http_reply_size) | |
394 | FROM access_log a1 | |
395 | WHERE a.date_day = a1.date_day | |
396 | ) * 100 | |
397 | ) AS most_active_client_t_pc | |
398 | ||
399 | FROM access_log a | |
400 | GROUP BY 1 | |
401 | ORDER BY 1; | |
402 | ||
403 | -- this index helps the "days_with_info" view | |
404 | CREATE INDEX date_day_idx ON access_log(date_day); | |
405 | ||
406 | ||
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); | |
409 | ||
410 | ||
411 | CREATE OR REPLACE VIEW avg_req_per_minute AS | |
412 | SELECT COUNT(*) FROM requests_in_last_minute; |