I like to have daily monitoring scripts running from crontab and sending me emails. When done in its most simplist….
sqlplus –s / as sysdba <<EOF > alogfile.txt
select * from jobs_report_table;
quit
EOF
mailx –s ‘Daily Monitoring’ myemail@abc.com < alogfile.txt
it has the disadvantage of being readable only in the default font of the email client. This means that columns rarely, if ever, line up. Added to this, the output looks plain and boring.
Answer, set the font and add some colour.
I have gone to great lengths in the past on this, producing complex HTML output, consisting of boxes, tables, etc. etc. But the basic idea is the same.
1. Create variables containing the formatting commands. One of starting and one for ending formatting.
eg. hilight_on='<span style="color: red; font-size: 8pt; font_weight: bold; ">'
hilight_off='</span>'
2. Perform all sql (as above) from the Unix script. Include the variables ${hiligh_on} ${highlight_off} before and after data or columns you wish to format.
3. Email the resulting file as follows.
( echo "Subject: Daily Checks"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo "Content-Disposition: inline"
echo '<HTML><BODY><PRE>'
cat alogfile.txt
echo '</PRE></BODY></HTML>'
) | /usr/sbin/sendmail myemail@abc.com
The email (3) sets the main text into <PRE> which has a default of courier. This allows the columns to be more or less straight. In the example script below the PRE has been replaced and the text falls into the main BODY. In this case the font should be set manually.
There’s nothing to stop you putting the HTML span commands (or whatever) directly into you SQL statements but I prefer the variable approach. It keeps the thing neat and tidy.
Just can also put <TABLE> commands in to control the font, size, colours (background/foreground) etc. etc.
Here is the final example that emails a list of tablespaces in table format, with a horrible green background.
Points to note are
1. The prompt statements before and after the select to create the table, and end it.
2. The colsep which provides the horizontal tabbing
3. The addition column that ends and restarted the next table row. It also has the same as a column name to do the same to the headers.
#!/bin/sh
. $HOME/.profile 2>/dev/null
tmpfile=tbspace_`date +%d%b%Y`.out
sqlplus -s / as sysdba <<EOF
set pages 100
set colsep '</TD><TD>'
set und off
set feedback off
spool ${tmpfile}
column "File Count" format 999999
column "Size(MB)" format 999999999.99
column "Free(MB)" format 999999999.99
column "Used(MB)" format 999999999.99
column "Max Ext(MB)" format 999999999.99
column "% Free" format 999.99
column "% Free Ext" format 999.99
column "Graph" format a11
column tablespace_name format a20
prompt <TABLE border="1" bgcolor="#00FF00"><TR><TD>
set lin 400
SELECT ts.tablespace_name,
"File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free",
RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph" ,
'</TD></TR><TR><TD>' "</TD></TR><TR><TD>"
FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*) "File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free";
prompt </TR></TABLE>
spool off
exit
EOF
(
echo "Subject: Database info $ORACLE_SID from cron job"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo "Content-Disposition: inline"
echo '<HTML><BODY>'
cat ${tmpfile}
echo '</BODY></HTML>'
) | /usr/sbin/sendmail myemail@abc.com
echo Done
Happyjohn
No comments:
Post a Comment