TDPVE backup report

hi

select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME,TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME,SUB_ENTITY as NODE_NAME,SCHEDULE_NAME,SUCCESSFUL,TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME,cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-24 hours

this script give me report of all vmware guest that backup throw tsm , i dont need report for all my node , just 2 or 3 node

how can i do it

Thx
TSMILAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TSMILAuthor Commented:
HI
 
i have also this script maybe i can get the info of vmware backup from here

select date(SCHEDULED_START)||' '||time(SCHEDULED_START) "Scheduled Start" , -
       date(ACTUAL_START)||' '||time(ACTUAL_START) "Actual Start" , -
       cast(SCHEDULE_NAME as char(24)) "Schedule Name", -
       cast(NODE_NAME as char(24)) "Node Name", -
       STATUS -
  from VMware -
     where -
       SCHEDULE_NAME not in -
         ('')  -
      and -
       NODE_NAME in -
        ( 'NODENAME' ) -
     and ( -
         (date(SCHEDULED_START)=date(current_timestamp-1 day) and hour(SCHEDULED_START)>=18) -
     or -
         (date(SCHEDULED_START)=date(current_timestamp) and hour(SCHEDULED_START)<7) -
         ) -
  order by SCHEDULED_START

Thx
woolmilkporcCommented:
First to your second post above:

That query is meant to report from the "Events" table. TDPVE reports require "summary_extended" or other tables containing sub_entity info.

<< from VMware <<

"VMware" is not a TSM table, so what you posted will certainly not work.

The query shown in the original question can be modified like this (I'll add a few line continuations for readability):

select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
SUB_ENTITY as NODE_NAME, SCHEDULE_NAME, SUCCESSFUL, -
TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME, -
cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB -
from summary_extended where -
ENTITY in ( 'NODE_NAME1', 'NODE_NAME2', 'NODE_NAME3' )  and -
ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and -
START_TIME>=current_timestamp-24 hours

ENTITY is the data mover node (proxy agent).
If you want to filter for the proxy node target (vSphere data center) or for the virtual machine itself (display name) use AS_ENTITY (proxy target) or SUB_ENTITY (virtual machine), respectively.
TSMILAuthor Commented:
hi

when i run the script i got - No match found using this criteria

but when i run this :

select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME,TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME,SUB_ENTITY as NODE_NAME,SCHEDULE_NAME,SUCCESSFUL,TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME,cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-24 hours

i saw the node informaion
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

woolmilkporcCommented:
It depends on what you mean when saying "Node". See the last part of my comment above.

A query filtering for virtual machine names would look like this:

select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
SUB_ENTITY as NODE_NAME, SCHEDULE_NAME, SUCCESSFUL, -
TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME, -
cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB -
from summary_extended where -
ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and -
SUB_ENTITY in ( 'Virtual_Machine1',  'Virtual_Machine2', 'Virtual_Machine3' ) and -
START_TIME>=current_timestamp-24 hours
TSMILAuthor Commented:
hi

 again i got this message SELECT: No match found using this criteria
TSMILAuthor Commented:
hi

i change to small letter and its work
woolmilkporcCommented:
Sorry, forgot to mention the case sensitivity. My mistake!
TSMILAuthor Commented:
hi

if i want to show this table in this structure it is possible ?

Scheduled Start          Actual Start             Schedule Name                 Node Name                     STATUS                    
--------------------     --------------------     -------------------------     -------------------------     --------------------------
2015-06-02 20.00.00      2015-06-02 20.02.22    Schedule Name        NODE NAME                    Completed                
2015-06-02 20.00.00      2015-06-02 20.00.45    Schedule Name        NODE NAME                    Completed

IT IS POSSIBLE ?

THX
woolmilkporcCommented:
The shown results are from the events table. This table only contains information for proxy targets (vSpehere hosts), not for single virtual machines.
So from summary_extended (which contains virtual machine info) you will only see something like "Failed" or "Succeeded" but not things like "Future" or "Pending". Also, there will be no "scheduled start", only the actual start, and schedule name will be empty if the operation was not scheduled.

So you will just see

Actual Start             Schedule Name                 Node Name                     STATUS  
--------------------     --------------------     -------------------------     --------------
2015-06-02 20.00.00      Schedule Name                 NODE NAME1                   Completed
2015-06-02 20.00.00      Schedule Name                 NODE NAME2                   Failed

Open in new window

If this is OK for you please let me know.
TSMILAuthor Commented:
hi

this is what i see :


   START_TIME: 2015-06-03 18:04:31
   END_TIME: 2015-06-03 18:10:06
   NODE_NAME: node name
   SCHEDULE_NAME: SCHEDULE_NAME
   SUCCESSFUL: YES
   ELAPTIME: 0 00:05:35
    GB: 2.98

can i see it like this
START_TIME          END_TIME             Schedule Name                 Node Name              SUCCESSFUL     ELAPTIME          GB
--------------------     --------------------     -------------------------     -------------------------     --------------------------
2015-06-02 20.00.00      2015-06-02 20.02.22    Schedule Name        NODE NAME                    Completed                
2015-06-02 20.00.00      2015-06-02 20.00.45    Schedule Name        NODE NAME                    Completed

and i can add order by node name to the script

thx
woolmilkporcCommented:
Sorry, I had to remove my comment because I think I got you wrong.
woolmilkporcCommented:
The desired output format will show up when your terminal window is broad enough.

When run as batch and "-displaymode=table" is specified it will also show the desired format.
woolmilkporcCommented:
And for the ordering - add

order by sub_entity

at the end of the last line, or replace

order by start_time

with that subcommand.
TSMILAuthor Commented:
hi

#!/bin/bash -x
ID=
PAS=
TODAY=$(date +"%Y.%m.%d")
HOST=$HOSTNAME
tsm_output=/tmp/sox-client-status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -server= -id=$ID -passw=$PAS -displaymode=table  << EOF >> $tsm_output
select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME, -
TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME, -
SUB_ENTITY as NODE_NAME, SCHEDULE_NAME, SUCCESSFUL, -
TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME, -
cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB -
from summary_extended where -
ACTIVITY_DETAILS='VMware' and -
SUB_ENTITY in ( 'Virtual_Machine1' ) and -
START_TIME>=current_timestamp-24 hours
order by sub_entity
quit
EOF


this is the script that i use

but the data is not in a organize table is there any way to display this in nice table because i need to display this data

thx
woolmilkporcCommented:
START_TIME>=current_timestamp-24 hours
order by sub_entity


The above will throw an error. I thought I told you to add the order statement at the end of (not: after)  the last line?

Either this way:

START_TIME>=current_timestamp-24 hours order by sub_entity

or this way:

START_TIME>=current_timestamp-24 hours -
order by sub_entity


Note the hyphen after "hours".

I never saw dsmadmc ignore "-displaymode=table".

I cannot reproduce your issue, regardless of what I try, sorry.
TSMILAuthor Commented:
hi
i attach 2 files
display is what i got from this - vmware
and dispaly 1 if i run other script and thats want i want to be the display

thx
display1.txt
display.txt
woolmilkporcCommented:
Your second output is a table.

I cannot see this output format there:

START_TIME: 2015-06-03 18:04:31
   END_TIME: 2015-06-03 18:10:06
   NODE_NAME: node name
   SCHEDULE_NAME: SCHEDULE_NAME
   SUCCESSFUL: YES
   ELAPTIME: 0 00:05:35
    GB: 2.98

What I can see is that your columns are far too wide. Did you set "sqldisplaymode wide" somewhere? Default is "narrow"!

I can't believe that this will help, but please add

set sqldisplaymode narrow

on a line of its own just between "/usr/bin/dsmadmc ... ..." and "select ... ...".  No hyphen after "narrow"!!

If this doesn't help we'll have to do some "casting". Perhaps we could make the script more "TSM 6.x" compliant. The original looks quite like TSM 5.x or below.
TSMILAuthor Commented:
hi you are correct if i run this select from TSM this is what i saw
START_TIME: 2015-06-03 18:04:31
   END_TIME: 2015-06-03 18:10:06
   NODE_NAME: node name
   SCHEDULE_NAME: SCHEDULE_NAME
   SUCCESSFUL: YES
   ELAPTIME: 0 00:05:35
    GB: 2.98

but if i run from crontab the display txt is what i got

maybe the script cossing this ?
woolmilkporcCommented:
Your columns are too wide! Did you try setting "sqldisplaymode narrow" (see my last comment above)? But as I already said, I don't think it will help.


Anyway, here is the "casted" version:

#!/bin/bash -x
ID=
PAS=
TODAY=$(date +"%Y.%m.%d")
HOST=$HOSTNAME
tsm_output=/tmp/sox-client-status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -server= -id=$ID -passw=$PAS -displaymode=table  << EOF >> $tsm_output
select date(START_TIME)||' '||time(START_TIME) as START_TIME, -
date(END_TIME)||' '||time(END_TIME) as END_TIME, -
cast(SUB_ENTITY as char(24)) as NODE_NAME,  -
cast(SCHEDULE_NAME as char(24)) as SCHEDULE_NAME, -
cast(SUCCESSFUL as char(10)) as SUCCESSFUL, -
cast(TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as char(12))  as ELAPTIME, -
cast(float(bytes)/1024/1024/1024 as dec(4,2)) as GB -
from summary_extended where -
ACTIVITY_DETAILS='VMware' and -
SUB_ENTITY in ( 'Virtual_Machine1' ) and -
START_TIME>=current_timestamp-24 hours -
order by sub_entity
quit
EOF

I think it will work, but I didn't have the opportunity to test.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TSMILAuthor Commented:
thx

can you explain what is the different please ?
TSMILAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for TSMIL's comment #a40811133

for the following reason:

Number 1
woolmilkporcCommented:
I think you made a mistake when closing this Q! You awarded points to yourself!

I'll object to your closing request, so you can review what you did.
woolmilkporcCommented:
The difference between the TSM <=5 and TSM >= 6 query languages is mainly in date formatting.
Since it's based on DB2 we now have functions to convert a timestanp to a date string, a time string and much more, so we don't need this TO_CHAR and format string method anymore.

Moreover, field widths have changed between >= 5 and >= 6. Many of them are now varchar(256) or higher or even CLOBs instead of char(24) or char(64) as before.

"cast"is used here to shorten columns to a suitable width, but it can also do format conversions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage

From novice to tech pro — start learning today.