Shell Script to Check for Errors

[intp]

Hi,

We use sqlloader utility to load tables. As part of this command , we have mentioned
.LOG and .BAD files. So it stores rejected records in .BAD file and have respective information in LOG file. All these days we are checking the log files and making sure process ran fine.

We are now introducing new process which has more than 25-30 tables loads , which inturn would create 30 log files.

Can someone let me know how to write a generic shell script which could read all .LOG files (will be stored in seperate directory) and if needed read .BAD files(will be stored in seperate directory,if there are any rejections) and report what are the tables / process has problems ?

PLS HELP URGENT.

Thanks

[John Dvorchak]

It would help if you could post a piece of each log and bad file so we can see the syntax. A simple grep could parse the logs for errors and email the results to you.

root> grep error /log/\*/.bad |mailx root
root> grep whatever /log/\*.log | mailx root

or if you want to run a script from cron use this:

#!/sbin/sh

grep error /log/\*.bad
grep whatever /log/\*.log

end

Then what ever is the output will be sent to standard out. If run from a cron job all standard out it mailed to the owner of the cron job.

[intp]

Ok. attached the sample of .LOG file.

I'll be looking in the following area in the
file.

Table "ORAADMIN"."PRODUCT":

Line1 : 1716 Rows successfully loaded.

Line 2: 0 Rows not loaded due to data errors.

Line3: 0 Rows not loaded because all WHEN clauses were failed.

Line4: 0 Rows not loaded because all fields were null.

I need to run a shell script which needs to
open all 30 .LOG files one by one (probably it needs to get all .LOG filename from the directory i'll specify) and scan thru these lines and if there are non-zero rows in Line2 or 3 or 4 ...then it has to show the file name. I dont want an email. Just display the log file names in the screen should be fine.

Hope its clear.

Thanks

[Antonio Cardoso_1]

Hi,

this simple command line
grep -E 'skipped:|rejected:|discarded:' *.LOG | grep -v -e ": *0"

should show you all lines which contain non zero values for lines
Total logical records skipped: 10
Total logical records read: 1716
Total logical records rejected: 0
Total logical records discarded: 5

=> maybe enough...

[Rodney Hills]

How about something like-

cd /dir/with/logfiles
for f in *.LOG ; do
$n=`grep "0 Rows"` $f
if [[ $n != "3" ]] ; then
echo "$f had errors..."
cat ${f%%.LOG}.BAD
fi
done

HTH

-- Rod Hills

[intp]

grep -E 'skipped:|rejected:|discarded:' *.LOG | grep -v -e ": *0"

gives me the following error

grep: illegal option -- E
Usage: grep -hblcnsviw pattern file . . .

*********************************************

and for ..


cd /dir/with/logfiles
for f in *.LOG ; do
$n=`grep "0 Rows"` $f
if [[ $n != "3" ]] ; then
echo "$f had errors..."
cat ${f%%.LOG}.BAD
fi
done


i guess even if there are 10/20 (or any round numbers) Rows not loaded due to data errors. still it will say count 3 right ?

i mean

1716 Rows successfully loaded.
10 Rows not loaded due to data errors.
50 Rows not loaded because all WHEN clauses were failed.
30 Rows not loaded because all fields were null.

"0 Rows" = 3

[Rodney Hills]

You are right. Change the line to-

n=`grep "^0 Rows"` $f

Rod Hills

[intp]

ok changed to...
$n=`grep "^0 Rows"` $f

but in log file those lines start wil two blank spaces.. so grep "^0 Rows" *.log
doesnt return anything ...si i changed to
grep " 0 Rows" *.log and it works ...but is this good way to do it ?

and when i plugin this code
$n=`grep " 0 Rows" $f`
in a shell script i get the following error msg...

myerrtest.sh[6]: =: not found

contents of my shell script
---------------------------

#! /bin/ksh

for f in *.log
do
echo $f
$n=`grep " 0 Rows not" $f`
echo $n
done

[Rodney Hills]

I guess you didn't notice in my second post that I removed the $ from the "n=...". It's a perl habit :-)

Rod Hills

[intp]

and if i change it to

$n=`grep " 0 Rows not"` $f

as in your initial code...

it goes to infinite loop and nothing happens ..i had to cancel and stop the process.

PLS HELP

[Rodney Hills]

Other syntax mistake I made. The $f needs to be in the back apostrophes.

n=`grep "^0 Rows" $f`

Rod Hills

[intp]

yes i changed it..

n=`grep " 0 Rows not" $f`

grep returns all the matching records and not a numeric value. so
if [[ $n != "3" ]] ; then

condition fails.

[Rodney Hills · Accepted solution]

Sorry again, I guess in my old age I'm forgetting the necessary options. We need to add "-c" to grep so it returns a count of matches.

n=`grep -c " 0 Rows not" $f`

Maybe I got it right this time.... :-)

Rod Hills

[intp]

worked this time :)

Thanks ALL