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/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

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 AT VM.MARIST DOT 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