[monitoring] how to create script to monitoring the status of orders and make graphs

Oracle 2020-06-02

The purpose is to make a graph like this.

Then,let's begin.

Install oracle support
"oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm" -- basic lib
"oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm" -- support lib for part
"oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm"
Install python_oracle support "cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm"
add the following configuration to the profile.

export PATH=/usr/lib/oracle/11.2/client64/bin:$PATH
export ORACLE_HOME=/usr/lib/oracle/11.2/client64/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export TNS_ADMIN=$ORACLE_HOME

4.Create script "/usr/local/bin/query_busdb4mo.py" or "/usr/local/bin/query_busdb4mo.sh" to collect the data from database.

#!/usr/bin/python
#encoding:UTF-8
#Author:Eric.zhang
#Email:Eric.zhangtj@homecredit.cn
#Usage:query_busdb4mo.py[seller_code][state_code]
#Seller_codeistheuniquecodefortheseller
#state_codeisthestatusoftheorder,likethis:
#ID	STATE_CODE	STATE_NAME	DESCRIPTION
#1	INITIAL	INITIAL	Ordercreated

importcx_Oracle
importsys
importos
importdatetime

try:
	seller_code=str(sys.argv[1])
	state_code=str(sys.argv[2])
except	Exception:
	print"Usagequery_busdb4mo.py[seller_code][state_code]"
	exit()

#Inputseller_code

#Timeformatislikethis"10/24/201516:54:32"
time_now=str(datetime.datetime.now().strftime("%m/%d/%Y%H:%M:%S"))
time_pass=str(((datetime.datetime.now()-datetime.timedelta(minutes=20)).strftime("%m/%d/%Y%H:%M:%S")))

#Connecttodatabaseusingtns
con=cx_Oracle.connect("APP_MO_RO/XXXXXXXX@10.26.14.206:1521/cfcdb06")

#Openacursor
cursor=con.cursor()
cursor.execute("withtbas(selecto.id_seller,os.idasid_order_state,os.state_code,count(*)asorder_countfromapp_mo.t_ordero,app_mo.t_order_stateoswhereo.id_state=os.idando.c
date>=to_date(:v_time_pass,'MM/DD/YYYYHH24:MI:SS')ando.cdate<=to_date(:v_time_now,'MM/DD/YYYYHH24:MI:SS')groupbyos.id,o.id_seller)selecttb.order_countfromtb,owner_int.vh_hom_sellerswheretb.id_seller=s.IDands.seller_code=:v_seller_codeandid_order_state=:v_id_order_state",v_time_pass=time_pass,v_time_now=time_now,v_seller_code=seller_code,v_id_order_state=state_code)
result=cursor.fetchall()

foriinresult:
	printi[0]


#Closethecursorandtheconnection
cursor.close()
con.close()

#!/bin/bash
#Author:Eric.zhang
#Email:Eric.zhangtj@homecredit.cn
#Usage:query_busdb4mo.sh[seller_code][state_code]

source/etc/profile

#argvs
seller_code=$1
id_order_state=$2

#times
time_now=`date"+%m/%d/%Y%H:%M:%S"`
time_pass=`date-d"20minutesago""+%m/%d/%Y%H:%M:%S"`

#echo$time_now
#echo$time_pass
#echo$seller_code
#echo$id_order_state

result=`/usr/lib/oracle/11.2/client64/bin/sqlplus-silentAPP_MO_RO/XXXXXXXX@cfcdb06<<END
setpagesize0Feedbackoffverifyoffheadingoffechooff;
--setserveroutputon;
varv_seller_codevarchar2(10);
exec:v_seller_code:='$seller_code';
--execDBMS_OUTPUT.put_line(:v_seller_code);
varv_id_order_statevarchar2(10);
exec:v_id_order_state:='$id_order_state';
--execDBMS_OUTPUT.put_line(:v_id_order_state);
withtbas(selecto.id_seller,app_mo.t_order_stateoswhereo.id_state=os.idando.cdate>=to_date
('$time_pass','MM/DD/YYYYHH24:MI:SS')ando.cdate<=to_date('$time_now',owner_int.vh_hom_sellerswheretb.id_seller=s.IDands.seller_code=:v_seller_codeandid_order_state=:v_id_order_state;
exit;
END`

[[-z"$result"]]&&exit0

echo$result

The result is like this:

[root@zabbixzabbix_agentd.conf.d]#/usr/local/bin/query_busdb4mo.sh0625281
535
[root@zabbixzabbix_agentd.conf.d]#/usr/local/bin/query_busdb4mo.py0625281
538
[root@zabbixzabbix_agentd.conf.d]#

5. create bash to collect the data.

[root@zabbixzabbix]#cat$(pwd)/check_hcc_online.sh
#!/bin/bash

functionORDER_CREATED{
/usr/local/bin/query_busdb4mo.py0625281
}

functionELIGIBILITY_CHECK_PASSED{
/usr/local/bin/query_busdb4mo.py0625282
	}

functionELIGIBILITY_CHECK_Failed{
/usr/local/bin/query_busdb4mo.py0625283
	}

functionIDENTITY_CHECK_PASSED{
/usr/local/bin/query_busdb4mo.py0625284
	}

functionIDENTITY_CHECK_Failed{
/usr/local/bin/query_busdb4mo.py0625285
	}

functionCONTRACT_APPROVED{
/usr/local/bin/query_busdb4mo.py0625286
	}

functionNOTIFY_VENDOR_TO_DELIVER_GOODS{
/usr/local/bin/query_busdb4mo.py0625287
	}

functionVENDOR_Feed_BACK_THAT_GOODS_WAS_SHIPPED{
/usr/local/bin/query_busdb4mo.py0625288
	}

functionDISBURSEMENT_TO_VENDOR{
/usr/local/bin/query_busdb4mo.py0625289
	}

functionCUSTOMER_RECEIVED_THE_GOODS{
/usr/local/bin/query_busdb4mo.py06252810
	}

functionORDER_CANCELLED_BY_CUSTOMER{
/usr/local/bin/query_busdb4mo.py06252811
	}

functionCONTRACT_CANCELLED{
/usr/local/bin/query_busdb4mo.py06252812
	}

functionORDER_REJECTED{
/usr/local/bin/query_busdb4mo.py06252813
	}

functionSMS_CHECK_SUCCESS{
/usr/local/bin/query_busdb4mo.py06252814
	}

functionSMS_CHECK_Failed{
/usr/local/bin/query_busdb4mo.py06252815
	}

functionCONTRACT_CREATE_SUCCESS{
/usr/local/bin/query_busdb4mo.py06252816
	}

functionCONTRACT_CREATE_Failed{
/usr/local/bin/query_busdb4mo.py06252817
	}

functionNOTIFIED_SHIPMENT_TO_HOMER{
/usr/local/bin/query_busdb4mo.py06252818
	}

functionNOTIFIED_DELIVERY_TO_HOMER{
/usr/local/bin/query_busdb4mo.py06252819
	}

functionNOTIFIED_CANCELLATION_TO_HOMER{
/usr/local/bin/query_busdb4mo.py06252820
	}

functionNOTIFIED_GOODS_RETURN_TO_HOMER{
/usr/local/bin/query_busdb4mo.py06252821
	}

functionORDER_PARTIALLY_CANCELLED_BY_CUSTOMER{
/usr/local/bin/query_busdb4mo.py06252822
	}

functionVERIFICATION_CALL_FOR_CUSTOMER{
/usr/local/bin/query_busdb4mo.py06252823
	}

functionORDER_TERMINATE_BY_CUSTOMER_IN_HCC{
/usr/local/bin/query_busdb4mo.py06252824
	}

$1
[root@zabbixzabbix]#

6. create configure file for this custom define.

[root@zabbixzabbix_agentd.conf.d]#cat$(pwd)/hcc_online.conf
UserParameter=hcc.initial,/home/zabbix/check_hcc_online.shORDER_CREATED
UserParameter=hcc.eligible,/home/zabbix/check_hcc_online.shELIGIBILITY_CHECK_PASSED
UserParameter=hcc.not_eligible,/home/zabbix/check_hcc_online.shELIGIBILITY_CHECK_Failed
UserParameter=hcc.identity_valid,/home/zabbix/check_hcc_online.shIDENTITY_CHECK_PASSED
UserParameter=hcc.identity_invalid,/home/zabbix/check_hcc_online.shIDENTITY_CHECK_Failed
UserParameter=hcc.contract_approved,/home/zabbix/check_hcc_online.shCONTRACT_APPROVED
UserParameter=hcc.shipment_notified,/home/zabbix/check_hcc_online.shNOTIFY_VENDOR_TO_DELIVER_GOODS
UserParameter=hcc.shipped,/home/zabbix/check_hcc_online.shVENDOR_Feed_BACK_THAT_GOODS_WAS_SHIPPED
UserParameter=hcc.disbursed,/home/zabbix/check_hcc_online.shDISBURSEMENT_TO_VENDOR
UserParameter=hcc.delivered,/home/zabbix/check_hcc_online.shCUSTOMER_RECEIVED_THE_GOODS
UserParameter=hcc.cancelled,/home/zabbix/check_hcc_online.shORDER_CANCELLED_BY_CUSTOMER
UserParameter=hcc.contract_cancelled,/home/zabbix/check_hcc_online.shCONTRACT_CANCELLED
UserParameter=hcc.rejected,/home/zabbix/check_hcc_online.shORDER_REJECTED
UserParameter=hcc.sms_check_success,/home/zabbix/check_hcc_online.shSMS_CHECK_SUCCESS
UserParameter=hcc.sms_check_Failed,/home/zabbix/check_hcc_online.shSMS_CHECK_Failed
UserParameter=hcc.contract_create_success,/home/zabbix/check_hcc_online.shCONTRACT_CREATE_SUCCESS
UserParameter=hcc.contract_create_Failed,/home/zabbix/check_hcc_online.shCONTRACT_CREATE_Failed
UserParameter=hcc.shipment_notified_homer,/home/zabbix/check_hcc_online.shNOTIFIED_SHIPMENT_TO_HOMER
UserParameter=hcc.delivery_notified_homer,/home/zabbix/check_hcc_online.shNOTIFIED_DELIVERY_TO_HOMER
UserParameter=hcc.cancellation_notified_homer,/home/zabbix/check_hcc_online.shNOTIFIED_CANCELLATION_TO_HOMER
UserParameter=hcc.goods_return_notified_homer,/home/zabbix/check_hcc_online.shNOTIFIED_GOODS_RETURN_TO_HOMER
UserParameter=hcc.partially_cancelled,/home/zabbix/check_hcc_online.shORDER_PARTIALLY_CANCELLED_BY_CUSTOMER
UserParameter=hcc.verification_call,/home/zabbix/check_hcc_online.shVERIFICATION_CALL_FOR_CUSTOMER
UserParameter=hcc.terminate,/home/zabbix/check_hcc_online.shORDER_TERMINATE_BY_CUSTOMER_IN_HCC
[root@zabbixzabbix_agentd.conf.d]#

7. test with zabbix_get

[root@zabbix zabbix_agentd.conf.d]# zabbix_get -s 127.0.0.1 -p 10050 -k 'hcc.initial'

545

[root@zabbix zabbix_agentd.conf.d]#

8.define the custom graph on zabbix surface.

[Oracle]记一次由sequence引发的enq sv-contention等待事件

数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库等待会话暴增...

oracle统计信息的锁定与解锁

（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该返回100多万...

【转】RMAN删除过期备份或非过期备份

（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操作时，会在RM...

数据恢复顾问（DRA）

（1）DRA介绍数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能够修复数据文...

RMAN备份与恢复（一）--认识RMAN

RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（restore）和恢复...

[monitoring] how to create script to monitoring the status of orders and make graphs

相关文章