优化Centreon统计报表制作

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

where a.host_name = b.host_name;