Script need to get fixed Post: 302481076

Sponsored Content

Top Forums Shell Programming and Scripting Script need to get fixed Post 302481076 by BeefStu on Thursday 16th of December 2010 03:12:44 PM

12-16-2010

Registered User

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:

BeefStu

View Public Profile for BeefStu

Find all posts by BeefStu

8 More Discussions You Might Find Interesting

1. 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

2. 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. 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...

4. 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...

5. 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 ...

6. 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'...

7. 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...

8. 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...

LEARN ABOUT OPENSOLARIS

trunc

trunc(3M)						  Mathematical Library Functions						 trunc(3M)

NAME

       trunc, truncf, truncl - round to truncated integer value

SYNOPSIS

       c99 [ flag... ] file... -lm [ library... ]
       #include <math.h>

       double trunc(double x);

       float truncf(float x);

       long double truncl(long double x);

DESCRIPTION

       These functions round their argument to the integer value, in floating format, nearest to but no larger in magnitude than the argument.

RETURN VALUES

       Upon successful completion, these functions return the truncated integer value.

       If x is NaN, a NaN is returned.

       If x is +-0 or +-Inf, x is returned.

ERRORS

       No errors are defined.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Interface Stability	     |Standard			   |
       +-----------------------------+-----------------------------+
       |MT-Level		     |MT-Safe			   |
       +-----------------------------+-----------------------------+

SEE ALSO

       math.h(3HEAD), attributes(5), standards(5)

SunOS 5.11							    12 Jul 2006 							 trunc(3M)