Andy,
What I did was copy my script that looks for moribund filespaces into the new script, knowing that the days calculation was correct. Then I started expanding it until I got lost in parentheses. (The offender was the left-most one.) Once someone pointed that out, it was easy to substitute LASTACC_TIME for BACKUP_END. So now the domain administrator has a weekly list divided by building (i.e., schedule).
Thanks.
At 01:28 PM 5/17/2005 -0700, you wrote:
Fred, I am not really clear on what you are trying to accomplish.
There is no BACKUP_END column in the NODES table; that column exists only in the FILESPACES table.
Something like this will show you info for nodes with file spaces that have not been backed up in the last 30 days:
select * from filespaces - where backup_end is null or backup_end < current_timestamp - 30 days
This will show you the schedules associated with those nodes:
select schedule_name, node_name - from associations - where node_name in - (select distinct node_name - from filespaces - where backup_end is null or backup_end < current_timestamp - 30 days)
;Unfortunately this might not be exactly what you are looking for. For example, if the node has a defunct file space (i.e. it has been removed from the domain, but not deleted from the TSM server), it will be shown in the results of this command, even if the other (live) file systems have been backed up recently.
There is no way to tie a file space's last backup to a schedule name.
Another approach might be to use the node's last access date:
select node_name - from nodes - where lastacc_time is null or lastacc_time < current_timestamp - 30 days
At this point, you can tie the node back to schedule names using a similar command as before:
select schedule_name, node_name - from associations - where node_name in - (select node_name - from nodes - where lastacc_time is null or lastacc_time < current_timestamp - 30 days)
(you can modify these further if they will be useful)
Regards,
Andy
Andy Raibeck IBM Software Group Tivoli Storage Manager Client Development Internal Notes e-mail: Andrew Raibeck/Tucson/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED]
The only dumb question is the one that goes unasked. The command line is your friend. "Good enough" is the enemy of excellence.
"ADSM: Dist Stor Manager" <ADSM-L@VM.MARIST.EDU> wrote on 2005-05-17 12:26:44:
> I have a very large domain, 800+ clients, where the owner would like his > reports broken out by schedule, starting with the not used in last 30 day > report. I think the appropriate select should look something like: > > select associations.schedule_name,associations.node_name from asso > ciations where associations.node_name in (select nodes.node_name from nodes > wher > e (((30 < days(current_timestamp) - days(backup_end)) or backup_end > is null and > domain_name='SSD')) group by associations.schedule_name > > I'm at the point where I'm getting the 'additional tokens needed' message, > which usually denotes unbalanced parentheses, but I can't see where. All > help greatly appreciated. > > > > Fred Johanson > ITSM Administrator > University of Chicago > 773-702-8464
Fred Johanson ITSM Administrator University of Chicago 773-702-8464
