升级改造报警系统（1）

环境分析及构建

---

• 平台现状

  • zabbix进行对主机进行监控（略）

  • 由提警微信发送接口，发送报警（略）

  • 由报警接收分析平台，对来源报警进行分析，及发送策略定义

• zabbix

    定义内容格式，及添加notify的报警媒介，配置报警动作将报警信息发送给notify；
    调用notify报警接口，进行报警策略分析及触发报警
  

• 构建notify报警策略分析平台

  设计平台流程架构图如下

notify报警接口接收到报警后，进行报警策略测编写及过滤，最后复合发送条件的条用微信接口发送到微信中（在此只研究notify报警策略系统） notify 使用pgsql对象存储接收到的报警信息 * 开发环境构建

    当前的平台环境是CentOS6.4 / Python2.6 / Psql9.3.8 / tornado3.2.2 / SQLAlchemy0.9.8，为兼容历史版本及代码将在此环境上进行开发；
    代码目录结构如下

pip install -r requirements.txt # 安装依赖包
Python bin/start_script #启动

# 安装Psql（略）
pg_restore -d alerts -U alerts -h 127.0.0.1 -p  --jobs=4 --verbose /data/alerts # 历史数据导入

并增加elasticsearch用于前端搜索的restful API使用

安装elasticsearch 因为elasticsearch不兼容Python2.6，需要创建Python2.7

pip install virtualenv
virtualenv --system-site-packages elasticsearch -p python2.7 # 创建一个2.7

source /path/elasticsearch/bin/active #进入虚拟环境

安装elasticsearch服务

yum install -y java-1.7.0-openjdk
rpm --import https://packages.elastic.co/GPG-KEY-elasticsearch
# vi /etc/yum.repos.d/es.repo #修改yum配置
[elasticsearch-2.x]
name=Elasticsearch repository for 2.x packages
baseurl=https://packages.elastic.co/elasticsearch/2.x/centos
gpgcheck=1
gpgkey=https://packages.elastic.co/GPG-KEY-elasticsearch
enabled=1

yum install -y elasticsearch #安装

修复配置文件

# vim /etc/elasticsearch/elasticsearch.yml

cluster.name: notify_dev
path.data: /path/to/data
path.logs: /path/to/logs
network.host: 0.0.0.0
node.name: alerts_dev

/etc/init.d/elasticsearch start #启动

curl 123.59.116.222:9200 #测试
使用put 方法创建 index
123.59.116.222:9200/alerts_dev

至此环境安装完成，安装并启动了代码，数据库增加了大量的历史数据，安装完成了elasticsearch；

PSQL与ES数据同步

安装multicorn和pg_es_fdw进行数据同步

wget http://api.pgxn.org/dist/multicorn/1.0.1/multicorn-1.0.1.zip
unzip multicorn-1.0.1.zip
cd multicorn-1.0.1
make && make install

git clone https://github.com/Mikulas/pg-es-fdw /tmp/pg-es-fdw
cd $_
sudo python setup.py install

创建扩展

CREATE EXTENSION multicorn;

创建该数据源的服务器对象

CREATE SERVER multicorn_es FOREIGN DATA WRAPPER multicorn OPTIONS ( wrapper 'dite.ElasticsearchFDW');

创建外部表(词表要与原表表结构一致)

CREATE FOREIGN TABLE alerts_es (
created_at timestamp,
updated_at timestamp,
deleted_at timestamp,
deleted boolean,
id integer,
keywords character varying(128),
ip character varying(128),
title character varying(128),
content character varying(512),
source character varying(128),
category character varying(128),
sendedid character varying(50))
SERVER multicorn_es OPTIONS (host '127.0.0.1', port '9200', node 'alerts_dev', index 'alerts');

创建监听 trigger

create

CREATE OR REPLACE FUNCTION index_alerts() RETURNS trigger AS $def$
BEGIN
INSERT INTO alerts_es (id, created_at, updated_at, deleted_at, deleted, keywords, ip, title, content, source, category, sendedid) VALUES
(NEW.id, NEW.created_at, NEW.updated_at, NEW.deleted_at, NEW.deleted, NEW.keywords, NEW.ip, NEW.title, NEW.content, NEW.source, NEW.category, NEW.sendedid); RETURN NEW; END; $def$ LANGUAGE plpgsql;

update

CREATE OR REPLACE FUNCTION reindex_alerts() RETURNS trigger AS $def$
BEGIN
UPDATE alerts_es SET
created_at = NEW.created_at, updated_at = NEW.updated_at, deleted_at = NEW.deleted_at, deleted = NEW.deleted, keywords = NEW.keywords, ip = NEW.ip, title = NEW.title, content = NEW.content, source = NEW.source, category = NEW.category, sendedid = NEW.sendedid
 WHERE id = NEW.id; RETURN NEW; END; $def$ LANGUAGE plpgsql;

delete

CREATE OR REPLACE FUNCTION delete_alert() RETURNS trigger AS $def$
BEGIN
DELETE FROM alerts_es a WHERE a.id = OLD.id; RETURN OLD; END; $def$ LANGUAGE plpgsql;

至此alerts原表中进行创建、更新、删除操作将自动同步到elasticsearch中去；

PSQL历史数据同步elasticsearch

因我们往PSQL中导入了大量的历史数据用于数据分析，所以需手动同步下历史数据到ES中； 因数据量较大，编写脚本每插入1000条sleep一下，使用insert语句导入，因insert能触发ES监听同步

run_now_id2= 819493


def insert_one(es_last_id):
    for i in xrange(1, 1000):
        print('---------{0}-----------'.format(i))

        insert_one_row = "PGPASSWORD='' psql -c " + "'" + "insert into alerts_es(created_at, updated_at, deleted_at, id, deleted, keywords, ip, title, content, source, category, sendedid) select created_at, updated_at, deleted_at, id, deleted, keywords, ip, title, content, source, category, sendedid from alerts where id={0}" \
        + "'" + " -U alerts -d aialert"
        print('88' * 20)
        print('Debug:  insert_one_row: {0}'.format(
            insert_one_row.format(int(es_last_id))))
        res = subprocess_execute_command(insert_one_row.format(int(es_last_id)))
        if not res['status']:
            print('Error2: pgsql execute failed {0}'.format(res))
            # continue
            return False
        print('Debug setup 3: res {0}'.format(res))
        es_last_id = es_last_id - 1


def main():
    
    total_num = 818839 / 1000
    a = 0
    _first_alerts_id = 1
    _es_last_id = 819493
    while True:
        if a <= total_num:
            if a == 0:

                global run_now_id2
                run_last_id = run_now_id2
                print('Debug setup 1: es_last_id: ', _first_alerts_id, run_last_id)
            else:

                global run_now_id2
                run_now_id2 = run_now_id2 - 1000
                run_last_id = run_now_id2
                print('Debug setup 2: es_last_id {0} {1}'.format(_es_last_id, run_last_id))
            print("+++++++++++++a: {0}++++++++++++++++++++++".format(a))
            print("Debug: run_start_id {0} ".format(run_last_id))
            if run_last_id > _first_alerts_id:
                inset_one(run_last_id)
                a = a+1
                time.sleep(50)
                print("+++++++++++++a: {0}++++++++++++++++++++++".format(a))

使用postman请求验证

至此，所有的环境构建完成；

---

注意

    tornado使用SQLAlchemy orm对PSQL进行操作，如orm操作时不触发 trigger 则需要在orm save是手动增加同步