0)查询本月数据记录
CREATE TABLE month1 as select id_metric,FROM_UNIXTIME(ctime, '%Y-%m-%d %H:%i:%S') as ctime1,ctime,value,status from data_bin where ctime >= unix_timestamp('2017-03-01') AND ctime < unix_timestamp('2017-03-22');
1)在centreon_storage库中创建关系表test1
CREATE TABLE test1 AS SELECT
index_data.host_name,
index_data.service_description,
metrics.metric_id,
metrics.metric_name
FROM
index_data,
metrics
WHERE
index_data.id = metrics.index_id;
2)性能数据
CREATE TABLE http_time AS SELECT
b.host_name,
b.service_description,
b.metric_name,
a.VALUE,
a.id_metric,
b.metric_id,
a.ctime1
FROM
month1 a,
test1 b
WHERE
a.id_metric = b.metric_id
AND a.ctime >= unix_timestamp('2017-03-01')
AND a.ctime < unix_timestamp('2017-03-22');
select count(*),metric_name from http_time group by metric_name order by count(*) desc;
3)数据取值调整
CREATE TABLE http_time_1 AS SELECT
host_name,
service_description,
metric_name,
ctime1,
ROUND(AVG(VALUE)*1000,2) as avg,
ROUND(MAX(VALUE)*1000,2) as max,
ROUND(MIN(VALUE)*1000,2) as min
FROM
http_time where metric_name = 'time'
GROUP BY
host_name;
CREATE TABLE cpu_user_1 AS SELECT
host_name,
service_description,
metric_name,
ctime1,
ROUND(AVG(VALUE),2) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
http_time where metric_name = 'cpu0'
GROUP BY
host_name;
4)查询
SELECT
a.host_name,
b.host_name,
a.service_description,
a.metric_name,
round(a.avg,2),
a.max,
a.min,
b.service_description,
b.metric_name,
round(b.avg*1000,2),
round(b.max*1000,2),
round(b.min*1000,2)
FROM
cpu_user_1 a,
http_time_1 b
CREATE TABLE month1 as select id_metric,FROM_UNIXTIME(ctime, '%Y-%m-%d %H:%i:%S') as ctime1,ctime,value,status from data_bin where ctime >= unix_timestamp('2017-03-01') AND ctime < unix_timestamp('2017-03-22');
1)在centreon_storage库中创建关系表test1
CREATE TABLE test1 AS SELECT
index_data.host_name,
index_data.service_description,
metrics.metric_id,
metrics.metric_name
FROM
index_data,
metrics
WHERE
index_data.id = metrics.index_id;
2)性能数据
CREATE TABLE http_time AS SELECT
b.host_name,
b.service_description,
b.metric_name,
a.VALUE,
a.id_metric,
b.metric_id,
a.ctime1
FROM
month1 a,
test1 b
WHERE
a.id_metric = b.metric_id
AND a.ctime >= unix_timestamp('2017-03-01')
AND a.ctime < unix_timestamp('2017-03-22');
select count(*),metric_name from http_time group by metric_name order by count(*) desc;
3)数据取值调整
CREATE TABLE http_time_1 AS SELECT
host_name,
service_description,
metric_name,
ctime1,
ROUND(AVG(VALUE)*1000,2) as avg,
ROUND(MAX(VALUE)*1000,2) as max,
ROUND(MIN(VALUE)*1000,2) as min
FROM
http_time where metric_name = 'time'
GROUP BY
host_name;
CREATE TABLE cpu_user_1 AS SELECT
host_name,
service_description,
metric_name,
ctime1,
ROUND(AVG(VALUE),2) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
http_time where metric_name = 'cpu0'
GROUP BY
host_name;
4)查询
SELECT
a.host_name,
b.host_name,
a.service_description,
a.metric_name,
round(a.avg,2),
a.max,
a.min,
b.service_description,
b.metric_name,
round(b.avg*1000,2),
round(b.max*1000,2),
round(b.min*1000,2)
FROM
cpu_user_1 a,
http_time_1 b
where a.host_name = b.host_name;