Select statement to only list backups with particular event status
Hello everyone, I am trying to create a select statement that will list all backups for clients in the domain: windows hmig that started after yesterday at 6PM until today at the current date/time that had a status of: Missed, In Progress, Fail%, Started. I tried the below select statement but it is not giving me everything that I am looking for and it's also reporting future events which I don't want. Can anyone tell me what I'm doing wrong? Thanks in advance! select event as Event,date(actual_start) as Date,time(actual_start) as Start,time(Completed) as End, node_name, domain_name,Status from events where domain_name='WINDOWS' or domain_name='HMIG' and status like 'Fail%' or status='Missed' or status='In Progres' or status='Started' and actual_time=current_timestamp-1 day This e-mail and any attachments to it are confidential and are intended solely for use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this e-mail without the author's prior permission. The views expressed in this e-mail message do not necessarily represent the views of Highmark Inc., its subsidiaries, or affiliates.
Re: Select statement to only list backups with particular event status
The EVENTS table has been an oddball in TSM, as I note in ADSM QuickFacts, because of the way it was engineered. Using relative timestamp references traditionally doesn't work, so you need to employ an absolute timestamp, as in select * from events WHERE SCHEDULED_START = '2010-06-28' Note also that there is no actual_time column in instances of the EVENTS table that I know of. Use the following to verify column names: select * from syscat.columns where tabname='EVENTS' I haven't seen 'In Progress' as one of the possible status values; but things change over numerous releases. Richard Sims On Jun 29, 2010, at 11:05 AM, Moyer, Joni M wrote: Hello everyone, I am trying to create a select statement that will list all backups for clients in the domain: windows hmig that started after yesterday at 6PM until today at the current date/time that had a status of: Missed, In Progress, Fail%, Started. I tried the below select statement but it is not giving me everything that I am looking for and it's also reporting future events which I don't want. Can anyone tell me what I'm doing wrong? Thanks in advance! select event as Event,date(actual_start) as Date,time(actual_start) as Start,time(Completed) as End, node_name, domain_name,Status from events where domain_name='WINDOWS' or domain_name='HMIG' and status like 'Fail%' or status='Missed' or status='In Progres' or status='Started' and actual_time=current_timestamp-1 day
Re: Select statement to only list backups with particular event status
Hi Richard, Is there any other way to do a report that would list missed, failed, in progress and started backups then on a daily basis running it from the prior day at 6PM until the current time the script is run at 9AM? I currently run q event at 9AM which catches the missed failed backups, but nothing that currently accounts for anything that is in progress or has been started. Any suggestions/ideas are appreciated! This one has me stumped! -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Richard Sims Sent: Tuesday, June 29, 2010 12:09 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: Select statement to only list backups with particular event status The EVENTS table has been an oddball in TSM, as I note in ADSM QuickFacts, because of the way it was engineered. Using relative timestamp references traditionally doesn't work, so you need to employ an absolute timestamp, as in select * from events WHERE SCHEDULED_START = '2010-06-28' Note also that there is no actual_time column in instances of the EVENTS table that I know of. Use the following to verify column names: select * from syscat.columns where tabname='EVENTS' I haven't seen 'In Progress' as one of the possible status values; but things change over numerous releases. Richard Sims On Jun 29, 2010, at 11:05 AM, Moyer, Joni M wrote: Hello everyone, I am trying to create a select statement that will list all backups for clients in the domain: windows hmig that started after yesterday at 6PM until today at the current date/time that had a status of: Missed, In Progress, Fail%, Started. I tried the below select statement but it is not giving me everything that I am looking for and it's also reporting future events which I don't want. Can anyone tell me what I'm doing wrong? Thanks in advance! select event as Event,date(actual_start) as Date,time(actual_start) as Start,time(Completed) as End, node_name, domain_name,Status from events where domain_name='WINDOWS' or domain_name='HMIG' and status like 'Fail%' or status='Missed' or status='In Progres' or status='Started' and actual_time=current_timestamp-1 day This e-mail and any attachments to it are confidential and are intended solely for use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this e-mail without the author's prior permission. The views expressed in this e-mail message do not necessarily represent the views of Highmark Inc., its subsidiaries, or affiliates.
Re: Select statement to only list backups with particular event status
The only way to see status=Started jobs is if you perform a query while they are under way: if you really wanted that, a cron job capture would take care of it. Running a query after all scheduled backups have finished will report final status of Completed (or Missed or Failed), which is probably what you get at 09:00. And, of course, the Events table does not contain information for backups which are independently performed on the client. Such event information is superficial, of course... The client administrator should be reviewing backup logs (and the dsmerror.log!) for anomalies which may be delaying backups (e.g., retries) or be causing individual files or whole file systems to not get backed up (as in a Domain omission or forgotten, old Exclude). Richard Sims http://people.bu.edu/rbs On Jun 29, 2010, at 1:28 PM, Moyer, Joni M wrote: Hi Richard, Is there any other way to do a report that would list missed, failed, in progress and started backups then on a daily basis running it from the prior day at 6PM until the current time the script is run at 9AM? I currently run q event at 9AM which catches the missed failed backups, but nothing that currently accounts for anything that is in progress or has been started. Any suggestions/ideas are appreciated! This one has me stumped!
Re: Select statement to only list backups with particular event status
Joni, Query Events has other problems, too: -- You might have a machine that isn't on any schedule, for example. Query Events won't tell you that. -- You might have include-exclude statements, or domain statements, that cause a directory or an entire drive to be skipped. It won't tell you about that. -- You might have retention policies that are way too short (so you can't recover from last week), or way too long (so you're wasting tons of storage). Again, no indication from query events. -- You might even have machines (VMs?) in production that nobody ever registered to TSM. Nothing in TSM will tell you about that. One thing you can do is stop using query backups, and use the filespace table's backup_end field to find backups that are older than a day or two. That solves some (not all) of the above problems. We have a more thorough solution here goog_918866862http://www.tsmworks.com/art. Hope this helps. Lindsay Morris CEO, TSMworks Tel. 1-859-539-9900 skype:18595399900?call lind...@tsmworks.com On Tue, Jun 29, 2010 at 1:28 PM, Moyer, Joni M joni.mo...@highmark.comwrote: Hi Richard, Is there any other way to do a report that would list missed, failed, in progress and started backups then on a daily basis running it from the prior day at 6PM until the current time the script is run at 9AM? I currently run q event at 9AM which catches the missed failed backups, but nothing that currently accounts for anything that is in progress or has been started. Any suggestions/ideas are appreciated! This one has me stumped! -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Richard Sims Sent: Tuesday, June 29, 2010 12:09 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: Select statement to only list backups with particular event status The EVENTS table has been an oddball in TSM, as I note in ADSM QuickFacts, because of the way it was engineered. Using relative timestamp references traditionally doesn't work, so you need to employ an absolute timestamp, as in select * from events WHERE SCHEDULED_START = '2010-06-28' Note also that there is no actual_time column in instances of the EVENTS table that I know of. Use the following to verify column names: select * from syscat.columns where tabname='EVENTS' I haven't seen 'In Progress' as one of the possible status values; but things change over numerous releases. Richard Sims On Jun 29, 2010, at 11:05 AM, Moyer, Joni M wrote: Hello everyone, I am trying to create a select statement that will list all backups for clients in the domain: windows hmig that started after yesterday at 6PM until today at the current date/time that had a status of: Missed, In Progress, Fail%, Started. I tried the below select statement but it is not giving me everything that I am looking for and it's also reporting future events which I don't want. Can anyone tell me what I'm doing wrong? Thanks in advance! select event as Event,date(actual_start) as Date,time(actual_start) as Start,time(Completed) as End, node_name, domain_name,Status from events where domain_name='WINDOWS' or domain_name='HMIG' and status like 'Fail%' or status='Missed' or status='In Progres' or status='Started' and actual_time=current_timestamp-1 day This e-mail and any attachments to it are confidential and are intended solely for use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this e-mail without the author's prior permission. The views expressed in this e-mail message do not necessarily represent the views of Highmark Inc., its subsidiaries, or affiliates.
Re: Select statement to only list backups with particular event status
Here is a select statement that I use to get all of this info for the last 24 hours: select * from events where status!='Completed' and status!='Future' and status!='Pending' and status!='Started' and scheduled_start = '2010-01-01' and scheduled_start = current_timestamp - 24 hours -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Richard Sims Sent: Tuesday, June 29, 2010 12:45 PM To: ADSM-L@vm.marist.edu Subject: Re: [ADSM-L] Select statement to only list backups with particular event status The only way to see status=Started jobs is if you perform a query while they are under way: if you really wanted that, a cron job capture would take care of it. Running a query after all scheduled backups have finished will report final status of Completed (or Missed or Failed), which is probably what you get at 09:00. And, of course, the Events table does not contain information for backups which are independently performed on the client. Such event information is superficial, of course... The client administrator should be reviewing backup logs (and the dsmerror.log!) for anomalies which may be delaying backups (e.g., retries) or be causing individual files or whole file systems to not get backed up (as in a Domain omission or forgotten, old Exclude). Richard Sims http://people.bu.edu/rbs On Jun 29, 2010, at 1:28 PM, Moyer, Joni M wrote: Hi Richard, Is there any other way to do a report that would list missed, failed, in progress and started backups then on a daily basis running it from the prior day at 6PM until the current time the script is run at 9AM? I currently run q event at 9AM which catches the missed failed backups, but nothing that currently accounts for anything that is in progress or has been started. Any suggestions/ideas are appreciated! This one has me stumped!
Re: Select statement to only list backups with particular event status
That worked like a charm! Thanks so much! -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Sheridan, Peter T. Sent: Tuesday, June 29, 2010 1:49 PM To: ADSM-L@VM.MARIST.EDU Subject: Re: Select statement to only list backups with particular event status Here is a select statement that I use to get all of this info for the last 24 hours: select * from events where status!='Completed' and status!='Future' and status!='Pending' and status!='Started' and scheduled_start = '2010-01-01' and scheduled_start = current_timestamp - 24 hours -Original Message- From: ADSM: Dist Stor Manager [mailto:ads...@vm.marist.edu] On Behalf Of Richard Sims Sent: Tuesday, June 29, 2010 12:45 PM To: ADSM-L@vm.marist.edu Subject: Re: [ADSM-L] Select statement to only list backups with particular event status The only way to see status=Started jobs is if you perform a query while they are under way: if you really wanted that, a cron job capture would take care of it. Running a query after all scheduled backups have finished will report final status of Completed (or Missed or Failed), which is probably what you get at 09:00. And, of course, the Events table does not contain information for backups which are independently performed on the client. Such event information is superficial, of course... The client administrator should be reviewing backup logs (and the dsmerror.log!) for anomalies which may be delaying backups (e.g., retries) or be causing individual files or whole file systems to not get backed up (as in a Domain omission or forgotten, old Exclude). Richard Sims http://people.bu.edu/rbs On Jun 29, 2010, at 1:28 PM, Moyer, Joni M wrote: Hi Richard, Is there any other way to do a report that would list missed, failed, in progress and started backups then on a daily basis running it from the prior day at 6PM until the current time the script is run at 9AM? I currently run q event at 9AM which catches the missed failed backups, but nothing that currently accounts for anything that is in progress or has been started. Any suggestions/ideas are appreciated! This one has me stumped! This e-mail and any attachments to it are confidential and are intended solely for use of the individual or entity to whom they are addressed. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not keep, use, disclose, copy or distribute this e-mail without the author's prior permission. The views expressed in this e-mail message do not necessarily represent the views of Highmark Inc., its subsidiaries, or affiliates.
