SQL help on TSM 6...

[cwilloug]

I was using the old MyTSMReports on my TSM 5.5 server, and liked several of the reports I was able to generate with that product.

I'm trying to get my daily backup summary for a 24 hour period,, but for a multi-days or weeks,, not a summary of those days.. if that makes sense.

Any help?

tsm: TSM6>SELECT activity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,
2)) as -
cont> "GB" FROM summary WHERE activity<>'TAPE MOUNT' AND activity<>'EXPIRATION
' -
cont> AND end_time>current_timestamp-24 hours GROUP BY activity

ACTIVITY: BACKUP
GB: 1928.31

ACTIVITY: FULL_DBBACKUP
GB: 59.49

ACTIVITY: MIGRATION
GB: 1279.60

ACTIVITY: RESTORE
GB: 0.00

ACTIVITY: STGPOOL BACKUP
GB: 1739.78


tsm: TSM6>SELECT activity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,
2)) as -
cont> "GB" FROM summary WHERE activity<>'TAPE MOUNT' AND activity<>'EXPIRATION
' -
cont> AND end_time>current_timestamp-96 hours GROUP BY activity

ACTIVITY: BACKUP
GB: 5520.73

ACTIVITY: DELETE FILESPACE
GB: 0.00

ACTIVITY: FULL_DBBACKUP
GB: 229.99

ACTIVITY: MIGRATION
GB: 5038.44

ACTIVITY: OFFSITE RECLAMATION
GB: 164.61

ACTIVITY: RESTORE
GB: 0.00

ACTIVITY: STGPOOL BACKUP
GB: 5681.92

 

[chad_small]

I didn't understand this part:

I'm trying to get my daily backup summary for a 24 hour period,, but for a multi-days or weeks,, not a summary of those days.. if that makes sense.

That didn't make sense, do you want to change the query to days or weeks?

 

[cwilloug]

What I would like is a daily summary,, something similar to this:

 

[cwilloug]

What I found (in my first post) is that I can get the data for a 24 hour period that I want,,, but if I want a summary for the last 7 days or 14 days I get all the days, not day 1 summary, day 2 summary, day 3 summary, etc...

 

[cwilloug]

Found it!!........

tsm: TSM6>SELECT DATE(SUMMARY.START_TIME) AS Day, Sum(SUMMARY.BYTES)/10737
41824 AS GB from summary WHERE (activity='BACKUP' or activity like 'NAS BACKUP*'
or activity='ARCHIVE') GROUP BY DATE(SUMMARY.START_TIME)

DAY GB
----------- ---------------------
2011-05-10 1838
2011-05-11 2042
2011-05-12 612
2011-05-13 577
2011-05-14 129
2011-05-15 253
2011-05-16 1331
2011-05-17 310
2011-05-18 1353
2011-05-19 1519
2011-05-20 848
2011-05-21 879
2011-05-22 1372
2011-05-23 556
2011-05-24 1115
2011-05-25 3391
2011-05-26 1684
2011-05-27 2043
2011-05-28 1722
2011-05-29 1476
2011-05-30 1296
2011-05-31 1258
2011-06-01 1907
2011-06-02 2590
2011-06-03 1878
2011-06-04 1585
2011-06-05 1067
2011-06-06 1234
2011-06-07 1934
2011-06-08 3050