Solved

TSM query for getting backups completed for last 10 days

Posted on 2010-11-09
15
3,256 Views
Last Modified: 2013-11-14
We have to reschedule some of the clients in the Schedules to balance it. For this, first i need to work on the trends for backups. I want to know how can i get the duration of the backups completed for the nodes for last 7 days. Is there any query for that so that i can generate that report & compare.

Thanks
virgo
0
Comment
Question by:virgo0880
  • 7
  • 7
15 Comments
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 250 total points
ID: 34098252
Now that's simple. See attachment.
Duration is in the third column ("Minutes").

The query will give you (in the last two columns) the number of files and number of MB backed up.

Your headline says "10 days", the text of your Q says "7 days". Adjust the number in the second-to-last line ("- 7 days" vs. "-10 days").

Best create a TSM script or a TSM macro.

First create a text file somewhere at your client from the text in the attachment.

Macro: Go to dsmadmc and enter
macro /path/to/textfile

Script: Go to dsmadmc and enter
define script somename File=/path/to/textfile
then enter
run somename

If you have questions about scripts or macros, please let me know (tomorrow).

wmp

select  cast(START_TIME as DATE) DATE,                   -
        cast(START_TIME as TIME) STTIME,                 -
            (END_TIME-START_TIME) minutes as "Minutes",  -
        cast(ENTITY as char(21)) "Node                 ",-
        cast(AFFECTED as dec(7)) Files,                  -
        cast((BYTES/1024/1024) as dec(9,2)) MB           -
 from summary                                            -
 where ACTIVITY='BACKUP'                                 -
 and cast(start_time as date)>=cast(current timestamp as date)-7 days -
 order by 4, 1, 2

Open in new window

0
 

Author Comment

by:virgo0880
ID: 34098506
ok. I will try this tomorrow. Also if I want the same report for the specific date for e.g. 11/01/2010 , wht modifications can be done in the query.

thanks
virgo
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 34100024
See the second-to-last line in the attachment.
select  cast(START_TIME as DATE) DATE,                   -
        cast(START_TIME as TIME) STTIME,                 -
            (END_TIME-START_TIME) minutes as "Minutes",  -
        cast(ENTITY as char(21)) "Node                 ",-
        cast(AFFECTED as dec(7)) Files,                  -
        cast((BYTES/1024/1024) as dec(9,2)) MB           -
 from summary                                            -
 where ACTIVITY='BACKUP'                                 -
 and cast(start_time as date)='2010-11-01'               -
 order by 4, 1, 2

Open in new window

0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Expert Comment

by:renever
ID: 34101071
From the events table it look like this

select domain_name, schedule_name, node_name, actual_start, completed, completed-actual_start Duration from events where  and scheduled_start >= '2010-01-01' and scheduled_start >= Current_timestamp - 240 hours  and status='Completed'

The first test against 2010-01-01 is necessary to show the events in the past

order by can be added to sort the output

Rene
0
 

Author Comment

by:virgo0880
ID: 34117095
Hi wmp,

I have tried your query & it works perfectly for me. Also, i want to generate a report of 10 days where in i want the average of values in the fields for duration,examined,MB etc..how can i do that.

thanks
virgo
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 34118929
Always glad to help:
select                                                                 -
    cast(ENTITY as char(21)) "Node                 ",                  -          
    min(cast(START_TIME as DATE)) "DATE-LO",                           -                      
    max(cast(START_TIME as DATE)) "DATE-HI",                           -                      
    cast(count(*) as dec(4)) "# Runs",                                 -
    avg((END_TIME-START_TIME)minutes) "AVG Minutes",                   -                 
    cast(avg(AFFECTED) as dec(9,2)) "AVG Files",                       -                             
    cast(avg(BYTES)/1024/1024 as dec(10,2)) "AVG MB"                   -                    
from summary                                                           -
where ACTIVITY='BACKUP'                                                -                     
and cast(start_time as date)>=cast(current timestamp as date)-10 days  -                                         
group by Entity                                                        -
order by 1, 2, 3

Open in new window

0
 

Author Comment

by:virgo0880
ID: 34123488
Hi wmp,

Thanks for that, do you have any tutorial /lists for different TSM queries , that would be great help to me.

Thanks
virgo
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 34123767
Hi,


there are no ready-made or standard queries for TSM, at least not from Tivoli/IBM.

TSM's query language is kind of an emulated, somewhat reduced SQL SELECT statement containing many elements of a classic SQL SELECT.

To find out which tables you can query against issue SELECT * from SYSCAT.TABLES
and to find out a table's columns issue SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME = 'tabname'

Here is a neat introduction containing some examples -
http://www.lascon.co.uk/d005104.htm

This link has much more information about TSM than just about SELECT - take a look.

Besides that, HELP SELECT in dsmadmc should also give a basic understanding.

wmp
0
 

Author Comment

by:virgo0880
ID: 34123832
ok i will see into that, thanks for the url. One more thing is I want the report just like the one you gave, but the thing is i want it for the nodes schedule wise i.e. schedule name, node name & then this further report. Can u help me in the query for that.

Thanks
virgo
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 34123914
This has to be drawn from the EVENTS table, but this table contains only timestamps or status flags, not bytes or files.

Joining EVENTS and SUMMARY is not possible.

To get the schedule duration see renever's query in http://#a34101071 above.

If you have further questions, how about opening a new thread?

wmp
0
 

Author Comment

by:virgo0880
ID: 34124711
The above thread link you have given is not opening..?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 34124780
Yes, sorry, don't know what happened. Seems they made a new interface here which needs a bit reworking.

I meant the fourth comment just above, in this  thread, Comment No. 34101071 by renever

0
 

Author Comment

by:virgo0880
ID: 34125079
I worked on the query & got it working . This is the query i customized :

select entity as Node_name, schedule_name, min(cast(START_TIME as DATE)) "DATE-LO",max(cast(START_TIME as DATE)) "DATE-HI", cast(count(*) as dec(4)) "# Runs", avg((END_TIME-START_TIME)minutes) "AVG Minutes", cast(avg(AFFECTED) as dec(9,2)) "AVG Files", cast(avg(BYTES)/1024/1024 as dec(10,2)) "AVG MB" from summary where ACTIVITY='BACKUP' and cast(start_time as date)>=cast(current timestamp as date)-10 days group by schedule_name, Entity order by 2

Also, i want some explainations about the query as I am not that much familier with SQL statements. What do you mean by "cast" & entity here, if you can explain me.

Thanks
virgo
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 34125134
ENTITY is a column of SUMMARY

cast is used for reformatting e.g. a timestamp to DATE, a number to contain a certain amount of decimal places etc.

Your original requirement was a particular query and not an exhaustive SQL seminar.

If you feel your original question answered I think we should finish this thread here.


0
 

Author Comment

by:virgo0880
ID: 34125865
Yes, I agree with you regarding the closing of thread, i was just asking the information pertaining to my query. Thats fine.

thanks
virgo
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
what is the difference between "sudo su" and "su - root" 6 137
Solaris SUN OS 4.1.3 machine clone 3 48
WD MybookLive Data disappear 6 67
Amazon Glacier backup 6 37
VM backup deduplication is a method of reducing the amount of storage space needed to save VM backups. In most organizations, VMs contain many duplicate copies of data, such as VMs deployed from the same template, VMs with the same OS, or VMs that h…
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question