Common examples of log data analysis - Simple Log Service

This topic provides some examples of log data analysis.

Trigger an alert when the error rate exceeds 40% within the previous 5 minutes

Calculate the rate at which an HTTP 500 error is returned every minute to trigger an alert when the error rate exceeds 40% within the previous 5 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

Collect traffic statistics and configure alerts

Collect traffic statistics every minute to trigger an alert when the traffic volume falls below a specific threshold. The statistics that are collected in the previous minute do not cover a full minute. To collect the average traffic statistics per minute, divide the statistical value by greatest(max(__time__) - min(__time__),1) for normalization.

* | 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 by data size

Distribute data to multiple buckets based on the data size and calculate the average latency of data in each bucket.

* | 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 the results. This query includes subqueries 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 URLs that meet the query condition

To count the number of URLs based on their characteristics, you can use the CASE WHEN clause or the COUNT_IF clause. The latter clause is simpler.

* | 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