This topic describes cases of log data analysis.
Case list
Trigger an alert when the error rate exceeds 40% over the last five minutes
Calculate the percentage of error 500 every minute. An alert is triggered when the
error rate exceeds 40% over the last five minutes.
status:500 | select __topic__, max_by(error_count,window_time)/1.0/sum(error_count) as error_ratio, sum(error_count) as total_error from (
select __topic__, count(*) as error_count , __time__ - __time__ % 300 as window_time from log group by __topic__, window_time
)
group by __topic__ having max_by(error_count,window_time)/1.0/sum(error_count) > 0.4 and sum(error_count) > 500 order by total_error desc limit 100
Count traffic and configure alerts
Count traffic every minute. An alert is triggered when traffic plunges. Traffic counted
in the last minute does not cover a full minute. Therefore, divide the statistical
value by using
(max(time) - min(time))
for normalization to count the average traffic per minute.
* | SELECT SUM(inflow) / (max(__time__) - min(__time__)) as inflow_per_minute, date_trunc('minute',__time__) as minute group by minute
Calculate the average latency of each data interval
Calculate the average latency of each bucket set by data interval.
* | select avg(latency) as latency , case when originSize < 5000 then 's1' when originSize < 20000 then 's2' when originSize < 500000 then 's3' when originSize < 100000000 then 's4' else 's5' end as os group by os
Return the percentages of different results
Return the count results of different departments and the percentages of these results.
This query combines the subquery and window functions. sum(c) over()
indicates the sum of values in all rows.
* | select department, c*1.0/ sum(c) over () from(select count(1) as c, department from log group by department)
Count the number of logs that meet the query condition
URLs must be by counted by characteristics. In this situation, you can use the CASE
WHEN syntax and the simpler count_if syntax.
* | select count_if(uri like '%login') as login_num, count_if(uri like '%register') as register_num, date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as time group by time order by time limit 100