阿里SLB-LOG-SQL日志查询语句

文章目录

• • • 阿里云`SLB-LOG-SQL`图表展示
    • `SQL`查询语句汇总

阿里云SLB-LOG-SQL图表展示

• 请求次数、客户端个数、成功/失败率

• 按请求时间排序查看PV/UV

• 按请求时间前15排序查看PV/UV/URL

按PV前15排序查看接口

按每分PV钟大于1000的排序查看URL

SQL查询语句汇总

十五分钟内状态图表展示SQL语句，时间在定义SQL时可自选

图表大小自调，可根据下图以作参考

# 请求次数（单值图）（进度条）
* | select COUNT(request_uri) * 0.0001 pv;

# 客户端个数（单值图）
* | select count(distinct client_ip) as "客户端个数";

# 请求成功率
 * | select round(sum(if(status = 200, 1, 0)) * 100.0/ count(1), 2) as sucess_ratio;

# 请求失败率
 * | select round(sum(if(status != 200, 1, 0)) * 100.0/ count(1), 2) as error_ratio;
 
# 请求时间（线图、表格）
* | select COUNT(request_uri) pv,COUNT(distinct client_ip) uv,
max(request_time) as "最大请求时间",
min(request_time) as "最小请求时间",
avg(request_time) as "平均请求时间",date_format(__time__, '%m-%d %H:%i')as t
GROUP BY t 
order by "最大请求时间" 
DESC 
LIMIT 1000;

# 请求时间前15的接口（线图）
* | select date_format(__time__, '%m-%d %H:%i')as t ,COUNT(request_uri) pv,COUNT(distinct client_ip) uv,MAX(request_uri) as url,min(request_time) as min_time,avg(request_time) as avg_time,max(request_time) as max_time
GROUP BY t 
ORDER BY max_time 
DESC  
LIMIT 15;

# 请求时间前15的接口（表格）
* | select COUNT(request_uri) pv,COUNT(distinct client_ip) uv,MAX(request_uri) as url,max(request_time) as max_time,min(request_time) as min_time,avg(request_time) as avg_time,date_format(__time__, '%m-%d %H:%i')as t
GROUP BY t 
ORDER BY max_time 
DESC
LIMIT 15;

# PV前15接口（线图）
* | select date_format(__time__, '%m-%d %H:%i')as t ,COUNT(request_uri) pv,
COUNT(distinct client_ip) uv,
min(request_time) as min_time,
avg(request_time) as avg_time,max(request_time) as max_time,
MAx(request_uri) as url
GROUP BY t 
ORDER BY COUNT(request_uri) 
DESC  
LIMIT 15;

# PV前15接口（表格）
* | select COUNT(request_uri) pv,
COUNT(distinct client_ip) uv,
MAX(request_uri) as url,
max(request_time) as max_time,min(request_time) as min_time,avg(request_time) as avg_time,date_format(__time__, '%m-%d %H:%i')as t
GROUP BY t 
ORDER BY pv 
DESC  
LIMIT 15;

# 每分钟PV大于10000（线图）
* | select date_format(__time__, '%m-%d %H:%i')as t,
COUNT(request_uri) pv,
COUNT(distinct client_ip) uv,
max(request_time) as max_time,
avg(request_time) as avg_time 
GROUP BY t 
LIMIT 2000;

# 每分钟PV大于10000（图表）
* | select date_format(__time__, '%m-%d %H:%i')as t ,
COUNT(request_uri) pv,
COUNT(distinct client_ip) uv,max(request_time) as max_time,
avg(request_time) as avg_time 
GROUP BY t 
ORDER BY COUNT(request_uri) 
DESC 
LIMIT 2000;