Code Example: ksh, sqlplus, scp, ftp, zip

==============================
General
==============================

Code example.

ksh script reading from ini file.

for each ini entry, do scp to IP destination, listed in ini file.

for specific IPs, do ftp.

call sqlplus, and generate .csv file.

zip the generated file.

==================================================================
/my_project/ftm/ftm_counter_transfer.sh
==================================================================

#!/bin/bash -x

(
run_date=`date +"%d%m%Y" --date="1 days ago"`
log=/my_project/ftm/log.log

### bring SPU counters ###

num_spu=10
ini=/my_project/ftm/server_list.ini
ip=`cat $ini |awk '{print$3}'|grep -v SERVICE`
run_date=`date +"%d%m%Y" --date="1 days ago"`
work=/my_project/ftm/tmp

for i in $ip
        do
                scp $i:/my_project/igate/*/DSI/logs/MSU_License*${run_date}*.csv $work
        done

FTPHOST='111.222.333.444'
USR='my_user'
PASS='my_pass'
ftp -n -v $FTPHOST <<EOF
user $USR $PASS
prompt
binary
lcd $work
cd /my_project/igate/my_customer/DSI/logs/
mget MSU_License*${run_date}*.csv
bye
EOF

FTPHOST='111.222.333.555'
USR='my_user'
PASS='my_pass'
ftp -n -v $FTPHOST <<EOF
user $USR $PASS
prompt
binary
lcd $work
cd /my_project/igate/my_customer/DSI/logs/
mget MSU_License*${run_date}*.csv
bye
EOF


. /etc/sh/orash/oracle_login.sh orainst
. /my_project/my_user/.set_profile

cd /my_project/ftm/
/software/oracle/112/bin/sqlplus DB_USER/DB_PASS@orainst @/my_project/ftm/statistics.sql
mv /my_project/ftm/tmp/app_counters.csv /my_project/ftm/tmp/app_counters_${run_date}.csv
cp /my_project/ftm/tmp/app_counters_${run_date}.csv /my_project/ftm/archive/

cd $work
zip -m ${run_date}.zip *${run_date}*.csv

) | tee -a /my_project/ftm/log_${run_date}.log

==================================================================
/my_project/ftm/server_list.ini
==================================================================
SERVICE_IP      GATE_NAME       MANAGMENT_IP    SPU_ID
11.33.222.4     xxx-mpu-1-man   11.33.222.12 spu_id#1
11.33.222.5     xxx-mpu-2-man   11.33.222.13 spu_id#2
11.33.222.28    yyy-mpu-1-man   11.33.222.36 spu_id#3
11.33.222.29    yyy-mpu-2-man   11.33.222.37 spu_id#4
11.33.222.52    zzz-mpu-1-man   11.33.222.60 spu_id#5
11.33.222.53    zzz-mpu-2-man   11.33.222.61 spu_id#6
11.33.222.76    qqq-mpu-1-man   11.33.222.84 spu_id#7
11.33.222.77    qqq-mpu-2-man   11.33.222.85 spu_id#8

==================================================================
/my_project/my_user/.set_profile
==================================================================
export ORASH=/etc/sh/orash
export BAS_ORACLE_LIST=orainst
export ORA_VER=1120
export ORACLE_SID=orainst
export ORACLE_BASE=/software/oracle
export ORACLE_ENV_DEFINED=yes
export ORA_NLS33=/software/oracle/112/ocommon/nls/admin/data
export ORACLE_HOME=/software/oracle/112
export ORA_EXP=/backup/ora_exp

==================================================================
/my_project/ftm/statistics.sql
==================================================================
spool /my_project/ftm/tmp/app_counters.csv
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 100
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
select to_char(ts_last_modified,'dd-mm-yyyy hh24:mi:ss')|| ',' ||
case STATIC_ID
when 102050101000000 then 'MPIA_total_LU'
when 102050103000000 then 'MPIA_total_CANCEL'
when 1011500104190000 then 'app_NumberOf_SMS_Messages_Success'
when 102080104000000 then 'smm_adaptors_LDAP_Requests'
when 102080101000000 then 'smm_adaptors_LDAP_Succesful_Respond'
end || ',' || sum(counter_delta)
from GA_W_COUNTERS_HISTORY
where STATIC_ID in(102050101000000,102050103000000,1011500104190000,102080104000000,102080101000000)
and to_char(ts_last_modified,'dd-mm-yyyy') = to_char(sysdate-1,'dd-mm-yyyy')
group by to_char(ts_last_modified,'dd-mm-yyyy hh24:mi:ss'),STATIC_ID
order by to_char(ts_last_modified,'dd-mm-yyyy hh24:mi:ss'),STATIC_ID;
spool off
exit