grafana-mysql数据源

告警总量分析

----------------------------------------
## 今天截至当前时间点
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id) as "今天截至当前时间点总量"
FROM global_message_info
WHERE
  $__timeFilter(row_create_time) and date(now())<row_create_time 
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d')
-----------------------------------------
## 昨天截至昨天当前时间点
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id) as "昨天截至昨天当前时间点"
FROM global_message_info
WHERE
  $__timeFilter(row_create_time) and DATE_SUB(CURDATE(), INTERVAL 1 DAY)<row_create_time and row_create_time<DATE_SUB(now(),INTERVAL 1 DAY)
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d')
-----------------------------------------
## 21每天告警总量
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id) as "21每天告警总量"
FROM global_message_info
WHERE
  $__timeFilter(row_create_time)
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d')
--------------------------------
## 7天平均告警量
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
(SELECT
  FLOOR(count(global_message_id)/7) 
FROM global_message_info
WHERE
    DATE_SUB(CURDATE(), INTERVAL 8 DAY) < date(row_create_time) and date(row_create_time) <CURDATE()) as "7天平均告警量" FROM global_message_info
WHERE DATE_SUB(CURDATE(), INTERVAL 8 DAY) < date(row_create_time) and date(row_create_time) <CURDATE()
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d')
-----------------------------------------------------

分类报警次数

 ---------------------------------------------
## class1-分类报警次数
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id) ,
  self_var_flag12 as metric
FROM global_message_info
WHERE
  $__timeFilter(row_create_time)
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d'),self_var_flag12
------------------------------------------------
## class1,CLASS2-分类报警次数
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id) ,
  concat(self_var_flag12,"-",self_var_flag13) as metric
FROM global_message_info
WHERE
  $__timeFilter(row_create_time)
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d'),self_var_flag12,self_var_flag13
-----------------------------------------------------

close_wait

--------------------------------------------
# close_wait-今天截至当前
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id),
  concat(SELF_VAR_FLAG6,"-",SELF_VAR_FLAG7,"-",SELF_VAR_FLAG8,"-",SELF_VAR_FLAG9) as metric
FROM global_message_info
WHERE
  $__timeFilter(row_create_time) AND message_info LIKE 'Close_wait connection%'  AND self_var_flag9 NOT IN ('运维')
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d'),SELF_VAR_FLAG6,SELF_VAR_FLAG7,SELF_VAR_FLAG8,SELF_VAR_FLAG9
------------------------------------------------------

kafka

-------------------------------------------
# 告警次数
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  count(global_message_id),
  concat(SELF_VAR_FLAG9,'-',SUBSTRING_INDEX(MESSAGE_INFO, 'Lag:', 1)) as metric
FROM global_message_info
WHERE
  $__timeFilter(row_create_time) AND SELF_VAR_FLAG13 = 'KAFKA' and SELF_NUM_FLAG1>10000
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d'),SELF_VAR_FLAG9,SUBSTRING_INDEX(MESSAGE_INFO, 'Lag:', 1)
-------------------------------------------------
# value大小
SELECT
  UNIX_TIMESTAMP(DATE_FORMAT(row_create_time, '%Y-%m-%d')) AS "time",
  max(SELF_NUM_FLAG1),
  concat(SELF_VAR_FLAG9,'-',SUBSTRING_INDEX(MESSAGE_INFO, 'Lag:', 1)) as metric
FROM global_message_info
WHERE
  $__timeFilter(row_create_time) AND SELF_VAR_FLAG13 = 'KAFKA' and SELF_NUM_FLAG1>10000
group BY DATE_FORMAT(row_create_time, '%Y-%m-%d'),SELF_VAR_FLAG9,SUBSTRING_INDEX(MESSAGE_INFO, 'Lag:', 1)
-------------------------------------------

当日告警分析