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

The purpose is to make a graph like this.

Then, let's begin.

1. 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"

2. Install python_oracle support "cx_Oracle-5.1.2-11g-py26-1.x86_64.rpm"

3. 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: [e-mail]
# Usage: query_busdb4mo.py [seller_code] [state_code]
# Seller_code is the unique code for the seller
# state_code is the status of the order, like this:
# ID	STATE_CODE	STATE_NAME	DESCRIPTION
# 1	INITIAL	        INITIAL	        Order created

import cx_Oracle
import sys
import os
import datetime

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

#Input seller_code

#Time format is like this "10/24/2015 16: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")))

#Connect to database using tns
con = cx_Oracle.connect("APP_MO_RO/XXXXXXXX@10.26.14.206:1521/cfcdb06")

#Open a cursor
cursor = con.cursor()
cursor.execute("with tb as (select o.id_seller, os.id as id_order_state, os.state_code, count(*) as order_count from app_mo.t_order o, app_mo.t_order_state os where o.id_state = os.id and o.c
date >= to_date(:v_time_pass,'MM/DD/YYYY HH24:MI:SS') and o.cdate <= to_date(:v_time_now,'MM/DD/YYYY HH24:MI:SS') group by os.id, os.state_code, o.id_seller) select tb.order_count from tb, owner_int.vh_hom_seller s where tb.id_seller = s.ID and s.seller_code = :v_seller_code and id_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()

for i in result:
	print i[0]


#Close the cursor and the connection
cursor.close()
con.close()

#!/bin/bash
# Author: Eric.zhang
# Email: [e-mail]
# 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 "20 minutes ago" "+%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 -silent APP_MO_RO/XXXXXXXX@cfcdb06 << END
set pagesize 0 feedback off verify off heading off echo off;
--set serveroutput on;
var v_seller_code varchar2(10);
exec:v_seller_code:='$seller_code';
--exec DBMS_OUTPUT.put_line(:v_seller_code);
var v_id_order_state varchar2(10);
exec:v_id_order_state:='$id_order_state';
--exec DBMS_OUTPUT.put_line(:v_id_order_state);
with tb as (select o.id_seller, os.id as id_order_state, os.state_code, count(*) as order_count from app_mo.t_order o, app_mo.t_order_state os where o.id_state = os.id  and o.cdate >= to_date
('$time_pass','MM/DD/YYYY HH24:MI:SS') and o.cdate <= to_date('$time_now','MM/DD/YYYY HH24:MI:SS') group by os.id, os.state_code, o.id_seller)select tb.order_count from tb, owner_int.vh_hom_seller s where tb.id_seller = s.ID and s.seller_code = :v_seller_code and id_order_state = :v_id_order_state;
exit;
END`

[[ -z "$result" ]] && exit 0

echo $result

The result is like this:

[root@zabbix zabbix_agentd.conf.d]# /usr/local/bin/query_busdb4mo.sh 062528 1
535
[root@zabbix zabbix_agentd.conf.d]# /usr/local/bin/query_busdb4mo.py 062528 1
538
[root@zabbix zabbix_agentd.conf.d]#

5. create bash to collect the data.

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

function ORDER_CREATED {
/usr/local/bin/query_busdb4mo.py 062528 1
        }

function ELIGIBILITY_CHECK_PASSED {
/usr/local/bin/query_busdb4mo.py 062528 2
	}

function ELIGIBILITY_CHECK_FAILED {
/usr/local/bin/query_busdb4mo.py 062528 3
	}

function IDENTITY_CHECK_PASSED {
/usr/local/bin/query_busdb4mo.py 062528 4
	}

function IDENTITY_CHECK_FAILED {
/usr/local/bin/query_busdb4mo.py 062528 5
	}

function CONTRACT_APPROVED {
/usr/local/bin/query_busdb4mo.py 062528 6
	}

function NOTIFY_VENDOR_TO_DELIVER_GOODS {
/usr/local/bin/query_busdb4mo.py 062528 7
	}

function VENDOR_FEED_BACK_THAT_GOODS_WAS_SHIPPED {
/usr/local/bin/query_busdb4mo.py 062528 8
	}

function DISBURSEMENT_TO_VENDOR {
/usr/local/bin/query_busdb4mo.py 062528 9
	}

function CUSTOMER_RECEIVED_THE_GOODS {
/usr/local/bin/query_busdb4mo.py 062528 10
	}

function ORDER_CANCELLED_BY_CUSTOMER {
/usr/local/bin/query_busdb4mo.py 062528 11
	}

function CONTRACT_CANCELLED {
/usr/local/bin/query_busdb4mo.py 062528 12
	}

function ORDER_REJECTED {
/usr/local/bin/query_busdb4mo.py 062528 13
	}

function SMS_CHECK_SUCCESS {
/usr/local/bin/query_busdb4mo.py 062528 14
	}

function SMS_CHECK_FAILED {
/usr/local/bin/query_busdb4mo.py 062528 15
	}

function CONTRACT_CREATE_SUCCESS {
/usr/local/bin/query_busdb4mo.py 062528 16
	}

function CONTRACT_CREATE_FAILED {
/usr/local/bin/query_busdb4mo.py 062528 17
	}

function NOTIFIED_SHIPMENT_TO_HOMER {
/usr/local/bin/query_busdb4mo.py 062528 18
	}

function NOTIFIED_DELIVERY_TO_HOMER {
/usr/local/bin/query_busdb4mo.py 062528 19
	}

function NOTIFIED_CANCELLATION_TO_HOMER {
/usr/local/bin/query_busdb4mo.py 062528 20
	}

function NOTIFIED_GOODS_RETURN_TO_HOMER {
/usr/local/bin/query_busdb4mo.py 062528 21
	}

function ORDER_PARTIALLY_CANCELLED_BY_CUSTOMER {
/usr/local/bin/query_busdb4mo.py 062528 22
	}

function VERIFICATION_CALL_FOR_CUSTOMER {
/usr/local/bin/query_busdb4mo.py 062528 23
	}

function ORDER_TERMINATE_BY_CUSTOMER_IN_HCC {
/usr/local/bin/query_busdb4mo.py 062528 24
	}

$1
[root@zabbix zabbix]#

6. create configure file for this custom define.

[root@zabbix zabbix_agentd.conf.d]# cat $(pwd)/hcc_online.conf
UserParameter=hcc.initial,/home/zabbix/check_hcc_online.sh ORDER_CREATED
UserParameter=hcc.eligible,/home/zabbix/check_hcc_online.sh ELIGIBILITY_CHECK_PASSED
UserParameter=hcc.not_eligible,/home/zabbix/check_hcc_online.sh ELIGIBILITY_CHECK_FAILED
UserParameter=hcc.identity_valid,/home/zabbix/check_hcc_online.sh IDENTITY_CHECK_PASSED
UserParameter=hcc.identity_invalid,/home/zabbix/check_hcc_online.sh IDENTITY_CHECK_FAILED
UserParameter=hcc.contract_approved,/home/zabbix/check_hcc_online.sh CONTRACT_APPROVED
UserParameter=hcc.shipment_notified,/home/zabbix/check_hcc_online.sh NOTIFY_VENDOR_TO_DELIVER_GOODS
UserParameter=hcc.shipped,/home/zabbix/check_hcc_online.sh VENDOR_FEED_BACK_THAT_GOODS_WAS_SHIPPED
UserParameter=hcc.disbursed,/home/zabbix/check_hcc_online.sh DISBURSEMENT_TO_VENDOR
UserParameter=hcc.delivered,/home/zabbix/check_hcc_online.sh CUSTOMER_RECEIVED_THE_GOODS
UserParameter=hcc.cancelled,/home/zabbix/check_hcc_online.sh ORDER_CANCELLED_BY_CUSTOMER
UserParameter=hcc.contract_cancelled,/home/zabbix/check_hcc_online.sh CONTRACT_CANCELLED
UserParameter=hcc.rejected,/home/zabbix/check_hcc_online.sh ORDER_REJECTED
UserParameter=hcc.sms_check_success,/home/zabbix/check_hcc_online.sh SMS_CHECK_SUCCESS
UserParameter=hcc.sms_check_failed,/home/zabbix/check_hcc_online.sh SMS_CHECK_FAILED
UserParameter=hcc.contract_create_success,/home/zabbix/check_hcc_online.sh CONTRACT_CREATE_SUCCESS
UserParameter=hcc.contract_create_failed,/home/zabbix/check_hcc_online.sh CONTRACT_CREATE_FAILED
UserParameter=hcc.shipment_notified_homer,/home/zabbix/check_hcc_online.sh NOTIFIED_SHIPMENT_TO_HOMER
UserParameter=hcc.delivery_notified_homer,/home/zabbix/check_hcc_online.sh NOTIFIED_DELIVERY_TO_HOMER
UserParameter=hcc.cancellation_notified_homer,/home/zabbix/check_hcc_online.sh NOTIFIED_CANCELLATION_TO_HOMER
UserParameter=hcc.goods_return_notified_homer,/home/zabbix/check_hcc_online.sh NOTIFIED_GOODS_RETURN_TO_HOMER
UserParameter=hcc.partially_cancelled,/home/zabbix/check_hcc_online.sh ORDER_PARTIALLY_CANCELLED_BY_CUSTOMER
UserParameter=hcc.verification_call,/home/zabbix/check_hcc_online.sh VERIFICATION_CALL_FOR_CUSTOMER
UserParameter=hcc.terminate,/home/zabbix/check_hcc_online.sh ORDER_TERMINATE_BY_CUSTOMER_IN_HCC
[root@zabbix zabbix_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.