One afternoon received phone call from customer.They wanted to run a report on daily basis to look for obsolete records in database and have them mail out report for those obsolete records. This report was also going to be viewed by higher management and hence required better arrangement in email.
Thinking:
1 We all know mailx commmand by now, which will help us to send emails from unix server to get information/reports on regular basis when put in a script and scheduled via CRON.
2 Since this email report was going to be viewed by higher management, proper text layout and viewing arrangement was necessary. So this time I decided to add attachment to email along with the body Which is different than sending regular mail.
3 One major drawback I have seen since so many years is that, we always send email out via mailx command where recepient receives that email from "unix user" who is running mailx command. In most of database accounts user name is oracle, so we get sender as "oracle@'hostname.domain.com' ". If /etc/passwd file has owner as physical person, then we may see alias to above email sender as " 'account owner name' oracle@'hostname.domain.com' "
4 Also, when recepient do "reply to all", they will need editing in sender list, else it will bring "mailer daemon" to replyer of that email since unix host will not receive any emails.
5 Finally, If I am not around to reply in repsonse to any issues in that report, we needed my manager to be CC'ed so that, he can allocate resource to take care of any work.
6 Creating report was easy part of all. So finally decided to create a quick script before I leave home for the day.
Implementation/scripts/outputs :
1 created a regular shell script and scheduled via CRON
2 "-r" in mailx command is "reply to" option. If we provide "my_name" along with -r option then, it will be replied back to me.
3 Attachment to mailx command is simple. We need to pipe "body of email" to file then pipe that to uuencode command to make an attachment and than pipe it again to mailx
--Script details
LOGFILE=/home/oracle/1.log
DATA=/home/oracle/1.data
.
..
... {rest of script to spool data via sqlplus in to file named LOGFILE }
....
echo "Please find attached file.\n Thanks,\n Sandeep " >${DATA}|uuencode ${LOGFILE} Obsolete_data.txt|cat ${DATA} -|mailx -s "Obsolete data from ${ORACLE_SID} database" -c " 'my_manager'@domain.com, 'my_name'@domain.com" -r " 'my_name'@domain.com"
" 'client_1'@customer.domain.com,'client_2'@Customer.domain.com"
.....
4 In mailx command
mailx -s "Obsolete data from ${ORACLE_SID} database" -c " 'my_manager'@domain.com, 'my_name'@domain.com" -r "
-c will perform Carbon Copy to my manager
-r will "reply to" person described after that option
-s is subject of email
5 In uunecode command
uuencode ${LOGFILE} Obsolete_data.txt|cat ${DATA}
${LOGFILE} is file spooled by sqlplus which has all report of obsolete record. Remember to view this file in "wordpad" when received in email as an attachment to see proper format.
Followed by attachment is name you want to provide to attached file
${DATA} contains body of email.
Conclusion:
We have successfully created email from unix host with an attachment which not only provides security to sensitive data,but also improves readability of emails by keeping required information in attached files. Customer was very pleased to receive daily report with proper format along with management contacts from my side.
