Nagios+centreon做分布式后导致centreon_storage数据库数据重复

Centreon增加poller后，出现centreon_storage数据库中log_archive_host 与 log_archive_service 表数据重复，report图表不准确。

原因:节点直接从主节点拷贝，没有关闭centreon软件，节点上不需要centreon软件

report报告centreon提取数据脚本源代码

host

点击(此处)折叠或打开

SELECT sum(`UPnbEvent`) as UP_A, sum(`UPTimeScheduled`) as UP_T, \".
\" sum(`DOWNnbEvent`) as DOWN_A, sum(`DOWNTimeScheduled`) as DOWN_T, \" .
\" sum(`UNREACHABLEnbEvent`) as UNREACHABLE_A, sum(`UNREACHABLETimeScheduled`) as UNREACHABLE_T, \" .
\" sum(`UNDETERMINEDTimeScheduled`) as UNDETERMINED_T, \" .
\" sum(`MaintenanceTime`) as MAINTENANCE_T \" .
\"FROM `log_archive_host` \".
\"WHERE `host_id` = \".$host_id.\" AND `date_start` >= \".$start_date.\" AND `date_end` <= \".$end_date.\" \".
\"AND DATE_FORMAT( FROM_UNIXTIME( `date_start`), \'%W\') IN (\".$days_of_week.\") \

service：

点击(此处)折叠或打开

SELECT service_id, sum(`OKTimeScheduled`) as OK_T, sum(`OKnbEvent`) as OK_A, \".
\"sum(`WARNINGTimeScheduled`) as WARNING_T, sum(`WARNINGnbEvent`) as WARNING_A, \".
\"sum(`UNKNOWNTimeScheduled`) as UNKNOWN_T, sum(`UNKNOWNnbEvent`) as UNKNOWN_A, \".
\"sum(`CRITICALTimeScheduled`) as CRITICAL_T, sum(`CRITICALnbEvent`) as CRITICAL_A, \".
\"sum(`UNDETERMINEDTimeScheduled`) as UNDETERMINED_T, \".
\"sum(`MaintenanceTime`) as MAINTENANCE_T \".
\"FROM `log_archive_service` \".
\"WHERE `host_id` = \".$host_id.\" \".
$oreon->user->access->queryBuilder(\"AND\", \"service_id\", $svcStr) .
\"AND `date_start` >= \".$start_date.\" AND `date_end` <= \".$end_date.\" \".
\"AND DATE_FORMAT( FROM_UNIXTIME( `date_start`), \'%W\') IN (\".$days_of_week.\") \".
\"GROUP BY `service_id`

处理办法：删除重复数据

点击(此处)折叠或打开

delete from log_archive_host where log_id in(select log_idtest from host_test2);
drop table host_test1
drop table host_test2
提交会话：
commit
service:
创建索引：
create index idx_lam_sid_sd on log_archive_service(service_id,date_start)
新建一个临时表service_test1存放重复的数据：
CREATE TABLE service_test1 (
SELECT
service_id,
date_start,
count(*)
FROM
log_archive_service
GROUP BY
service_id,
date_start
HAVING
count(*) > 1
)
新建一个临时表service_test2存放重复的数据：
CREATE TABLE service_test2 (
SELECT
max(log_id) as log_idtest
FROM
log_archive_service
WHERE
(service_id, date_start) IN (
SELECT
service_id,
date_start
FROM
service_test1
)
GROUP BY
service_id,
date_start
)
select * from service_test1;
select * from service_test2;
删除重复的数据：
delete from log_archive_service where log_id in(select log_idtest from service_test2);
删除临时表：
drop table service_test1
drop table service_test2
提交会话：
commit

ITPub博客

Nagios+centreon做分布式后导致centreon_storage数据库数据重复