Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

To get older than last 7days records using awk scripting to generate report and send email

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To get older than last 7days records using awk scripting to generate report and send email
# 1  
Old 07-19-2017
To get older than last 7days records using awk scripting to generate report and send email
Hello All,

I have need as below:
1--> I need to get all users(who submit jobs) and their details by using below command:
Code:
qstat -u \*

output of the above command looks line below:

Code:
job-ID	prior	name	user-id	state	"submit/start at"	queue	jclass	slots ja-task-ID
-------------------------------------------------------------------------------------------
147956 10.29988 python_val user1       r     07/19/2017 07:30:15 queue1	1
147960 10.29988 python_dev user1       r     07/19/2017 07:30:33 queue2	1
147504 0.31988 gbdt_1.4.1 user2   r     07/19/2017 05:41:20 queue3 	1
122900 0.30988 run_python user3      r     07/10/2017 01:10:01 queue4 	1

The above output is space separated and i am interested in job-id, user-id and submit/start at (which is job submit date) from the above output

2--> I have another command which displays all users details from ADS as below:

Code:
/ccore/pbis/bin/enum-members "adsusers"

The output of the above command looks like below: (i am displaying one user's output, complete output contains bulk of below same blocks)

Code:
User object [001] (XXXXXXXX)
============
Enabled: 
Distinguished name: 
SAM account name: 
NetBIOS domain name: 
UPN: email@FINCO.COM
Display Name:FName LName
Alias: user-id
UNIX name:
GECOS:
Shell: 
Home directory: 
Windows home directory: 
Local windows home directory:
UID: 
Primary group SID: 
Primary GID: 
Password expired: 
Password never expires: 
Change password on next logon:
User can change password:
Account disabled:
Account expired:
Account locked:

In the above output i am interested in UPN (email), Display Name, Alias (user-id) from the above output

3 --> Now the requirement is i need to get the job details which are running more than last 7 days and send those details to user (which i will get email id from point#2)

currently i am getting this by following script:

Code:
!#/bin/bash

for line in `qstat -u \* | awk -v dt="$(date "--date=$(date) -7 day" +%m/%d/%Y)" 'BEGIN{OFS=":"}/qw/{if($6<dt) print $4,$1}'`;  
do 
user=$(echo $line | awk -F":" '{print $1}'); 
jobid=$(echo $line | awk -F":" '{print $2}');
email=$(/ccore/pbis/bin/enum-members "adsusers" | grep ^UNIX -B3  | grep $user -B2 | grep UPN | awk '{print $2}'); 
userName=$(/ccore/pbis/bin/enum-members "adsusers" | grep ^UNIX -B3  | grep $user -B2 | grep -i "Display Name" | awk -F":" '{print $2}')
echo -e "Hello $userName,\n\n Job(s) with job id(s): $jobid executing more than last 7 days, hence request you to take action, else job will be killed in another 1 days, \n\n Thank you." | mailx -s "Long running job for user: $userName ($user) and Job ID: $jobid" "${email}"
done

I know i am using a pattern matching using qw which i had to do and i am getting output as below:

Code:
Email Subject: Long running job for user:  FName LName (user-id) and Job ID(s): job-id
Email Body:
Hello FName LName,
Job(s) with job id(s): job-id executing more than last 7 days, hence request you to take action, else job will be killed in another 1 days, 
Thank you.

How can i implement the above script using awk? I know I am using awk, but its in bits and peaces.
Also the issue with the above script is - if the user submit more than one job, for example 2 in above case (user1 with job id-147956 and 147960), he is receiving 2 separate emails, but he should receive one email with 2 job details.

can some one please help me to fix this?

Thank you!!
# 2  
Old 07-20-2017
Comparing dates in above format will fail on month or year transition, so you need a different approach. One would be converting to epoch seconds and then compare, or, as done here, just shifting the number to year/month/day format.
Unfortunately, none of the user-ids given in the qstat output find a match in the ADS file, so assumptions had to be made.
Try the following - ON A TEST SYSTEM, NOT PRODUCTION! - (but be aware that there's an small uncertainty in mailing the body as this can't be tested from here):
Code:
{ qstat -u \*; /ccore/pbis/bin/enum-members "adsusers"; } | awk -v dt="$(date "--date= -7 day" +%Y%m%d)" '

/^User obj/     {F2 = 1
                 FS = ":"
                 T1 = T2 = ""
                 next
                }
!F2             {if (NR < 3) next
                 split ($6, T, "/")
                 if (T[3]T[1]T[2] < dt) JID[$4] = JID[$4] " " $1
                 next
                }

/^UPN/          {T1 = $2
                }
/^Display/      {T2 = $2
                }
/^Alias/        {gsub (/ /, _, $2)
                 EM[$2] = T1
                 DN[$2] = T2
                }
END             {for (j in JID) {print "echo Hello " DN[j] "\\" RS "\\"
                                 print "Job\(s\) with job id\(s\): " JID[j] " executing more than last 7 days, hence request you to take action, else job will be killed in another
                                 print "Thank you.\\"
                                 print " \"|\" "
                                 print "mailx -s \"Long running job for user: " DN[j] " (" j ") and Job ID: " JID[j] "\" "  EM[j] 
                                }
                }
' | sh

This User Gave Thanks to RudiC For This Post:
VasuKukkapalli
# 3  
Old 07-20-2017
Hello RudiC, Thank you so much for your time and response.
All the users of
Code:
 qstat -u \*

will definitely present in
Code:
 /ccore/pbis/bin/enum-members "adsusers"

for sure.
Which means what ever user-id's as part of 1st command output will definitely be present (all the details including user-id (Alias)) in 2nd command output.
I have slightly change your script to test by adding another awk variable - testEmail like below:

Code:
{ qstat -u \*; /ccore/pbis/bin/enum-members "adsusers" } | awk -v dt="$(date "--date= -7 day" +%m/%d/%Y)" -v testEmail="vasubabu1@finco.com"'

/^User obj/     {F2 = 1
                 FS = ":"
                 T1 = T2 = ""
                 next
                }
!F2             {if (NR < 3) next
                 split ($6, T, "/")
                 if (T[3]T[1]T[2] < dt) JID[$4] = JID[$4] " " $1
                 next
                }

/^UPN/          {T1 = $2
                }
/^Display/      {T2 = $2
                }
/^Alias/        {gsub (/ /, _, $2)
                 EM[$2] = T1
                 DN[$2] = T2
                }
END             {for (j in JID) {print "echo Hello " DN[j] "\\" RS "\\"
                                 print "Job\(s\) with job id\(s\): " JID[j] " executing more than last 7 days, hence request you to take action, else job will be killed in another
                                 print "Thank you.\\"
                                 print " \"|\" "
                                 print "mailx -s \"Long running job for user: " DN[j] " (" j ") and Job ID: " JID[j] "\" "  testEmail 
                                }
                }
' | sh

But i am getting below exception:

Code:
awk: warning: escape sequence `\(' treated as plain `('
awk: warning: escape sequence `\)' treated as plain `)'
Usage: awk [POSIX or GNU style options] -f progfile [--] file ...
Usage: awk [POSIX or GNU style options] [--] 'program' file ...
POSIX options:          GNU long options:
        -f progfile             --file=progfile
        -F fs                   --field-separator=fs
        -v var=val              --assign=var=val
        -m[fr] val
        -O                      --optimize
        -W compat               --compat
        -W copyleft             --copyleft
        -W copyright            --copyright
        -W dump-variables[=file]        --dump-variables[=file]
        -W exec=file            --exec=file
        -W gen-po               --gen-po
        -W help                 --help
        -W lint[=fatal]         --lint[=fatal]
        -W lint-old             --lint-old
        -W non-decimal-data     --non-decimal-data
        -W profile[=file]       --profile[=file]
        -W posix                --posix
        -W re-interval          --re-interval
        -W source=program-text  --source=program-text
        -W traditional          --traditional
        -W usage                --usage
        -W use-lc-numeric       --use-lc-numeric
        -W version              --version

To report bugs, see node `Bugs' in `gawk.info', which is
section `Reporting Problems and Bugs' in the printed version.

gawk is a pattern scanning and processing language.
By default it reads standard input and writes standard output.

Examples:
        gawk '{ sum += $1 }; END { print sum }' file
        gawk -F: '{ print $1 }' /etc/passwd

Could you please help me to fix this? Thank you!!

---------- Post updated at 11:12 AM ---------- Previous update was at 10:28 AM ----------

I got what the problem is, in below print statement double quotes was not ending properly.

Code:
print "Job\(s\) with job id\(s\): " JID[j] " executing more than last 7 days, hence request you to take action, else job will be killed in another

Unfortunately even after i fix this i didn't receive any email,and I don't see any exceptions. Just executing and I don't see any email, that's all. HEre is the last few statements of output:

Code:
> END             {for (j in JID) {print "echo Hello " DN[j] "\\" RS "\\"
>                                  print "Job(s) with job id(s): " JID[j] " executing more than last 7 days, hence request you to take action, else job will be killed in another 1 Day"
>                                  print "Thank you.\\"
>                                  print " \"|\" "
>                                  print "mailx -s \"Long running job for user: " DN[j] " (" j ") and Job ID: " JID[j] "\" "  testEmail
>                                 }
>                 }
> ' | sh
[vasulr@assbd0035 ~]$

could you please help me? Thank you!!
Last edited by VasuKukkapalli; 07-20-2017 at 03:26 PM..
# 4  
Old 07-20-2017
I'm afraid I can't. It was quite a mess to get the output in shape, and I couldn't test it with mail.
Try not to pipe it into sh, but analyse it on screen if the output makes sense. If it does, create a file of the same contents and have sh run it, mayhap with -xv options set. You may want to come back with results / errors so thes can be discussed here.

EDIT: looks like a partial line 1 days," was lost in dragging and dropping the script. Sorry for that - I counted double quotes incredibly often before it (almost) worked.
Last edited by RudiC; 07-20-2017 at 04:18 PM..
# 5  
Old 07-21-2017
Hello RudiC,

I have made small changes in the solution that you provided and now, its working as expected, Thank you so much for your time.

Here is the final script that is working as expected:

Code:
!#/bin/bash

{ qstat -u \*; /ccore/pbis/bin/enum-members "adsusers"; } | awk -v dt=$(date "--date=$(date) -7 day" +%m/%d/%Y) '
 /^User obj/     {
                  F2 = 1
                  FS = ":"
                  T1 = T2 = ""
                  next
                 }
 !F2             {
                  if (NR < 3) next
                  if ($5 ~ "qw" && $6 < dt) JID[$4] = $1 "," JID[$4]
                  next
                 }

 /^UPN/          {T1 = $2
                 }
 /^Display/      {T2 = $2
                 }
 /^Alias/        {gsub (/ /, _, $2)
                  EM[$2] = T1
                  DN[$2] = T2
                 }
 END             {for (j in JID) {print "echo -e \"Hello " DN[j] " \\n \\nJob(s) with job id(s): " JID[j] " executing more than last 7 days, hence request you to take action, else job(s) will be killed in another 1 day \\n \\n Thank you.\" | mailx -s \"Long running job for user: " DN[j] " (" j ") and Job ID(s): " JID[j] "\" " EM[j]
                                 }
                 }
 ' | sh

Thank you!!
Vasu
# 6  
Old 07-21-2017
You know that it won't work reliably as given in your post#5? With that date format, January 2018 is less (earlier) than December 2017.
And, you don't need the $(date) for the date arithmetics. date assumes NOW when differences are given.
The trick with the one line printout of the commands for sh is a good one...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk use to generate report

Hi , In a directory list of ddl files are stored in the given format above. Above is the sample ddl file. The ddl file name is same as that of table name ie email_notifications.ddl I want to generate below report using awk utility reading all the ddl files stored in /ddl path Desired output:... (1 Reply)
Discussion started by: vedanta
1 Replies

2. Shell Programming and Scripting

Send email if latest file in a directory is older than 2 hours

I have a objective of Sending email if latest file in a directory(excluding files of sub-dirs) is older than 2 hours. eg : ls -ltr drwx--x--x 2 abcde abc 256 2017-02-07 20:10 Mail -rw-rw-r-- 1 abcde abc 1170 2017-02-24 17:30 test -rw-rw-r-- 1 abcde abc 356 2017-03-09 18:00 xyz.csv... (3 Replies)
Discussion started by: simpltyansh
3 Replies

3. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

4. Shell Programming and Scripting

Remove bad records from file and move them into a file then send those via email

Hi my requirement is that i want pull the bad records from input file and move those records in to a seperate file. that file has to be sent via email.. any suggentions please (1 Reply)
Discussion started by: sxk4999
1 Replies

5. UNIX for Dummies Questions & Answers

new to ldap, send email to a ou or group, and see a list from email client

hi, i'm running openldap on ubuntu 10.04, creating new items with apache directory studio (windows version). i use the ldap just as an address book to our small office (email clients are windows live mail 2009, 2011, microsoft outlook 2007 and 2010). a. i cant see a list of the contacts,... (0 Replies)
Discussion started by: V4705
0 Replies

6. Shell Programming and Scripting

Script to send email after comparing the folder permissions to a certain permission & send email

Hello , I am trying to write a unix shell script to compare folder permission to say drwxr-x-wx and then send an email to my id in case the folders don't have the drwxr-x-wx permissions set for them . I have been trying to come up with a script for few days now , pls help me:( (2 Replies)
Discussion started by: nairshar
2 Replies

7. Shell Programming and Scripting

How to generate sample records from a file

i have a file having 30 million records.i want to generate a file having say 5% of total records in another file. the records in the new file shud be randomly generated. (1 Reply)
Discussion started by: Nishithinfy
1 Replies

8. Shell Programming and Scripting

awk scripting - matching records and summing up time

Hello. I just found out about awk, and it appears that this could handle the problem I'm having right now. I first stumbled on the thread How to extract first and last line of different record from a file, and that problem is almost similar to mine. In my case, an ASCII file will contain the... (0 Replies)
Discussion started by: Gonik
0 Replies

9. UNIX for Dummies Questions & Answers

awk to find the status and send an email

Hi, I have a datafile which has the following data and it can have much more records. The data set is as follows: ISA~00~ ~00~ ~ZZ~F159B ~ZZ~U1CAD ~051215~184 3~U~00200~000011432~0~P~< GS~FA~TC11A~U1CAD~051215~1843~000011432~X~002002 ST~997~0001... (6 Replies)
Discussion started by: isingh786
6 Replies

10. UNIX for Advanced & Expert Users

Unable to send eMail from a UNIX-Host ( using mailx ) to a Outlook-email-addres(Win)

Hi A) I am able to send eMail using mailx from a UNIX ( solaris 8 ) host to my Outlook-email-ID : FName.Surname@Citigroup.com ( This is NOT my actual -eMail-ID). But in Outlook the "From :" eMail address is displayed as " usr1@unix-host1.unregistered.email.citicorp.com " .i.e the words... (2 Replies)
Discussion started by: Vetrivela
2 Replies
Login or Register to Ask a Question