TSM command to get the count of backup in GB's?

Question

Can some one please provide me the TSM command to get the count of backup in GB's for last 30 days?

An example of SQL command which we can execute on TSM to get the data size for last 24 hours:

SELECT substr(entity,1,20) AS "Node", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB Bkp"
FROM summary
WHERE activity = 'BACKUP' AND start_time>=current_timestamp - 24 hours
GROUP BY entity
order by 2 desc

Which dbms are you using? (TSM and backups are not covered by the ANSI SQL standard...) — jarlh, Nov 10 '16 at 08:47
actually i need sql command to get the data.... as TSM can execute sql commands — vikram, Nov 10 '16 at 08:53
an example of SQL command which we can execute on TSM to get the data size for last 24 hours — vikram, Nov 10 '16 at 08:53
SELECT substr(entity,1,20) AS "Node", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB Bkp" FROM summary WHERE activity='BACKUP' AND start_time>=current_timestamp - 24 hours GROUP BY entity order by 2 desc — vikram, Nov 10 '16 at 08:54

score 0 · Answer 1 · answered Nov 10 '16 at 11:57

I haven't worked on TSM for many years, but can you not simply change the number of hours in the WHERE clause to 24 hours * 30 days?

SELECT substr(entity,1,20) AS "Node", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB Bkp"
FROM summary
WHERE activity = 'BACKUP' AND start_time>=current_timestamp - 720 hours
GROUP BY entity
order by 2 desc

To get GB, divide the bytes again by 1024 in the SELECT clause.

score 0 · Answer 2 · edited Sep 20 '21 at 20:12

You might try using this SQL query; just change the 7 days for 30 days. Hope this works. Here is a useful link with many SQL queries. https://github.com/thobiast/tsm_sql

12.3. Statistics of archive, backup, restore and retrieve operations per node in the last 7 days (GB)

TSM version 6

SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) -
FROM summary 
WHERE DAYS(current_timestamp)-DAYS(end_time)<=7 and ( activity='ARCHIVE' OR -
  activity='BACKUP' OR activity='RESTORE' OR activity='RETRIEVE' ) 
GROUP BY entity, activity

TSM version 5

SELECT entity, activity, CAST(FLOAT(SUM(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) -
FROM summary 
WHERE end_time>current_timestamp-(7)DAY and ( activity='ARCHIVE' OR -
  activity='BACKUP' OR activity='RESTORE' OR activity='RETRIEVE' ) 
GROUP BY entity, activity

TSM command to get the count of backup in GB's?

2 Answers2