How to see total GB backed up and pipe it to a text file.

[TSMJay]

Hi all,

I am currently trying to justify to my company why I need more LTO drives to make operations a bit smoother.



I found the following SQL query command to give me the total GB backed up in the last 24 hours.



select sum(cast(bytes/1024/1024/1024 as decimal(6,2))) TGBU from summary where start_time>=current_timestamp - 24 hours and activity='BACKUP'



I am not very good with SQL so be nice.

Question is how can I pipe this to a text file or CSV file so I can plot it on a graph?

 

[kleist]

I use the TSM Operational Reporting utility for this (and other) reasons. The default options in the custom summary section should show you what you want, but if you need to get more specific look at the node activity summary and even more detailed is the Backup Archive Client Backup Summary.



I installed the tool on a server that is running IIS, and set a schedule to run the report every day at 8:00AM and then send the report to the web page. When you set up the report, there is an option at the bottom that asks how many web revisions you want to keep of the report. I keep 30 days right now, but may be changing that to keep the max of 99 (so I can see trends over a 3 month time period.)

 

[custew]

When you want to put things in a CSV, I've found the best way for me is to use dsmadmc in "comma" mode.



from the command line...



dsmadmc -comma -id=youradminid -pass=yourpassword select a bunch of stuff > somefile



Then, open the file in Excell and go to town.



Curtis

 

[computeraces]

I use the following...



select cast(sum(bytes)/1024/1024/1024 as decimal(10,2)) as GB from -

summary where activity='BACKUP' and start_time >= (current_timestamp - 24 hours )



It's probably the same thing. I also do not know SQL and I'm running TSM 5.2.3.3, but this should work with all versions of 5. I have this saved in the "server command scripts" area of the TSM web based administration (or whatever the proper name is). Yeah, I'm a newbie at this and don't use all the proper names of things. I have a lot of scripts saved there and just run them from time to time. I run this one every day. We have about 30 clients and one TSM server and we backup about 50 GB's a night. We have a Dell PowerVault 136T with 6 tape drives.



Hope this helps.



Cory

 

[TSMJay]

Thanks for your help.



The thing is I backup about 30+ nodes and the total average GB's backed up is about 300GB a night. I wanted to automate the proccess so I could prove to the people at my work, who allocate the monies, how much I backup. (I need more drives!!!!!)



If anyone could help me with a script that writes directly to a file that would be awesome.



Anyway everyone keep on thinkin about this.



BTW Happy New Year Everyone!!

 

[may]

happy new year all,



so first creat this script on your tsm server called backup_activ:



set sqlmathmode round

set sqldatetimeformat eur

select entity as "Node", date(start_time) as "Date", -

time(start_time) as "St Time", time(end_time) as "End Time", -

substr(cast(end_time-start_time as varchar(17)),3,8) as "Elapsed", -

cast(sum(affected) as varchar(5)) as "Obj", -

case -

when sum(bytes) > 1073741824 then -

cast(sum(bytes)/1073741824 as varchar(5))||' Gb' -

when sum(bytes) > 1048576 then -

cast(sum(bytes)/1048576 as varchar(5))||' Mb' -

when sum(bytes) > 1024 then -

cast(sum(bytes)/1024 as varchar(5))||' Kb' -

else cast(sum(bytes) as varchar(5)) -

end as "Size" -

from summary -

where current_date-date(start_time)='1' and activity='BACKUP' and -

successful='YES' -

group by start_time,end_time, entity

select cast((sum(affected)) as varchar(25)) as "Total No. Objects Backed Up", -

case -

when sum(bytes) > 1073741824 then -

cast(sum(bytes)/1073741824 as varchar(10))||' Gb' -

when sum(bytes) > 1048576 then -

cast(sum(bytes)/1048576 as varchar(10))||' Mb' -

when sum(bytes) > 1024 then -

cast(sum(bytes)/1024 as varchar(10))||' Kb' -

else cast(sum(bytes) as varchar(10)) -

end as "Total Size" -

from summary where current_date-date(start_time)='1' and activity='BACKUP' and successful='YES'







then go on a clint where u installed the admin consol, from there do use this command:



dsmadmc -id=xxxx -password=xxxx run backup_activ > c:\backup.txt



or



dsmadmc -comma -id=xxxx -password=xxxx run backup_activ > c:\backup.txt , if u want to put it in a excel!



hope it will help

 

[nbs03]

You might want to check out TSMManager. It has a 30 day free trial and produces some nice graphics which visually depict how busy your tape drives are, disk pool utilization and a few other informative statistics. You can install it on a Windos workstation in a few minutes and have some informative trends recorded by the end of the week.



Have a nice day.

Neil