On Thu, 20 Jan 2005, David Moore might have said: > Hello List - > > My goal is to script a customized report in the Operational Reporting tool, > to report backup information for selected nodes to specific email recipients > (the nodes are differentiated by the node-contact field). > > My problem is that a straight select on the EVENTS table is only pulling back > results that occurred since midnight. Then, when I try to insert a WHERE > clause (WHERE SCHEDULED_START >= CURRENT_TIMESTAMP - 24 HOURS), I get a RC=11 > (no match). With trial & error, if I hard-code the previous day (WHERE > SCHEDULED_START >= '2005-01-19 17:00'), I receive the results I'm looking > for. But, I need this in script form with no hard-coding, since it will run > automatically. > > I've also investigated the use of a Q EVENT * * clause, but I can't use it, > because I can't key on the CONTACT field off the NODES table (I could > hard-code the specific nodes, but that changes relatively often, so that's > not a fool-proof method for my site). > > Does anyone have a solution to this problem, either through the use of a > variable or a TIME field that will compare to the SCHEDULED_START field? > Remember, it needs to be a straight SELECT statement that runs through the > Operational Reporting tool. > > Thanks, in advance and have a happy Mardi Gras. > > David Moore > State of Louisiana >
This isn't exactly what you asked for, but it's something I moved into production yesterday. I execute the script below with the crontab entry: 0 0,4,6,8,10,12,16,20 * * * /home/megglest/tsmnodes.pl | /usr/bin/pod2html --quiet > /usr/HTTPServer/htdocs/en_US/tsmnodes.html It is executed from a non-root account using a non-administrator TSM account. ------------------------------------------------- #!/usr/bin/perl # $Id$ # $Log$ # query tsm for all nodes (f=d) # query tsm for all schedules (f=d) # query tsm for all domains (f=d) # query tsm for all policies (f=d) # put it all together and print a page for each node # future: print pages when asked by domain, by policy, by schedule, etc use strict; BEGIN { unlink '/tmp/tsmnodes.log'; } my $id = 'OPERATOR'; my $pw = 'PASSWORD'; #my $log = '/ttilogs/tsmnodes.log'; my $log = '/tmp/tsmnodes.log'; # log messages my $logmsgopen = 0; my $bequiet = 0; sub logmsg { my $msg = shift; if(!$logmsgopen) { $! = ''; open(LOGMSG, ">> $log"); if($!) { warn "$0: unable to open log '$log': $!" unless $bequiet; $bequiet = 1; return; } my $oldfh = select(LOGMSG); $| = 1; select($oldfh); $logmsgopen = 1; } my $lt = scalar(localtime); if(substr($msg, -1) eq "\n") { print LOGMSG $lt, ' ', $msg; } else { print LOGMSG $lt, ' ', $msg, "\n"; } } # execute commands # log the results # return the results sub execute { my $cmd = shift; &logmsg('cmd: ' . $cmd); open(CMD, "$cmd 2>&1 |") or die "$0: unable to execute command '$cmd': $!"; my @lines = <CMD>; close CMD; foreach my $line (@lines) { &logmsg($line); } &logmsg('done'); chomp(@lines); return [EMAIL PROTECTED]; } # execute a dsmadmc command sub dsmadmc { my $query = shift; #my $cmd = "dsmadmc -id=$id -pa=$pw -tabdelimited '$query'"; my $cmd = "dsmadmc -id=$id -pa=$pw -displaymode=list '$query'"; return execute($cmd); } # convert the argument into a label # save the labels/name association my %labels; sub genlabel { my $text = shift; my $label = lc($text); $label =~ s/[^[:alpha:]]//go; $text =~ s/^\s*//o; $text =~ s/\s*$//o; $labels{$label} = $text; return $label; } # load the given hash with the given values sub loadhash { my $h = shift; my $l = shift; my $k = shift; my $key; my %hash; my $found; chomp(@{$l}); foreach my $line (@{$l}) { if($line =~ /ANS8000I/o) { $found = 1; undef %hash; next; } next if $line =~ /ANS8002I|ANS8001I|ANR2034E/o; next unless $found; if($line =~ /^\s*$/o) { next unless scalar(keys(%hash)); if(defined($hash{$k})) { $h->{$hash{$k}} = { %hash }; } else { print STDERR "$0: key '$k' not found\n"; } undef %hash; next; } my @a = split(/\s*:\s*/o, $line); my $label = genlabel(shift(@a)); $hash{$label} = join(':', @a); } if(defined($hash{$k}) and scalar(keys(%hash))) { $h->{$hash{$k}} = { %hash }; } elsif(%hash and scalar(keys(%hash)) != 0) { print STDERR "$0: key '$k' not found\n"; } } # get a list of nodes and all details my %nodes; loadhash(\%nodes, dsmadmc('query node f=d'), 'nodename'); # for each node, get that node's filespaces my %filespaces; foreach my $node (sort keys %nodes) { my %h1; my %h2; loadhash(\%h1, dsmadmc("query occ $node *"), 'filespacename'); loadhash(\%h2, dsmadmc("query files $node f=d"), 'filespacename'); foreach my $fs (sort keys %h1) { foreach my $k (sort keys %{$h1{$fs}}) { $nodes{$node}->{'filespaces'}->{$fs}->{$k} = $h1{$fs}->{$k}; } } foreach my $fs (sort keys %h2) { foreach my $k (sort keys %{$h2{$fs}}) { $nodes{$node}->{'filespaces'}->{$fs}->{$k} = $h2{$fs}->{$k}; } } } # get a list of all schedules my %schedules; loadhash(\%schedules, dsmadmc('query sched f=d'), 'schedulename'); # get a list of all domains my %domains; loadhash(\%domains, dsmadmc('query domain f=d'), 'policydomainname'); # get a list of all policies my %policies; loadhash(\%policies, dsmadmc('query policy f=d'), 'policydomainname'); # get the association between nodes, policies, and domains my $pdn; my $sn; my @n; my $found = 0; foreach my $line (@{dsmadmc('query assoc')}) { chomp($line); if($line =~ /ANS8000I/o) { $found = 1; next; } next unless $found; if($line =~ /^\s*$/o) { foreach my $n (@n) { $nodes{$n}->{'policydomainname'} = $pdn; $nodes{$n}->{'schedulename'} = $sn; } undef $pdn; undef $sn; undef @n; next; } if($line =~ /policy domain name/oi) { $pdn = (split(/\s*:\s*/o, $line))[1]; } elsif($line =~ /schedule name/oi) { $sn = (split(/\s*:\s*/o, $line))[1]; } elsif($line =~ /associated nodes/oi) { @n = split(' ', (split(/\s*:\s*/o, $line))[1]); } } # put it all together # print the summary my %summary; foreach my $nodename (sort keys %nodes) { my $node = $nodes{$nodename}; $summary{'platform'}->{$node->{'platform'}}->{'number'}++; my $platform = $summary{'platform'}->{$node->{'platform'}}; foreach my $fsname (sort keys %{$node->{'filespaces'}}) { my $fs = $node->{'filespaces'}->{$fsname}; my $active = $fs->{'capacitymb'}; $active =~ s/,//go; $active =~ s/ .*$//o; $active = 0 if $active > 1000000000; $active *= $fs->{'pctutil'} / 100; $platform->{'activesize'} += $active; my $nfs = $fs->{'numberoffiles'}; $nfs =~ s/,//go; $nfs =~ s/ .*$//o; $platform->{'numberoffiles'} += $nfs; my $pso = $fs->{'physicalspaceoccupiedmb'}; $pso =~ s/,//go; $pso =~ s/ .*$//o; $platform->{'physicalspaceoccupiedmb'} += $pso; $summary{'activesize'} += $active; $summary{'numberoffiles'} += $nfs; $summary{'physicalspaceoccupiedmb'} += $pso; } } $summary{'platformcount'} = scalar keys %{$summary{'platform'}}; my $dt = localtime; print <<EOP; =head1 TTI BACKUP SUMMARY FOR ALL NODES This summary lists how many nodes of what type. =head2 SUMMARY FOR ALL PLATFORMS Number of platforms: $summary{'platformcount'} Number of files: $summary{'numberoffiles'} Size of Active Files (MB): $summary{'activesize'} Size of Inactive Files (MB): $summary{'physicalspaceoccupiedmb'} =head2 SUMMARY FOR EACH PLATFORM EOP foreach my $platformname (sort keys %{$summary{'platform'}}) { my $platform = $summary{'platform'}->{$platformname}; my $npct = sprintf("%.2f", $platform->{'numberoffiles'} / $summary{'numberoffiles'} * 100); my $apct = sprintf("%.2f", $platform->{'activesize'} / $summary{'activesize'} * 100); my $ipct = sprintf("%.2f", $platform->{'physicalspaceoccupiedmb'} / $summary{'physicalspaceoccupiedmb'} * 100); $platform->{'activesize'} = sprintf("%.2f", $platform->{'activesize'}) if $platform->{'activesize'}; print <<EOP; =head3 SUMMARY FOR PLATFORM: $platformname Number of files: $platform->{'numberoffiles'} ($npct) Size of Active Files (MB): $platform->{'activesize'} ($apct) Size of Inactive Files (MB): $platform->{'physicalspaceoccupiedmb'} ($ipct) EOP } print "\f\n\n"; # what filespaces have not been backed up in a while? print "=head1 TTI BACKUP ISSUES: OLD FILESPACES\n\n"; print " Node Name Filespace Backup Date Days Size\n", " --------------------------------------------------------------------\n\n"; my @oldfilespaces; foreach my $nodename (sort keys %nodes) { my $node = $nodes{$nodename}; foreach my $fsname (sort keys %{$node->{'filespaces'}}) { my $fs = $node->{'filespaces'}->{$fsname}; my $active = $fs->{'capacitymb'}; $active =~ s/,//go; $active *= $fs->{'pctutil'} / 100; $active = sprintf("%.2f", $active); my $days = $fs->{'dayssincelastbackupcompleted'} || 'UNKNOWN'; my $oldind = ''; $oldind = '**' if $days eq 'UNKNOWN' or $days > 60; if($oldind eq '**') { push(@oldfilespaces, [ $nodename, $fsname, $days, $fs->{'lastbackupcompletiondatetime'}, $active ]); } } } foreach my $old (@oldfilespaces) { print sprintf(" %-10.10s %-20.20s %-20.20s %4d %10.2f\n", $old->[0], $old->[1], $old->[3], $old->[2], $old->[4] > 1000000000 ? '******' : $old->[4]); } # print the node detail print "\n=head1 TTI BACKUP DETAIL FOR EACH NODES\n\n"; # print one page per each node my $tfile = "/tmp/tsmnodes.$$"; foreach my $nodename (sort keys %nodes) { my $node = $nodes{$nodename}; my $domain = $domains{$node->{'policydomainname'}}; my $sched = $schedules{$node->{'schedulename'}}; print <<EOP; =head2 TTI BACKUP STATUS FOR NODE: $nodename ($dt) This status sheet is generated directly from the data that exists within TSM. This form needs acknowledgement by the Application Owner, Backup Administrator, Platform Manager, Backup Manager, and Technical Services Manager each six months. This exercise is prompted by the Information Services Administrator. =head3 ACKNOWLEDGEMENT Application Owner: ___________________________ Backup Administrator: ___________________________ Platform Manager (Intel/AIX/Mainframe): ___________________________ Backup Manager: ___________________________ Technical Services Manager: ___________________________ =head3 NODE SETTINGS Application Owner: $node->{'contact'} Days Since Last Access: $node->{'dayssincelastaccess'} Platform: $node->{'platform'} TSM Client Version: $node->{'clientversion'} Compression: $node->{'compression'} Archive Delete Allowed: $node->{'archivedeleteallowed'} Backup Delete Allowed: $node->{'backupdeleteallowed'} Registration Date/Time: $node->{'registrationdatetime'} Registration Administrator: $node->{'registrationadministrator'} TCP/IP Name: $node->{'tcpipname'} TCP/IP Address: $node->{'tcpipaddress'} Session Initiation: $node->{'sessioninitiation'} =head3 DOMAINS Domain Name: $node->{'policydomainname'} Activation Date/Time: $domain->{'activationdatetime'} Description: $domain->{'description'} Backup Retention (Grace Period): $domain->{'backupretentiongraceperiod'} Archive Retention (Grace Period): $domain->{'archiveretentiongraceperiod'} Last Update by (administrator): $domain->{'lastupdatebyadministrator'} Last Update Date/Time: $domain->{'lastupdatedatetime'} =head3 SCHEDULES Schedule Name: $node->{'schedulename'} Domain Name: $node->{'policydomainname'} Description: $sched->{'description'} Action: $sched->{'action'} Options: $sched->{'options'} Objects: $sched->{'objects'} Start Date/Time: $sched->{'startdatetime'} Priority: $sched->{'priority'} Day Of Week: $sched->{'dayofweek'} Last Update by (administrator): $sched->{'lastupdatebyadministrator'} Last Update Date/Time: $sched->{'lastupdatedatetime'} =head3 POLICIES EOP print "=head3 FILESPACES\n\n"; foreach my $fsname (sort keys %{$node->{'filespaces'}}) { my $fs = $node->{'filespaces'}->{$fsname}; my $active = $fs->{'capacitymb'}; $active =~ s/,//go; $active *= $fs->{'pctutil'} / 100; $active = sprintf("%.2f", $active); my $inactive = 'UNKNOWN'; my $days = $fs->{'dayssincelastbackupcompleted'} || 'UNKNOWN'; my $oldind = ''; $oldind = '**' if $days eq 'UNKNOWN' or $days > 60; if($oldind eq '**') { push(@oldfilespaces, [ $nodename, $fsname, $days, $fs->{'lastbackupcompletiondatetime'}, $active ]); } print <<EOP; Filespace Name: $fs->{'filespacename'} FSID: $fs->{'fsid'} Is Filespace Unicode: $fs->{'isfilespaceunicode'} Capacity (MB): $fs->{'capacitymb'} Pct Util: $fs->{'pctutil'} Number of Files: $fs->{'numberoffiles'} Active Files (MB) (calculated): $active Inactive Files (MB): $fs->{'physicalspaceoccupiedmb'} Last Backup Completion Date/Time: $fs->{'lastbackupcompletiondatetime'} Days Since Last Backup Completed: $fs->{'dayssincelastbackupcompleted'} EOP } print "\f\n\n"; } END { unlink $tfile if -f $tfile; } __END__ -------------------------------------------------