Grafana使用日志2--对接MySQL数据库的一些注意点

背景

之前zabbix有一个需求是监控每一笔交易的耗时，即结束时间-开始时间，现在由于业务需求，需要在Grafana中统计所有交易时间的占比，分组并展示
但是接入的zabbix插件并不支持该功能，zabbix插件只能够查询出每个interval间隔内的数据并展示，并不能将所有时间内的数据汇总并分析，所以冥思苦想之下，只能接入zabbix的mysql，用sql脚本的形式获取数据

设计思路

思路就是，把对应的host group中的所有的host对应的每一个item，从item表中取出并在history表中查询一段时间内的所有数据，并按照大小进行分组

接入Zabbix数据源

data source选择mysql，然后只需要填入API信息以及用户名密码，点击最下面的save&test就可以看连接是否成功了

最终实现

SELECT 
    i.name AS item_name,                      -- Item 名称
    CASE 
        WHEN hu.value BETWEEN 0 AND 1000 THEN '[0s-1s]' 
        WHEN hu.value BETWEEN 1000 AND 2000 THEN '[1s-2s]' 
        WHEN hu.value BETWEEN 2000 AND 3000 THEN '[2s-3s]' 
        WHEN hu.value BETWEEN 3000 AND 4000 THEN '[3s-4s]' 
        WHEN hu.value BETWEEN 4000 AND 5000 THEN '[4s-5s]'
        ELSE 'Other' 
    END AS value_range,
    COUNT(*) AS count
FROM 
    history hu
JOIN 
    items i ON hu.itemid = i.itemid
JOIN 
    hosts h ON i.hostid = h.hostid
JOIN 
    hosts_groups hg ON h.hostid = hg.hostid
JOIN 
    hstgrp g ON hg.groupid = g.groupid
WHERE 
    g.name IN ('host group name')
    AND i.name REGEXP '^transaction time \\[$TXN_TYPE\\]' 
    AND hu.clock BETWEEN '$__unixEpochFrom()' AND '$__unixEpochTo()'
GROUP BY 
    value_range
ORDER BY 
    value_range; 

问题记录

^transaction time [$TXN_TYPE] 无法获取到数据

解决方案

在 MySQL 中，使用 REGEXP 时，特殊字符（如方括号 [ ]）具有特定的含义。例如，[ ] 通常用于定义字符集（例如，[a-z] 匹配所有小写字母）。因此，您的正则表达式 ^Transaction Time of type [Sale] 中的方括号被解释为字符集匹配，而不是字面匹配。
为了让 MySQL 将方括号视为普通字符，而不是正则表达式的特殊符号，需要对它们进行 转义。

正确写法如下：

i.name REGEXP '^transaction time \\[$TXN_TYPE\\]' 

$__timeFilter(hu.clock)没有作用

本来没有定义查询的时间范围，后来想要根据Grafana上的时间自动修改时间，于是想着用上$__timeFilter(hu.clock)这个方法，但是始终检索不出来任何结果，于是看了下它的查询

调查

发现他会把这个方法转换成如下的语句

AND hu.clock BETWEEN FROM_UNIXTIME(1704179643) AND FROM_UNIXTIME(1735802043)

from_unixtime是MySQL里的时间函数date为需要处理的参数（该参数是Unix 时间戳），可以是字段名，也可以直接是Unix 时间戳字符串后面的 '%Y%m%d' 主要是将返回值格式化。
百度百科附上： https://baike.baidu.com/item/from_unixtime/1801387?fr=ge_ala

但是在数据库中，该clock字段就是按照时间戳来保存的，所以说根本就不需要转换成字符串格式

修改sql语句

AND hu.clock BETWEEN '$__timeFrom()' AND '$__timeTo()'

依旧无效,于是查看官网上的说明，发现$__timeFrom()其实还是会转换成FROM_UNIXTIME(1494410783)
这边我们应该用的是$__unixEpochFrom()和$__unixEpochTo()

最终解决方案

AND hu.clock BETWEEN '$__unixEpochFrom()' AND '$__unixEpochTo()'