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,
FROM_UNIXTIME(a.ctime, '%Y-%m-%d %H:%i:%S') as ctime
FROM
data_bin 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-21')
AND b.metric_name = 'time';
CREATE TABLE cpu_user AS SELECT
b.host_name,
b.service_description,
b.metric_name,
a.VALUE,
a.id_metric,
b.metric_id,
FROM_UNIXTIME(a.ctime, '%Y-%m-%d %H:%i:%S') as ctime
FROM
data_bin 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-21')
AND b.metric_name = 'CpuUser';
3)数据取值调整
CREATE TABLE http_time_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE)*1000,2) as avg,
ROUND(MAX(VALUE)*1000,2) as max,
ROUND(MIN(VALUE)*1000,2) as min
FROM
http_time
GROUP BY
host_name;
CREATE TABLE cpu_user_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE),2) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
cpu_user
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;
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,
FROM_UNIXTIME(a.ctime, '%Y-%m-%d %H:%i:%S') as ctime
FROM
data_bin 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-21')
AND b.metric_name = 'time';
CREATE TABLE cpu_user AS SELECT
b.host_name,
b.service_description,
b.metric_name,
a.VALUE,
a.id_metric,
b.metric_id,
FROM_UNIXTIME(a.ctime, '%Y-%m-%d %H:%i:%S') as ctime
FROM
data_bin 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-21')
AND b.metric_name = 'CpuUser';
3)数据取值调整
CREATE TABLE http_time_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE)*1000,2) as avg,
ROUND(MAX(VALUE)*1000,2) as max,
ROUND(MIN(VALUE)*1000,2) as min
FROM
http_time
GROUP BY
host_name;
CREATE TABLE cpu_user_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE),2) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
cpu_user
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;