I am writing a script so that to filter jobs that are failed in past 24hrs and also the failed job is not completed or not in running state again.
rm -rf jobsfailed.txt jobscompleted.txt jobsnotcompleted.txt ## Remove the files which are created
export PGPASSWORD="$PG_PASS"
failed_jobs=`psql -t -U bacula bacula << EOD
SELECT jobid,job,name,jobbytes,realendtime,jobstatus,level FROM job WHERE jobstatus IN ('f','A','E') AND realendtime > NOW() - INTERVAL '24 HOURS';
\q
EOD` ### Collect all the jobs which are in the defined states
echo "$failed_jobs" >> jobsfailed.txt ### redirect the values to a file
sortedlist=$(awk -F'|' '{print $3}' jobsfailed.txt | sort | uniq) ## sort the values based on the jobname
for i in $sortedlist
do
retVal=$?
jobs_notcompleted=`psql -t -U bacula bacula << EOD1
SELECT jobid,job,name,jobbytes,realendtime,jobstatus,level FROM job WHERE name LIKE '$i' AND jobstatus IN ('T','R') AND starttime > NOW() - INTERVAL '24 HOURS' ORDER BY jobid DESC LIMIT 1;
\q
EOD1` ### If the job is in above defined states(T or R) then jobs completed successfully. Any other state apart from above then job not completed
if [[ $retVal -eq 0 ]]; then
echo "$jobs_notcompleted" >> jobscompleted.txt
else
echo "$jobs_notcompleted" >> jobsnotcompleted.txt
fi
exit $retVal
done
But i am not getting desired output. Since if no state is getting matched, then it is producing (0 rows) output. Please let me know, is there any other way if 0 rows are matched then that $jobs_notcompleted value should redirect to the jobsnotcompleted.txt file. jobscompleted.txt file is getting created and working as expected.
