Скрипт используется для рассылки информации по проблемным направлениям, на которых трафик есть, но почему-то звонки не проходят (ACD<1)
#!/bin/sh #скрипт мониторинга направлений DATABASE=ambs USER=dbuser PASS=password VHOUR=`date +%H |sed 's/^0//i'` YEARANDMONTH=`date +%Y%m` echo -e "From: [email protected] To: [email protected] Cc: [email protected] Cc: [email protected] Subject: сабж кириллицей в UTF (можно получить отправив самому себе письмо и открыть его исходник) Mime-Version: 1.0 Content-Type: text/html;charset='UTF-8' <html><body>" mysql -u$USER -p$PASS $DATABASE -B -H -e "SELECT so.SITENAME as ORIG_OPER, st.SITENAME as TERM_OPER, dst.DEST_NAME, SUM(U.ALL_CALLS) as ALL_CALLS, IFNULL(ROUND(sum(U.ALL_MIN)/sum(U.COMPL_CALLS)/60,2),0) AS ACD, sum(U.SUCC_CALLS) as SUCC_CALLS, sum(U.COMPL_CALLS) as COMPL_CALLS, ROUND(sum(U.ALL_MIN)/60,2) as ALL_MIN FROM ( SELECT CB.DST_SITE as TERM_OPER_ID, CB.SRC_SITE as ORIG_OPER_ID, CB.DST_DEST as TERM_DEST_ID, C.ELAPSED_TIME as ALL_MIN, 1 as ALL_CALLS, CB.SUCC_FLAG as SUCC_CALLS, (C.ELAPSED_TIME>0) as COMPL_CALLS FROM CDR_BILL"$YEARANDMONTH" CB join CDR C on C.CID=CB.CID WHERE CB.BILL_DATE=DATE(SUBTIME(NOW(),'1:0:0')) and CB.VHOUR=HOUR(SUBTIME(NOW(),'1:0:0')) and CB.TBLTYPE=1 UNION ALL SELECT CB.DST_SITE as TERM_OPER_ID, CB.SRC_SITE as ORIG_OPER_ID, CB.DST_DEST as TERM_DEST_ID, C.ELAPSED_TIME as ALL_MIN, 1 as ALL_CALLS, CB.SUCC_FLAG as SUCC_CALLS, (C.ELAPSED_TIME>0) as COMPL_CALLS FROM CDR_BILL"$YEARANDMONTH" CB join CDRzero"$YEARANDMONTH" C on C.CID=CB.CID WHERE CB.BILL_DATE=DATE(SUBTIME(NOW(),'1:0:0')) and CB.VHOUR=HOUR(SUBTIME(NOW(),'1:0:0')) and CB.TBLTYPE=0 ) U JOIN SITE so on so.ID=U.ORIG_OPER_ID JOIN SITE st on st.ID=U.TERM_OPER_ID join DEST dst on dst.ID=U.TERM_DEST_ID GROUP BY U.TERM_OPER_ID,U.ORIG_OPER_ID,U.TERM_DEST_ID HAVING ALL_CALLS>200 and ACD<'1.00' order by 4 desc ,1,2;" echo "</body></html>"
В кроне это записано как-то так:
00 6,9,13 * * 1,2,3,4,5 root /usr/local/bin/routestat.sh | /usr/sbin/sendmail -t 30 7,10,11,14 * * 1,2,3,4,5 root /usr/local/bin/routestat.sh | /usr/sbin/sendmail -t