夜莺-Nightingale
夜莺V7
项目介绍 功能概览
部署升级 部署升级
数据接入 数据接入
告警管理 告警管理
数据查看 数据查看
功能介绍 功能介绍
API FAQ
夜莺V6
项目介绍 架构介绍
快速开始 快速开始
黄埔营
安装部署 安装部署
升级
采集器 采集器
使用手册 使用手册
API API
数据库表结构 数据库表结构
FAQ FAQ
开源生态
Prometheus
版权声明
第1章:天降奇兵 第1章:天降奇兵
第2章:探索PromQL 第2章:探索PromQL
第3章:Prometheus告警处理 第3章:Prometheus告警处理
第4章:Exporter详解 第4章:Exporter详解
第5章:数据与可视化 第5章:数据与可视化
第6章:集群与高可用 第6章:集群与高可用
第7章:Prometheus服务发现 第7章:Prometheus服务发现
第8章:监控Kubernetes 第8章:监控Kubernetes
第9章:Prometheus Operator 第9章:Prometheus Operator
参考资料

Query Tables

Similar to logging into MySQL via the terminal and inputting an SQL query, for example, querying all data from a table:

select * from database_name.table_name limit 10

Query Time Series Chart

  1. You must use the as time syntax in SQL to specify which column is the time column, then group by time order by time desc to sort by time, for example:
select count(*) as count, trigger_time as time 
from n9e_v6_plus.alert_his_event 
group by time 
order by time 
desc LIMIT 100

  1. You can use the where clause to query data from a specific time period, as shown in the following example:

2.1 Query data from the last minute. You can also replace WEEK with MINUTE, SECOND, HOUR, DAY, MONTH, etc.

SELECT count(*) AS count, trigger_time AS time FROM n9e_v6_plus.alert_his_event  WHERE FROM_UNIXTIME(trigger_time) >= DATE_SUB(NOW(), INTERVAL 1 WEEK)

2.2 Query data from a specific time period. If trigger_time is a Unix timestamp (1720061167), the WHERE statement can directly write WHERE trigger_time >= 1720060214 AND trigger_time < 1720061214 or trigger_time >= $__unixEpochFrom() AND trigger_time < $__unixEpochTo(). $__unixEpochFrom() represents the start Unix timestamp, and $__unixEpochTo() represents the end Unix timestamp.

SELECT count(*) AS count, trigger_time AS time 
FROM n9e_v6_plus.alert_his_event  
WHERE trigger_time >= $__unixEpochFrom() AND trigger_time < $__unixEpochTo()
GROUP BY time 
ORDER BY time DESC

2.3 Query the number of alerts generated per minute in the last 7 days

SELECT FROM_UNIXTIME(trigger_time, '%Y-%m-%d %H:%i:00') AS alert_minute,COUNT(*) AS alert_count 
FROM n9e_v6_plus.alert_his_event 
WHERE trigger_time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) 
GROUP BY alert_minute 
ORDER BY alert_minute DESC LIMIT 100;

2.4 Query the number of alerts generated per minute in the last 7 days

SELECT FROM_UNIXTIME(trigger_time, '%Y-%m-%d %H:%i:00') AS alert_minute,COUNT(*) AS alert_count 
FROM n9e_v6_plus.alert_his_event 
WHERE trigger_time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY)) 
GROUP BY alert_minute 
ORDER BY alert_minute DESC;

2.5 Example table structure for alert_his_event

CREATE TABLE `alert_his_event` (
    `id` bigint unsigned not null AUTO_INCREMENT,
    `cate` varchar(128) not null,
    `rule_id` bigint unsigned not null,
    `rule_name` varchar(255) not null,
    `prom_ql` varchar(8192) not null comment 'promql',
    `first_trigger_time` bigint,
    `trigger_time` bigint not null,
    `trigger_value` varchar(255) not null,
    PRIMARY KEY (`id`),
    KEY (`trigger_time`, `rule_id`)
) ENGINE=InnoDB DEFAULT CHARSET = utf8mb4;

Using Macros

When configuring dashboards, it is common to require dynamic time ranges that change according to the time range selected in the dashboard’s top-right corner. In such cases, you can use time-related macros. Below are the currently supported macros and their descriptions (Note: in the following explanations, 1494410783 is the start time passed by the front end, and 1494410983 is the end time passed by the front end):

Macro Description
$__timeFilter(dateColumn) If dateColumn is in the format %Y-%m-%d %H:%i:%s, this macro can be used, and it will be replaced by dateColumn BETWEEN FROM_UNIXTIME(1494410783) AND FROM_UNIXTIME(1494410983)
$__timeFrom() If the time column format is %Y-%m-%d %H:%i:%s, this macro can be used and will be replaced with the selected start time. FROM_UNIXTIME(1494410783)
$__timeTo() If the time column format is %Y-%m-%d %H:%i:%s, this macro can be used and will be replaced with the selected end time. FROM_UNIXTIME(1494410983)
$__unixEpochFilter(dateColumn) If dateColumn is in Unix timestamp format, it will be replaced by dateColumn > 1494410783 AND dateColumn < 1494497183
$__unixEpochFrom() Replaced by the currently selected start time in Unix timestamp format. For example, 1494410783
$__unixEpochTo() Replaced by the currently selected end time in Unix timestamp format. For example, 1494497183
$__unixEpochNanoFilter(dateColumn) If dateColumn is in nanosecond timestamp format, it will be replaced by dateColumn > 1494410783152415214 AND dateColumn < 1494410983142514872
$__unixEpochNanoFrom() Replaced by the currently selected start time in nanosecond timestamp format. For example, 1494410783152415214
$__unixEpochNanoTo() Replaced by the currently selected end time in nanosecond timestamp format. For example, 1494410983142514872
$__timeGroup(dateColumn,'5m') Used in group by to aggregate data by 5-minute intervals
$__unixEpochGroup(dateColumn,'5m') Used in group by to aggregate data by 5-minute intervals

Example of Using Macros

Using the alert_his_event table as an example, the SQL query to count the number of alerts per minute within a specific time range is as follows:

SELECT 
  $__unixEpochGroup(trigger_time, '1m') AS time,
  COUNT(*) AS alert_count
FROM 
  n9e_v6_plus.alert_his_event
WHERE 
  $__unixEpochFilter(trigger_time)
GROUP BY 
  time
ORDER BY 
  time
快猫星云 联系方式 快猫星云 联系方式
快猫星云 联系方式
快猫星云 联系方式
快猫星云 联系方式
快猫星云
OpenSource
开源版
Flashcat
Flashcat