Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script need to get fixed

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script need to get fixed
# 1  
Old 12-16-2010
Network Script need to get fixed
I need immediate help with this code. I do want to through the output of query into the log file and then on behelf of result i do want to automate an email warning system. while compiling this code i failed to through the output into the log file.



To avoid manual password change for our users we need to automate process of password expiry. We have to inform and ask user to change the password before the password get expire.

Action Points:

· Apply logic in Linux script based on output of expiry query to fetch username and\ no. Of days to expire. Follow suggestion to start with.
Mail will be send to each user whose password will expire in 15 days with Message 'Please change your password from web application as it will expire in x no. of days'.

Code:
#!/usr/bin/ksh
. ${HOME}/.profile
. /data01/dsproject/vdw/include/emailscript.ksh
INC001_SET_ENV admin
SCRIPTNAME=Email.ksh
TOOL=bteq
LOGFILE=${LOGDIR}/${SCRIPTNAME%.*}.log
echo "${DATE} ${TIME} START - \"${SCRIPTNAME}\"\n"      > ${LOGFILE}
${TOOL} <<- EOF >> ${LOGFILE} 2>&1
.RUN FILE=${BASEDIR}/.dbadmin
-- Show temp tables from DBC.Tables
-Temp Table
 
SELECT 
                     a.DatabaseName,
            
                     ((100 * ((4 * NULLIFZERO(a.PasswordChgDate) - 1) / 146097)
                     + (4 * (((4 * a.PasswordChgDate - 1) MOD 146097) / 4)
                     + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * a.PasswordChgDate
                     - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
                     / 12) * 10000 + (((5 * (((4 * (((4 * a.PasswordChgDate - 1)
                     MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
                     + 1) * 100 + ((5 * (((4 * (((4 * a.PasswordChgDate - 1) MOD
                     146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
                     (DATE, FORMAT 'yy/mm/dd', NAMED PasswdChgDate),
        
            b.expirepassword ,
            Passwdchgdate + COALESCE(b.expirepassword,90) AS "NextChgDate",
           (NextChgDate-DATE) AS NUM_of_Days_Left,
            COALESCE(a.Profilename,'NOT ASSIGNED') AS Profilename
  FROM dbc.DBASE a LEFT JOIN dbc.profileinfo b ON a.profilename = b.profilename
  WHERE  COALESCE(a.commentstring,'NOCMT') <> 'Not Used'
  AND a.Passwordchgdate >= 0
  AND NextChgDate BETWEEN DATE AN
 
 
 
.IF ERRORCODE != 0 THEN .EXIT ERRORCODE
.LOGOFF
.EXIT 0
EOF
i= ${LOGFILE}| wc -l
For i <> 0
User[i]  = awk '{print $1}' ${LOGFILE} | grep –n $i
Expiredays[i] = awk '{print $2}' ${LOGFILE} | grep –n $i
Mailaddress = ${User[i]}”@td.com”
i--
RC=$?
if [ "${RC}" != "0" ] ; then
  echo "Error: ${SCRIPTNAME} exited with RC=${RC}" > ${MAIL_HEADER}
for I <> 0 
mail -s ${SCRIPTNAME} -a ${LOGFILE} ${Mailaddress[i]} <  ‘Dear ’${user[i] “your password is about to expire in ” ${ExpiredDays[i]} “days” 
  exit 1
else
  exit 0
fi
 
echo "\n"                                               >> ${LOGFILE}
echo "${DATE} ${TIME} STOP  - \"${SCRIPTNAME}"\"        >> ${LOGFILE}

Moderator's Comments:
Mod Comment Use code tags please
Last edited by pludi; 12-16-2010 at 05:01 AM..
# 2  
Old 12-16-2010
Within in Oracle you can do the following:

Code:
 
column username, format a20 head 'User'
column Now format a20 head "Now"
column Expire_Date format a20 head "Expire Date"
column Days format 9999 head "Days"
column Hrs format 99 head "Hrs"
column Min format 99 head "Mins"
column Sec format 99 head "Secs"
SELECT username, TO_CHAR(sysdate,'YYYY.MM.DD-HH24:MI:SS') "Now",
TO_CHAR(expiry_date,'YYYY.MM.DD-HH24:MI:SS') "Expire_Date",
trunc((((86400*(expiry_date-sysdate))/60)/60)/24) "Days",
trunc(((86400*(expiry_date-sysdate))/60)/60)-24*(trunc((((86400*(expiry_date-sysdate))/60)/60)/24)) "Hrs",
trunc((86400*(expiry_date-sysdate))/60)-60*(trunc(((86400*(expiry_date-sysdate))/60)/60)) "Min",
trunc(86400*(expiry_date-sysdate))-60*(trunc((86400*(expiry_date-sysdate))/60)) "Sec"
FROM dba_users where expiry_date is not null and expiry_date > sysdate;

 
Sample output:
 
User       Now                      Expire Date               Days Hrs Mins Secs
------- -------------------- --------------------  ----- --- ---- ----
XXX     2010.12.16-14:57:47  2011.03.14-12:54:22     87  21   56   35
YYY     2010.12.16-14:57:47  2011.02.03-16:42:15     49   1   44   28

Set heading off and spool to a file.

Than you can read the file, get the number of Days until expiration
test if it is less than or equal to your threshold and than send mail.

Code:
exec 9<full path of results from oracle
while read -u9 dataline 
do
         expire_days=$(echo $dataline | awk -F' '  ' { print $4 } ')
         if [ $expire_days -le $threshold ]
         then
               user=$(echo $dataline | awk -F' '  ' { print $1 } ')
               mailx command with information
         fi 
done
exec 9<&-

To do this in the shell would be a pain as that is why you probbably
posted your question
This User Gave Thanks to BeefStu For This Post:
asifrafiqe
# 3  
Old 12-21-2010
If it's Oracle, you can also create a stored proc using UTL_SMTP and send the emails from within Oracle. If you're interested, you can google it and find many examples of how to set it up.
This User Gave Thanks to jsmithstl For This Post:
asifrafiqe
# 4  
Old 12-21-2010
i am thankful to all of my friends here is the script whicih ii finally figure out and it worked for me
Code:
#!/usr/bin/ksh
. ${HOME}/.profile
. /data01/dsproject/vdw/include/include_001.ksh
INC001_SET_ENV admin
SCRIPTNAME=pwd_expire.ksh
TOOL=bteq
LOGFILE=${LOGDIR}/${SCRIPTNAME%.*}.log
echo "${DATE} ${TIME} START - \"${SCRIPTNAME}\"\n"      > ${LOGFILE}
##########################################################################
#
##########################################################################
DO_PWD_CHECK()
{
#echo $*
while [ "$#" -gt "0" ] ; do
  APPL=$1
  ITAM_MAIL=$2
  rm -rf ${FILEDIR}/${APPL}_pwd_expire.log
  ${TOOL} <<- EOF >> ${LOGFILE} 2>&1
  .RUN FILE=${BASEDIR}/.dbadmin
  .SET ERROROUT STDOUT
  .EXPORT REPORT FILE=${FILEDIR}/${APPL}_pwd_expire.log
  .SET WIDTH 200
  SET SESSION DATEFORM=ANSIDATE;
 
SELECT 
   a.DatabaseName,
            
   ((100 * ((4 * NULLIFZERO(a.PasswordChgDate) - 1) / 146097)
   + (4 * (((4 * a.PasswordChgDate - 1) MOD 146097) / 4)
   + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * a.PasswordChgDate
   - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
   / 12) * 10000 + (((5 * (((4 * (((4 * a.PasswordChgDate - 1)
   MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
   + 1) * 100 + ((5 * (((4 * (((4 * a.PasswordChgDate - 1) MOD
   146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
   (DATE, FORMAT 'yy/mm/dd', NAMED PasswdChgDate),
  
            b.expirepassword ,
            Passwdchgdate + COALESCE(b.expirepassword,90) AS "NextChgDate",
            COALESCE(a.Profilename,'NOT ASSIGNED') AS Profilename,
            a.commentstring
  FROM dbc.DBASE a LEFT JOIN dbc.profileinfo b ON a.profilename = b.profilename
  WHERE  COALESCE(a.commentstring,'NOCMT') <> 'Not Used'
  AND a.Passwordchgdate >= 0
  --AND  ( DATE + 30 > NextChgDate )
  AND NextChgDate BETWEEN DATE AND DATE + 30
  ORDER BY 5,1;
 
SELECT 
   a.DatabaseName,            
   'User need to change initial password' as Reason,
            b.expirepassword ,
            -- a.Passwordchgdate + COALESCE(b.expirepassword,90) AS "NextChgDate",
            COALESCE(a.Profilename,'NOT ASSIGNED') AS Profilename,
            a.commentstring
  FROM dbc.DBASE a LEFT JOIN dbc.profileinfo b ON a.profilename = b.profilename
  WHERE  COALESCE(a.commentstring,'NOCMT') <> 'Not Used'
  AND a.Passwordchgdate < 0
  --AND  ( DATE + 30 > NextChgDate )
  ORDER BY 5,1;
 
  .IF ERRORCODE != 0 THEN .EXIT ERRORCODE
  .LOGOFF
  .EXIT 0
EOF
  RC=$?
  if [ "${RC}" != "0" ] ; then
    echo "Error: ${SCRIPTNAME} exited with RC=${RC}" > ${MAIL_HEADER}
    mail -s ${SCRIPTNAME} -a ${LOGFILE} ${MAIL_ADDR} < ${MAIL_HEADER}
    exit 1
  elif [ -f "${FILEDIR}/${APPL}_pwd_expire.log" ] ; then
    echo "${APPL}, password expire" > ${MAIL_HEADER}
    mail -s ${SCRIPTNAME} -a ${FILEDIR}/${APPL}_pwd_expire.log ${ITAM_MAIL} < ${MAIL_HEADER}
  fi
  shift 2
done
return
}
########################################################################
#
########################################################################
DO_PWD_CHECK QW_applications ${QW_ITAM_MAIL_ADDR} DRO_applications ${DRO_ITAM_MAIL_ADDR} BASE ${MAIL_ADDR} 
echo "\n"                                               >> ${LOGFILE}
echo "${DATE} ${TIME} STOP  - \"${SCRIPTNAME}"\"        >> ${LOGFILE}
exit 0

Last edited by Franklin52; 12-21-2010 at 07:44 AM.. Reason: Please use code tags, thank you
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Fixed Length file from a SQL script

Hi, I have a DB2 UDB 9.7 SQL script, as follows: I need to pass the script into Unix and generate a fixed length file from this. Can someone kindly provide a script to achieve it? SELECT CAST(COALESCE(CL_ID,'000000000') AS CHAR(9)) AS CL_ID ,STATUS... (5 Replies)
Discussion started by: ebsus
5 Replies

2. Shell Programming and Scripting

[Fixed Itself!] Sending mail form script using sSMTP does not work

I have installed sSMTP and set it up to use my gmail. Sending from cli does work fine: msg file:From: test@gmail.com To test2@gmail.com Subject: test post This is a test Executing from console:ssmtp -t < msg does work fine. But from script it does not work:#!/bin/sh ssmtp -t < msg... (0 Replies)
Discussion started by: Jotne
0 Replies

3. UNIX for Dummies Questions & Answers

Help with Fixed with data

Hi I have a data with varying length i need to convert it to data of fixed length 1000 by appending blank spaces. This has to be done only for specific records i tried with awk but it not happening. Pls suggest me some advices awk '/^500/ {$0=sprintf("%s%t" w-length "s", $0,x)}1'... (12 Replies)
Discussion started by: akshay01987
12 Replies

4. Shell Programming and Scripting

Concatenating fixed length lines in shell script

I have a peculiar file with record format like given below. Each line is wrapped to next lines after certain number of characters. I want to concatenate all wrapped lines into 1. Input:(wrapped after 10 columns) This is li ne1 This is li ne2 and this line is too lo ng Shortline ... (8 Replies)
Discussion started by: kmanyam
8 Replies

5. Shell Programming and Scripting

Help with executing parallel sessions for same shell script with different but fixed parameters

Hi Experts, There is a shell script that accepts positional parameter between 1-25 to execute case statement of script depending upon the parameter passed. Now I need to run all the 25 sessions parallely. In each option of case statement it is connecting with sqlplus and executing a select... (11 Replies)
Discussion started by: Opamps123
11 Replies

6. Shell Programming and Scripting

Need awk script to compare 2 fields in fixed length file.

Need a script that manipulates a fixed length file that will compare 2 fields in that file and if they are equal write that line to a new file. i.e. If fields 87-93 = fields 119-125, then write the entire line to a new file. Do this for every line in the file. After we get only the fields... (1 Reply)
Discussion started by: Muga801
1 Replies

7. Shell Programming and Scripting

script to run repeatedly after a fixed interval of time

Hi , I am working on the following script . I want this script to run and scan the log file repeatedly after 3 hours. This script will run & scan just for the current date logs and after every 3 hours. Kindly advice what to add in this script for this purpose. #!/bin/sh diff common.log... (3 Replies)
Discussion started by: himvat
3 Replies

8. Shell Programming and Scripting

Shell script..invoking command and exiting at fixed intervals

Hi, I need to write a shell script. Based on command line param to script say demode=yes Need to run an ant command for an hour(configurable) Then exit Again run the ant command all this needs to be in a loop. Thanks in advance Raj (1 Reply)
Discussion started by: rajuak12
1 Replies
Login or Register to Ask a Question