Shell脚本一键推送PostgreSQL数据库查询结果至钉群告警

1.文件目录结构如下，创建device_mapping.txt文件

[root@localhost services_monitor]# pwd
/mnt/services_monitor
[root@localhost services_monitor]# ll
total 8
-rw-r--r-- 1 root root 1519 Dec 11 18:38 device_mapping.sh
-rw-r--r-- 1 root root  156 Dec 11 18:39 device_mapping.txt

2.Shell脚本内容如下 

cat  device_mapping.sh
#:!/bin/bash

# 设备列表文件绝对路径
text_file="/mnt/services_monitor/device_mapping.txt"

# pg数据库密码
export PGPASSWORD='88888888888888888'


#PG数据库SQL查询语句
sql_cmd="SELECT platform,device_sub_type  FROM ( SELECT datasource, device_sub_type FROM aio_brm.device WHERE del_flag = '0' AND device_type = '-' GROUP BY device_sub_type, datasource ) base LEFT JOIN ( SELECT VALUE,COALESCE ( language_value, label ) AS platform FROM pig.sys_dict_item LEFT JOIN pig.LANGUAGE ON label = language_key AND "language" = 'zh' WHERE dict_key = 'datasource' ) trans ON base.datasource = trans.VALUE"


#PSQL命令行
#-U 用户名
#-d  数据库
#-p  端口号
psql -U prd_pg01 -d  database01  -p 99999 -h 127.0.0.1   -c  "$sql_cmd" > $text_file



#钉钉webhook
webhook_url="https://oapi.dingtalk.com/robot/send?access_token=999999999999999999999999999999999999999999999999999999999999999"


#钉钉MESSAGE
MESSAGE=$(cat "$text_file")



#钉钉推送告警信息
dingding_push(){
      curl      -H "Content-Type: application/json" \
                -d '{
                    "msgtype": "text",
                    "text": {
                        "content": "【生产环境告警】:\n '"$MESSAGE"' \n"
                    }
                }' $webhook_url
}


main(){
   dingding_push
}


main

3. 配置计划任务

[root@localhost services_monitor]# crontab  -l
00  10   *   *   *   /bin/bash  /mnt/services_monitor/device_mapping.sh  >  /dev/null  2>&1