This topic describes cases of log data analysis.
Trigger an alert 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
greatest(max(__time__) - min(__time__),1)for normalization to count the average traffic per minute.
* | SELECT SUM(inflow) / greatest(max(__time__) - min(__time__),1) 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
* | 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