Re: [Bacula-users] FW: Client last backup time report
I do similar stats with this script. Script searches for clients those have no successful backup for last 5 days: #!/bin/bash CURDATE=`date +%s` # DELTA is 5 days DELTA=432000 CHECKDATE=$((CURDATE-DELTA)) CHECKDATE2=`date -d @$CHECKDATE "+%Y-%m-%d %H:%M:%S"` ### Read client list via bcopnsole /usr/sbin/bconsole -c /etc/bacula/bconsole.conf < '$CHECKDATE2' ORDER BY Job.Sta rtTime;" | /usr/bin/mysql -s -u bacula /tmp/$CLIENT.sqlres ### If no backup or only failed backups are in database for last 5 days send email if [ `cat /tmp/$CLIENT.sqlres | wc -l` -eq 0 ] || [ "`grep T /tmp/$CLIENT.sqlres`" == "" ]; then echo "$CLIENT" >> /tmp/bcheckjobstatus.mail fi done ### If we have client without successfull backup send mail if [ -f /tmp/bcheckjobstatus.mail ]; then sed -i '1i From: bac...@backup.fm' /tmp/bcheckjobstatus.mail sed -i '2i To: root' /tmp/bcheckjobstatus.mail sed -i '3i Subject: Bacula clients without successful backup' /tmp/bcheckjobstatus.mail sed -i '4i \ ' /tmp/bcheckjobstatus.mail sed -i '5i Clients without successful backup for 5 days:' /tmp/bcheckjobstatus.mail cat /tmp/bcheckjobstatus.mail | /usr/sbin/sendmail -f bac...@backup.fm root fi ### Cleanup rm -f /tmp/bcheck_backup_status.out /tmp/*.sqlres /tmp/bcheckjobstatus.mail On 11/15/2013 11:11 PM, Robyn wrote: > I need to create a report with the last time a good backup was run of each > client. > > We are looking for anyone who has not backed up recently, so it would be > nice if the report could be set for clients that have not had a successful > backup in 1 week, or even a variable amount of time. > > I am assuming this would be SQL. Grepping (or anything else) Bacula's 'List > Jobs' would not work, since if a client has not even started a backup it > would not be listed there. (Our backups are kicked off by remotely calling a > script on each client that starts the FD. We have several 'waves' of backups > when departments are not here or would be least affected by the backup.) > > We envision the report being something like: > > > > Name Last Backup F/D/I JobFiles JobBytes JobStatus > > COMPUTER12013-11-06 23:59 I 29129,056 T > > LAPTOP2 2013-10-20 10:30 D 17 89,423 T > > COMPUTER22013-10-19 17:05 I 0 0 E > > > > Anyone else doing something like this, or can point me to some examples? > > > > Thanks in advance > > > > > -- > DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps > OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access > Free app hosting. Or install the open source package on any LAMP server. > Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! > http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk > > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- Shape the Mobile Experience: Free Subscription Software experts and developers: Be at the forefront of tech innovation. Intel(R) Software Adrenaline delivers strategic insight and game-changing conversations that shape the rapidly evolving mobile landscape. Sign up now. http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Please add info to bacula Troubleshooting
Troubleshooting: Bacula daemons not starting. On debian/ubuntu systems bacula startup with init.d sysv init script /etc/init.d/bacula. For check why it not starting use init.d script modifications (for example): ARGS="-d20 -dt -c /etc/bacula/bacula-fd.conf" do_start() { start-stop-daemon --start --quiet --pidfile $PIDFILE \ --exec $DAEMON -- $ARGS >> /var/log/bacula-fd-initd.log 2>&1 } We added `-d20 -dt` and `>> /var/log/bacula-fd-initd.log 2>&1` -- С Уважением, специалист по техническому и программному обеспечению, системный администратор Скубриев Владимир ~~~ Россия, Ростовская область, г. Таганрог тел. моб: +7 (918) 504 38 20 skype: v.skubriev icq: 214-800-502 www: skubriev.ru -- Shape the Mobile Experience: Free Subscription Software experts and developers: Be at the forefront of tech innovation. Intel(R) Software Adrenaline delivers strategic insight and game-changing conversations that shape the rapidly evolving mobile landscape. Sign up now. http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] FW: Client last backup time report
One refinement. SELECT DISTINCT doesn't actually work (ha). If you want to see just the last job failure, the query would look like this: select j.name as "Job Name", c.name as "Client", max(j.schedtime) as "Job Scheduled Time" from job j inner join client c on c.clientid=j.clientid where j.jobstatus='E' and endtime >= now()-'10 days'::interval group by j.name, c.name The previous query showed this for our system: Job Name| Client | Job Scheduled Time ++- e905c-Critical | e905c | 2013-11-12 06:00:00 oe7| oe7| 2013-11-12 20:00:00 oe7| oe7| 2013-11-13 20:00:00 e905c-Critical | e905c | 2013-11-13 20:00:00 e905c-Critical | e905c | 2013-11-13 20:00:00 oe7| oe7| 2013-11-14 20:00:00 oe7| oe7| 2013-11-15 20:00:00 (7 rows) But with the query above, we get thus: Job Name| Client | Job Scheduled Time ++- oe7| oe7| 2013-11-15 20:00:00 e905c-Critical | e905c | 2013-11-13 20:00:00 (2 rows) Steven Hammond I.T. Manager Technical Chemical Company For support, please email us at supp...@technicalchemical.com. -Original Message- From: Thomas Lohman [mailto:thom...@mtl.mit.edu] Sent: Monday, November 18, 2013 10:24 AM To: Robyn; bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] FW: Client last backup time report We do something like this by running a job within Bacula every morning that scans all client configuration files, builds a list of expected current jobs/clients and then queries the Bacula DB to see when/if they've been successfully backed up or not (i.e. marked with a T). If it's been more than the specified number of days, then they are added to a list which is then mailed to whatever address is specified (e.g. the IT system folks). The content of the message looks something like this: WARNING --> Bacula has not backed up: (1) Job: foobar for Client: foobar-host in the past 10 days I suspect that this utility is fairly specific to our configuration structure so not sure if it could be of direct help to you but I figured I'd throw it out there as an example that it is pretty straight forward to do what you want to do and a lot of ways to implement it. :) --tom > I need to create a report with the last time a good backup was run of > each client. > > We are looking for anyone who has not backed up recently, so it would be > nice if the report could be set for clients that have not had a > successful backup in 1 week, or even a variable amount of time. > > I am assuming this would be SQL. Grepping (or anything else) Bacula's > 'List Jobs' would not work, since if a client has not even started a > backup it would not be listed there. (Our backups are kicked off by > remotely calling a script on each client that starts the FD. We have > several 'waves' of backups when departments are not here or would be > least affected by the backup.) > > We envision the report being something like: > > Name Last Backup F/D/I JobFiles JobBytes JobStatus > > COMPUTER12013-11-06 23:59 I 29129,056 T > > LAPTOP2 2013-10-20 10:30 D 17 89,423 T > > COMPUTER22013-10-19 17:05 I 0 0 E > > Anyone else doing something like this, or can point me to some examples? > > Thanks in advance > > > > -- > DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps > OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access > Free app hosting. Or install the open source package on any LAMP server. > Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! > http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk > > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access Free app hosting. Or install the open source package on any LAMP server. Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Shape the Mobile Experience: Free Subscription Software experts and developers: Be at the forefront of tech innovation. Intel(R) Software Adrenaline delivers strategic insight and game-changing conversations that
Re: [Bacula-users] FW: Client last backup time report
I'm a newbie and so others can/will probably offer a better way of doing this, but I would probably do something like this: I would create a bash script and run it from crontab with the following line: psql -d Bacula = now()-'10 days'::interval If you just want to see the LAST time a job failed (not every time in the last 10 days) then use SELECT DISTINCT. I actually added it to our bconsole query list by appending the query.sql file with: #21 :Backup Exception Report *Enter # of days to report: SELECT j.name as "Job Name", c.name as "Client", j.schedtime as "Job Run Date" FROM job j INNER JOIN client c on c.clientid=j.clientid WHERE j.jobstatus='E' and endtime >= now()-'%1 days'::interval; Steven Hammond I.T. Manager Technical Chemical Company For support, please email us at supp...@technicalchemical.com. -Original Message- From: Thomas Lohman [mailto:thom...@mtl.mit.edu] Sent: Monday, November 18, 2013 10:24 AM To: Robyn; bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] FW: Client last backup time report We do something like this by running a job within Bacula every morning that scans all client configuration files, builds a list of expected current jobs/clients and then queries the Bacula DB to see when/if they've been successfully backed up or not (i.e. marked with a T). If it's been more than the specified number of days, then they are added to a list which is then mailed to whatever address is specified (e.g. the IT system folks). The content of the message looks something like this: WARNING --> Bacula has not backed up: (1) Job: foobar for Client: foobar-host in the past 10 days I suspect that this utility is fairly specific to our configuration structure so not sure if it could be of direct help to you but I figured I'd throw it out there as an example that it is pretty straight forward to do what you want to do and a lot of ways to implement it. :) --tom > I need to create a report with the last time a good backup was run of > each client. > > We are looking for anyone who has not backed up recently, so it would be > nice if the report could be set for clients that have not had a > successful backup in 1 week, or even a variable amount of time. > > I am assuming this would be SQL. Grepping (or anything else) Bacula's > 'List Jobs' would not work, since if a client has not even started a > backup it would not be listed there. (Our backups are kicked off by > remotely calling a script on each client that starts the FD. We have > several 'waves' of backups when departments are not here or would be > least affected by the backup.) > > We envision the report being something like: > > Name Last Backup F/D/I JobFiles JobBytes JobStatus > > COMPUTER12013-11-06 23:59 I 29129,056 T > > LAPTOP2 2013-10-20 10:30 D 17 89,423 T > > COMPUTER22013-10-19 17:05 I 0 0 E > > Anyone else doing something like this, or can point me to some examples? > > Thanks in advance > > > > -- > DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps > OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access > Free app hosting. Or install the open source package on any LAMP server. > Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! > http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk > > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access Free app hosting. Or install the open source package on any LAMP server. Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- Shape the Mobile Experience: Free Subscription Software experts and developers: Be at the forefront of tech innovation. Intel(R) Software Adrenaline delivers strategic insight and game-changing conversations that shape the rapidly evolving mobile landscape. Sign up now. http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] FW: Client last backup time report
We do something like this by running a job within Bacula every morning that scans all client configuration files, builds a list of expected current jobs/clients and then queries the Bacula DB to see when/if they've been successfully backed up or not (i.e. marked with a T). If it's been more than the specified number of days, then they are added to a list which is then mailed to whatever address is specified (e.g. the IT system folks). The content of the message looks something like this: WARNING --> Bacula has not backed up: (1) Job: foobar for Client: foobar-host in the past 10 days I suspect that this utility is fairly specific to our configuration structure so not sure if it could be of direct help to you but I figured I'd throw it out there as an example that it is pretty straight forward to do what you want to do and a lot of ways to implement it. :) --tom > I need to create a report with the last time a good backup was run of > each client. > > We are looking for anyone who has not backed up recently, so it would be > nice if the report could be set for clients that have not had a > successful backup in 1 week, or even a variable amount of time. > > I am assuming this would be SQL. Grepping (or anything else) Bacula's > 'List Jobs' would not work, since if a client has not even started a > backup it would not be listed there. (Our backups are kicked off by > remotely calling a script on each client that starts the FD. We have > several 'waves' of backups when departments are not here or would be > least affected by the backup.) > > We envision the report being something like: > > Name Last Backup F/D/I JobFiles JobBytes JobStatus > > COMPUTER12013-11-06 23:59 I 29129,056 T > > LAPTOP2 2013-10-20 10:30 D 17 89,423 T > > COMPUTER22013-10-19 17:05 I 0 0 E > > Anyone else doing something like this, or can point me to some examples? > > Thanks in advance > > > > -- > DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps > OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access > Free app hosting. Or install the open source package on any LAMP server. > Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! > http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk > > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access Free app hosting. Or install the open source package on any LAMP server. Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] FW: Client last backup time report
Hi, add the lines below to your query.sql and you should be good to go, adding a time period parameter should be trivial (check the other examples in query.sql). HTH Uwe ## :List all clients without a backup in the last week 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 ## BTW, is it possible to select a query by cmd line parameter and pipe it to bconsole like so? echo "query id=14 " | bconsole Uwe -- NIONEX --- Ein Unternehmen der Bertelsmann SE & Co. KGaA -- DreamFactory - Open Source REST & JSON Services for HTML5 & Native Apps OAuth, Users, Roles, SQL, NoSQL, BLOB Storage and External API Access Free app hosting. Or install the open source package on any LAMP server. Sign up and see examples for AngularJS, jQuery, Sencha Touch and Native! http://pubads.g.doubleclick.net/gampad/clk?id=63469471&iu=/4140/ostg.clktrk ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users