Solved

ksh script not working correctly

Posted on 2015-02-18
33
209 Views
Last Modified: 2015-02-19
I am trying to set up a script to monitor a couple of user accounts that keep getting locked

Here is my script

It runs but does not send an email when one of the user accounts
is locked

I know my email works because I have other scripts that run
and email results


Can you help Please

#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/11204
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH
export $DBA1='bev@mysystem.com"

tempfile=/tmp/user_locked.log
sqlplus -S scott/tiger <<EOF >$tempfile
 set head off feed off verify off
select 'This account is LOCKED' from dual;

define exit_status = 0
col xs new_value exit_status

select username, account_status, 1 as xs from dba_users
where account_status = 'LOCKED'
and username in ('SCOTT','TEST_USER');
exit &exist_status
EOF
if [ $? = 0 ]
then
mailx -s "Account LOCK Check" $DBA1 < $tempfile
fi

In the $tempfile is

This Account is LOCKED

TEST_USER LOCKED
0
Comment
Question by:bkreynolds48
  • 13
  • 12
  • 8
33 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617118
I hope that

exit &exist_status

is a typo??

If it is, add just after EOF

echo $?

to check whether the exit code is set as desired.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617205
yes that was a typo -- sorry
the echo statement came back as

echo 0
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617231
with set -x I see at the if statement

if [ 0 = 0 ]
 then
mailx -s "Account LOCK Check" $DBA1 < $tempfile
 fi

HOWEVER no mail is sent

if I run the mail statement by itself it sends it
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617258
With some ksh implementations numeric comparisons must be done with "-eq" and string comparisons require quotes.

So try

if [ $? -eq 0 ]

or

if [ $? = "0" ]

If still no luck retry with "set -xv" instead of "set -x" to get more debugging info.
Does "mailx" get executed or deosn't it?
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617321
tried both -eq & = "0"
both return 0

neither executes the mail statement - I can execute the mail statement from the command line and it does mail the file
ALSO
I put after

if [ $? = 0 ]
 then
echo "this is a test >$tempfile
 mailx -s "Account LOCK Check" $DBA1 < $tempfile
 fi


the echo statement is not in $tempfile

so it looks like the if / then statement is not working
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617391
I hope the missing closing quote is a typo, too?

echo "this is a test" >$tempfile
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617393
yes I have quotes there
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617409
And the "echo $?" statement you added just after the EOF line echoed "0", is that correct?

EDIT: Sorry, nonsense removed.

Did you try "set -xv"?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40617437
I have almost always had a problem with SQL*Plus and return codes.  Sometimes they work and sometimes they don't.  My preference is to do it an entirely different way.  Ignore the return code from SQL*Plus and just look at the contents of $tempfile.  You can do something like this:

cnt=`grep LOCKED $tempfile | wc -l`
if [ $cnt -gt 1 ] ; then
   mailx....
fi
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617441
The above would have been my next suggestion ...

No "wc" needed, "grep -c LOCKED $tempfile" is sufficient.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40617484
The wc is required because there will always be 1 occurrence of LOCKED in the file.  The first select from DUAL will always return a row into the file.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617486
... and grep -c will return the number of matches 1, 2, ...

"man grep" for more.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617492
tried both of your examples - still no email
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40617494
OK.  That didn't used to be an option.  So, I learned something today.  I guess that dates me.  I never went back looking for a new option that did that.  It certainly makes sense that it is there.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40617495
What are the contents of $tmpfile and what is the count that is being stored in $cnt?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617506
I have an old Debian system at hand where the "grep" executable dates back to 2008-08-31 - and it supports "-c".
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 34

Expert Comment

by:johnsone
ID: 40617514
I go back a lot farther than that.  Think AT&T SVR4.  That was a new release when I was starting out.  It has always been an option in the GNU version that I am aware of.  I just never knew it made it into the mainstream versions.  I never looked because I had another way to do it.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617600
EOF
echo 0
cnt=+ grep -c LOCKED /tmp/user_lock.log
2
if [ gt 1 ] ;then

in the log file there is this

This account is LOCKED

SCOTT     LOCKED

thanks for your help
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617605
Is the above the result of

cnt=$(grep -c LOCKED /tmp/user_lock.log)
if [ $cnt gt 1 ] ;then
...
?

$(  ) is equivalent to   ` `
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40617608
The if statement looks incorrect.  Where is $cnt?  You may have misspelled the name of the variable.  Can you post a full copy of the script now.  With the full contents of the output file as well as a full copy of the screen output.

That will help people so we can see the whole thing.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617697
this is the file changed as directed about to use cnt

#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/11204
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH
export $DBA1='bev@mysystem.com"

tempfile=/tmp/user_locked.log
sqlplus -S scott/tiger <<EOF >$tempfile
 set head off feed off verify off
select 'This account is LOCKED' from dual;

define exit_status = 0
col xs new_value exit_status

select username, account_status, 1 as xs
from dba_users
where account_status = 'LOCKED'
and username in ('SCOTT','TEST_USER');
exit &exit_status
EOF
echo $?
#
cnt=`grep -c LOCKED $tempfile`
if [ $cnt -gt 1 ];then
mailx -s "Account LOCK Check" $DBA1 < $tempfile
fi
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40617723
What about the output and contents of the tempfile?

I cut it down to the simplest form and it works for me.
#!/bin/ksh
tempfile=/tmp/user_locked.log
sqlplus -S scott/tiger <<EOF >$tempfile
 set head off feed off verify off
select 'This account is LOCKED' from dual;
select username, account_status, 1 as xs 
from dba_users
where account_status = 'LOCKED';
exit
EOF

cnt=`grep -c LOCKED $tempfile`
if [ $cnt -gt 1 ];then
echo "sending mail"
#mailx -s "Account LOCK Check" $DBA1 < $tempfile
fi

Open in new window

The only difference is I removed the environment settings and the user list in the select statement.

Are you sure the mailx is working correctly?  Add the statement inside the if statement to see if it at least gets inside the if.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617791
I am sure the mailx statement is working because I can copy/paste it to the command line and it works
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617891
It's strange enough, but it seems there is a problem with comparisons.

You say that things work from command line, so could you please perform these little tests from there:

if [ 1 -eq 1 ]; then echo YES; fi
if [ 1 -eq 2 ]; then echo YES; fi

What's on the terminal?

if [ 1 -eq 1 ]; then mailx -s "test" bev@mysystem.com < /etc/filesystems; fi

Does the message arrive?

 By the way, i hope that the non-matching quotes in

export $DBA1='bev@mysystem.com"

are also due to a typo??
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40617929
yes this was a typo
By the way, i hope that the non-matching quotes in

 export $DBA1='bev@mysystem.com"

if I do this on the command line I get the email
mailx -s "test" bev@mysystem.com < /etc/filesystems

this didn't do anything

if [ 1 -eq 1 ]; then echo YES; fi
if [ 1 -eq 2 ]; then echo YES; fi

 What's on the terminal? - nothing on the terminal

 if [ 1 -eq 1 ]; then mailx -s "test" bev@mysystem.com < /etc/filesystems; fi

no email
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617938
This

if [ 1 -eq 1 ]; then echo YES; fi

must return "YES"! If it doesn't then there is something perfectly broken!

Try

[ 1 -eq 1 ] && echo YES

This must also return "YES". If it does then there is a problem with "if", if it doesn't then we have a problem with the square bracket.

Run

which if
alias if

Both should result in "not found" messages.

Likewise:

which [
alias [

should return error messages, and

which ]
alias ]

should return "not found" messages.

Are you actually using the Korn shell?

Try

echo $0

What do you see? What do you see when pressing <Ctrl><V> ?

And what is your OS?

Which way do you access the Unix box? PuTTY?
Which editor do you use? vi?

NB: Even if those "which" and "alias" checks above would return non-error output it wouldn't really matter, because shell builtins always take precedence. I suggested them just for completeness.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40617959
My day is over, sorry. CU tomorrow!
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40618639
Also, spacing is important in the test commands.  Be sure that there is a space (more than one is fine, but there has to be at least one) between the brackets and anything else.  I see that what is being posted has the spaces, just want to be sure that you are getting them in all the commands.
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40618810
I put your commands in it's own script and ran

#
#!/bin/ksh
#
set -xv
export $DBA1='bev@mysystem.com'
export LOG='/tmp/log.txt'
#
if [ 1 -eq 1 ]; then echo YES > $LOG; fi
if [ 1 -eq 2 ]; then echo YES > $LOG; fi

the log file had YES

then I put this in the file

#
#!/bin/ksh
#
set -xv
export $DBA1='bev@mysystem.com'
export LOG='/tmp/log.txt'
#
if [ 1 -eq 1 ]; then echo YES > $LOG;
 then
mailx -s "test" $DBA1 <$LOG
fi
if [ 1 -eq 2 ]; then echo NO > $LOG; fi

YES was in the log and I received the email with "YES" in it
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 40618831
This is wrong syntax:

if [ 1 -eq 1 ]; then echo YES > $LOG;
then
mailx -s "test" $DBA1 <$LOG
fi

Besides that, comparisons seem to work. What's different to the original script? A different editor?
Has the script been created on another system, maybe Windows, and then transferred from there?
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 40618942
using the same system - same user - same file system for each of these tests
AIX 6.1 -  KSH vi is the editor
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 40619051
So it could be that we have an issue with the here document.

Please make sure that there is no space/tab or any other invisible character after EOF.

Best delete the whole line containing EOF with <dd>, add an empty line with <O> and type just EOF followed by a newline (<ENTER>) and nothing else.
0
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 40619100
That was the problem.  I did a "set list" and saw a space after EOF - I deleted the line and re-added EOF with no space.
Thank you so much for your time and patience
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now