Re: [Bacula-users] Query what clients haven't been backed up within a certain time period

[Geert S. <Gee...@va...>]

Op 20120622 om 16:52 schreef Dan Langille:
> On 2012-06-21 19:54, Mike Hobbs wrote:
> > Hello, I was wondering if anyone has written a bacula query that you 
> > can
> > run to find out what clients haven't been backed up within a certain
> > time period?  If so could you share?  I'm not an SQL guy which is why 
> > I
> > am asking.
> 
> This query provides a list of all clients who have not had a backup 
> finish within the past week:
> 
> SELECT C.name
> FROM client C
> WHERE NOT EXISTS (SELECT jobid
>         FROM job J
>          WHERE J.endtime > (now() - interval '1 week')
>            AND J.clientid = C.clientid)
> ORDER BY name
> 
> This was written for PostgreSQL. 
> I hope it runs on MySQL with minor modifications.
> 

Yes, after minor modifications it does run on MySQL

SELECT C.name FROM Client C WHERE NOT EXISTS
 ( SELECT jobid
   FROM Job J
   WHERE J.endtime > date_sub( now(),INTERVAL 1 week )
   AND J.clientid = C.clientid
 )
ORDER BY name


Cheers
Geert Stappers
-- 
http://www.vanadplatform.com/