[SYSTEM]
- Grafana versoin: v8.1.3
- OS: Red Hat Enterprise Linux
- MySQL version: v8.0.34
Hello:)
I’m creating the dashboard with MySQL Data source. The time data performed the query directly in the database is different from the data performed the query in the grafana dashboard.
-
This is the time data from database.
-
This is the time data from grafana.
** The timing of data is the same. But I got the different data. Even though today is August 16th.
- This is the query i made.
select
a.hostname as “Hostname”
,a.timedate as “Time”
,a.cpu_sys as “System or Privilegedtime”
,b.cpu_user as “User or Usertime”
,c.cpu_iowait as “Iowait or Processortime”
,d.cpu_idle as “Idle or Interrupttime”
FROM
(
SELECT
h.host as hostname
,STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’) as timedate
,MAX(hi.value) as cpu_sys
from items i , hosts h , history hi
where 1=1
and i.hostid=h.hostid
and i.itemid = hi.itemid
and hi.clock >= 1692167206 AND hi.clock <= 1692170806 ##Time range 변수
and i.name in (
##평균을 내고싶은 종목 명
‘CPU system 10m’
)
and h.host in (‘kbamstarf02’) ## 시스템 명
GROUP BY
h.host,
STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’)
) a,
(
SELECT
h.host as hostname
,STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’) as timedate
,MAX(hi.value) as cpu_user
from items i , hosts h , history hi
where 1=1
and i.hostid=h.hostid
and i.itemid = hi.itemid
and hi.clock >= 1692167206 AND hi.clock <= 1692170806 ##Time range 변수
and i.name in (
##평균을 내고싶은 종목 명
‘CPU user 10m’
)
and h.host in (‘kbamstarf02’) ## 시스템 명
GROUP BY
h.host,
STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’)
) b ,
(
SELECT
h.host as hostname
,STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’) as timedate
,MAX(hi.value) as cpu_iowait
from items i , hosts h , history hi
where 1=1
and i.hostid=h.hostid
and i.itemid = hi.itemid
and hi.clock >= 1692167206 AND hi.clock <= 1692170806 ##Time range 변수
and i.name in (
##평균을 내고싶은 종목 명
‘CPU iowait 10m’
)
and h.host in (‘kbamstarf02’) ## 시스템 명
GROUP BY
h.host,
STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’)
) c,
(
SELECT
h.host as hostname
,STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’) as timedate
,MAX(hi.value) as cpu_idle
from items i , hosts h , history hi
where 1=1
and i.hostid=h.hostid
and i.itemid = hi.itemid
and hi.clock >= 1692167206 AND hi.clock <= 1692170806 ##Time range 변수
and i.name in (
##평균을 내고싶은 종목 명
‘CPU idle 10m’
)
and h.host in (‘kbamstarf02’) ## 시스템 명
GROUP BY
h.host,
STR_TO_DATE(truncate(DATE_FORMAT(FROM_UNIXTIME(hi.clock),‘%Y%m%d%H%i%S’),-3),‘%Y%m%d%H%i%S’)
) d
where 1=1
and a.hostname = b.hostname
and a.hostname = c.hostname
and a.hostname = d.hostname
and a.timedate = b.timedate
and a.timedate = c.timedate
and a.timedate = d.timedate
It doesn’t seem to be a query problem. Because I can get the data well when I performed a query directly from the database.
Does anyone know what’s wrong?
I’d really appreciate it if you could answer.
