Nagios+centreon做分散式後導致centreon_storage資料庫資料重複

Michael_DD發表於2014-09-19
iOS分散式資料庫
Nagios+centreon做分散式後導致centreon_storage資料庫資料重複
Centreon增加poller後，出現centreon_storage資料庫中log_archive_host 與 log_archive_service 表資料重複，report圖表不準確。


原因:節點直接從主節點複製，沒有關閉centreon軟體，節點上不需要centreon軟體

report報告centreon提取資料指令碼原始碼

host

點選(此處)摺疊或開啟

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

點選(此處)摺疊或開啟

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



處理辦法：刪除重複資料

點選(此處)摺疊或開啟

  1. host:

  3. 新建一個臨時表host_test1存放重複的資料：

  5. CREATE TABLE host_test1 (
  6.     SELECT
  7.         host_id,
  8.         date_start,
  9.         count(*)
  10.     FROM
  11.         log_archive_host
  12.     GROUP BY
  13.         host_id,
  14.         date_start
  15.     HAVING
  16.         count(*) > 1
  17. )


  20. 新建一個臨時表host_test2存放重複的資料：


  23. CREATE TABLE host_test2 (
  24.     SELECT
  25.         max(log_id) as log_idtest
  26.     FROM
  27.         log_archive_host
  28.     WHERE
  29.         (host_id, date_start) IN (
  30.             SELECT
  31.                 host_id,
  32.                 date_start
  33.             FROM
  34.                 host_test1
  35.         )
  36.     GROUP BY
  37.         host_id,
  38.         date_start
  39. )

  41. select * from host_test1;
  42. select * from host_test2;

  44. 刪除重複資料
  1. delete from log_archive_host where log_id in(select log_idtest from host_test2);

  3. drop table host_test1
  4. drop table host_test2

  6. 提交會話：
  7. commit




  12. service:

  14. 建立索引：
  15. create index idx_lam_sid_sd on log_archive_service(service_id,date_start)

  17. 新建一個臨時表service_test1存放重複的資料：

  19. CREATE TABLE service_test1 (
  20.     SELECT
  21.         service_id,
  22.         date_start,
  23.         count(*)
  24.     FROM
  25.         log_archive_service
  26.     GROUP BY
  27.         service_id,
  28.         date_start
  29.     HAVING
  30.         count(*) > 1
  31. )


  34. 新建一個臨時表service_test2存放重複的資料：


  37. CREATE TABLE service_test2 (
  38.     SELECT
  39.         max(log_id) as log_idtest
  40.     FROM
  41.         log_archive_service
  42.     WHERE
  43.         (service_id, date_start) IN (
  44.             SELECT
  45.                 service_id,
  46.                 date_start
  47.             FROM
  48.                 service_test1
  49.         )
  50.     GROUP BY
  51.         service_id,
  52.         date_start
  53. )



  57. select * from service_test1;
  58. select * from service_test2;

  60. 刪除重複的資料：
  61. delete from log_archive_service where log_id in(select log_idtest from service_test2);

  63. 刪除臨時表：
  64. drop table service_test1
  65. drop table service_test2



  69. 提交會話：
  70. commit

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1274249/，如需轉載，請註明出處，否則將追究法律責任。

相關文章