Hemant Shah wrote:
> --- On Sun, 7/13/08, Gregory Orange <gregory.orange AT metoceanengineers DOT 
> com> wrote:
>> Hemant Shah wrote:
>> <gregory.orange AT metoceanengineers DOT com> wrote:
>>>   Try following:
>>> echo "list volumes" | bconsole | grep Purged
>> I'd like to know jobs which are purged, not volumes.
>> Any way of doing that?
> 
> You may have to run SQL query against jobmedia and media tables.
> Something like:
> 
> select distinct job.name,job.jobid,media.volstatus from media, jobmedia, job 
> where job.jobid = jobmedia.jobid and jobmedia.mediaid = media.mediaid and 
> media.volstatus = 'Purged'

Hmm, not quite. This is what I really wanted:

SELECT file.fileid FROM file WHERE file.jobid NOT IN (
SELECT DISTINCT file.jobid FROM file INNER JOIN job ON file.jobid=job.jobid
)
... but this query never finishes. Actually I want DELETE FROM rather 
than SELECT at the top, but you get the idea.



This is what I've ended up with so far:
#!/bin/bash
LOGS="/var/log/backup"
TODAY=`/bin/date +%Y%m%d`
YESTERDAY=`/bin/date +%Y%m%d --date="1 day ago"`

psql -h dbhost -U dbuser bacula -t -c "
SELECT DISTINCT job.jobid FROM job
" > ${LOGS}/${TODAY}.jobids.txt

diff ${LOGS}/${YESTERDAY}.jobids.txt ${LOGS}/${TODAY}.jobids.txt | sed 
-n 's/< *\([0-9][0-9]*\)/\1, /p' > ${LOGS}/${TODAY}.removejobs.txt

psql -h dbhost -U dbuser bacula -t -c "
SELECT file.fileid FROM file WHERE file.jobid IN (
`cat ${LOGS}/${TODAY}.removejobs.txt`
0 )
" > ${LOGS}/${TODAY}.delresult.txt

bzip2 ${YESTERDAY}.*.txt



I'll change the final SELECT to a DELETE once I've seen it run a coupla 
times.

-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users