How to get this view

illllm

ADSM.ORG Member
Joined
Jan 9, 2018
Messages
153
Reaction score
2
Points
0
PREDATAR Control23

Hi Everyone,

I am trying to create a report that will display this:

Node Name | Amount to Backup | Last Backup Date | Last Backup Size | # Files Backed up | Number of Skipped Files

How do i query the TSM db for this ? I looked at Tobias.org and some of the SQL queries here but it does not give this information.

thanks,
 
PREDATAR Control23

The summary table should pretty much have the info you're after. Something like:
select entity as Node,examined as FileCount,end_time as last_backup,bytes/1024/1024/1024 as GB,affected as Files_backed_up,failed as Files_failed from summary where activity='BACKUP'
 
PREDATAR Control23

Wonderful ! Perfect ! Thank you so much !! BTW how do i list the SQL tables I can query ?
 
PREDATAR Control23

Is there a way to combine two node names together and show their totals ? I mean, For eg: SQL DBbackups show two jobs on the same date. I need to combine the jobs to show one node name with total of the two jobs.
 
PREDATAR Control23

so this query give me a list of names....how do i see what under each of those tables ?
 
PREDATAR Control23

Is there a way to combine two node names together and show their totals ? I mean, For eg: SQL DBbackups show two jobs on the same date. I need to combine the jobs to show one node name with total of the two jobs.

I finally got to a TSM server so can test. Something like this can give you the output from multiple backup activities in the last 24 hours, with the most recent end time if there are multiple activities:

Code:
select distinct(entity)as "Node name",sum(examined) as "Files examined",sum(affected) as "Files backed up",sum(failed) as "Files failed",sum(bytes/1024/1024/1024) as "GB backed up",max(end_time) as "Last backup end" from summary where activity='BACKUP' and end_time>=current_timestamp - 24 hours group by entity

illllm said:
so this query give me a list of names....how do i see what under each of those tables ?
Once you get the table name, you can query like this:
Code:
>select colname from syscat.columns where tabname='SUMMARY'
 
PREDATAR Control23

trying to learn this .... could you please help understand --this does not work:

SELECT distinct (Node_Name) as Node_Name, sum(bkbytes_to_replicate/1024/1024/1024) as GB_To_Replicate, sum(bkbytes_replicated/1024/1024/1024) as GB_Replicated from replicationview
 
PREDATAR Control23

I'm away from the server again, but give this a whirl:
SELECT Node_Name, sum(bkbytes_to_replicate/1024/1024/1024) as GB_To_Replicate, sum(bkbytes_replicated/1024/1024/1024) as GB_Replicated from replicationview group by node_name
 
Top