ADSM-L

FW: Select Statement: The ANSWER (GROUP BY)

2002-12-19 03:57:33
Subject: FW: Select Statement: The ANSWER (GROUP BY)
From: "Seay, Paul" <seay_pd AT NAPTHEON DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Tue, 17 Dec 2002 16:11:39 -0500
This was what I finally provided to the person asking the question.

Try this as a define script input through the browser.

select summary.entity as ""NODE NAME"", nodes.domain_name as ""DOMAIN"",
nodes.platform_name as ""PLATFORM"", cast((cast(sum(summary.bytes) as float)
/ 1024 / 1024) as decimal(10,2)) as MBYTES , count(*) as ""CONNECTIONS"",
cast (client_version as char(1)) || '.' || cast (client_release as char(1))
|| '.' || cast (client_level as char(1)) || '.' || trim(cast(client_sublevel
as
char(2))) as ""Level"" from summary ,nodes where
summary.entity=nodes.node_name and summary.activity='BACKUP' and start_time
>current_timestamp - 1 day group by entity, domain_name, platform_name,
client_version, client_release, client_level, client_sublevel order by
MBytes desc

Make sure when you paste it in the window that you take all the returns out.
There should be only one line when you get done.

It worked for me.

If you do a q script f=d this is what you will see if it is right:

tsm: TSMPRD00>q script summary_profile f=d

                          Name: SUMMARY_PROFILE
                   Line Number: Description
                       Command: Summary of backups and levels Last Update by
(administrator): PDS00
         Last Update Date/Time: 12/12/2002 17:14:57

                          Name: SUMMARY_PROFILE
                   Line Number: 1
                       Command: select summary.entity as "NODE NAME",
                                 nodes.domain_name as "DOMAIN",
                                 nodes.platform_name as "PLATFORM",
                                 cast((cast(sum(summary.bytes) as float) /
1024
                                 / 1024) as decimal(10,2)) as MBYTES ,
count(*)
                                 as "CONNECTIONS", cast (client_version as
                                 char(1)) || '.' || cast (client_release as
                                 char(1)) || '.' || cast (client_level as
                                 char(1)) || '.' ||
trim(cast(client_sublevel
                                 as char(2))) as "Level" from summary ,nodes
                                 where summary.entity=nodes.node_name and
                                 summary.activity='BACKUP' and
                                 start_time>current_timestamp - 1 day group
by
                                 entity, domain_name, platform_name,
                                 client_version, client_release,
client_level,
                                 client_sublevel order by MBytes desc Last
Update by (administrator): PDS00
         Last Update Date/Time: 12/12/2002 17:14:57



Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: RLochhead AT cse-insurance DOT com [mailto:RLochhead AT cse-insurance DOT 
com]
Sent: Thursday, December 12, 2002 3:26 PM
To: Seay, Paul
Subject: RE: Select Statement: The ANSWER



Thanks, I will try the command line.





                    "Seay, Paul"

                    <seay_pd@napt        To:
"'RLochhead AT cse-insurance DOT com'" <RLochhead AT cse-insurance DOT com>
                    heon.com>            cc:

                                         Subject:     RE: Select Statement:
The ANSWER
                    12/12/2002

                    12:14 PM







OK,
Try pasting it into the command first and executing it.  Have you ever used
the admin GUI on Windows?  It really works well for stuff like this.

The problem is, in a define script all of the double quotes have to be keyed
twice.

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: RLochhead AT cse-insurance DOT com [mailto:RLochhead AT cse-insurance DOT 
com]
Sent: Thursday, December 12, 2002 2:01 PM
To: Seay, Paul
Subject: RE: Select Statement: The ANSWER



Paul,

if I copy the select script into a define command script window with no
modifications, I get a result when I click on finish stating, page cannot be
displayed.  So I was wondering what if any modifications on the variables do
I have to do?  I am running Win 2k on TSM server, TSM client is 5.1.5.2.

Thanks,  Ron




                    "Seay, Paul"

                    <seay_pd@napt        To:
"'RLochhead AT cse-insurance DOT com'" <RLochhead AT cse-insurance DOT com>
                    heon.com>            cc:

                                         Subject:     RE: Select Statement:
The ANSWER
                    12/12/2002

                    10:43 AM







Describe what you mean by not getting anywhere.  Do you mean it never
responds, gets a return code of 11, return code of 3, or what?

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: RLochhead AT cse-insurance DOT com [mailto:RLochhead AT cse-insurance DOT 
com]
Sent: Thursday, December 12, 2002 12:41 PM
To: seay_pd AT naptheon DOT com
Subject: Select Statement: The ANSWER



Paul,

I am trying to use this select statement but am not getting anywhere.  Is
there a variable that I am suppose to change?  Or can I run it as stated? I
am in Win enviroment running TSM client 5.1.5.2.

Thanks,
Ron





                    "Seay, Paul"

                    <seay_pd@NAPT        To:     ADSM-L AT VM.MARIST DOT EDU

                    HEON.COM>            cc:

                    Sent by:             Subject:     Re: Select Statement:
The ANSWER
                    "ADSM: Dist

                    Stor Manager"

                    <ADSM-L AT VM DOT MA

                    RIST.EDU>





                    12/11/2002

                    11:07 PM

                    Please

                    respond to

                    "ADSM: Dist

                    Stor Manager"







select summary.entity as "NODE NAME", nodes.domain_name as "DOMAIN",
nodes.platform_name as "PLATFORM", cast((cast(sum(summary.bytes) as float) /
1024 / 1024) as decimal(10,2)) as MBYTES , count(*) as "CONNECTIONS", cast
(client_version as char(1)) || '.' || cast (client_release as char(1)) ||
'.' || cast (client_level as char(1)) || '.' || trim(cast(client_sublevel as
char(2))) as "Level" from summary ,nodes where
summary.entity=nodes.node_name and summary.activity='BACKUP' and start_time
>current_timestamp - 1 day group by entity, domain_name, platform_name,
client_version, client_release, client_level, client_sublevel order by
MBytes desc

I had always wondered if this could be done.  I played with it until I
figured it out.  It was just a matter of looking at it from a group by point
of view.  The only problem is it does not fit across the screen.

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: Gill, Geoffrey L. [mailto:GEOFFREY.L.GILL AT SAIC DOT COM]
Sent: Wednesday, December 11, 2002 10:56 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Select Statement


There are so many replies to this that look like contradictions to each
other that I'd like to look at this further on my system. I am not a SQL
guru by any means, I can't even seem to combine a couple of statements to
get what I need. Hopefully someone can help me(us) out here. I do an audit
license daily so this should come back with good, or in this case, bad
results.

When I try to combine these to get an output I always get a statement error.
I want to find out what levels are having the problem. I think it's
ridiculous that this does not work for "ALL" client levels. It always did in
the past and I can't understand why I have to now upgrade possibly 80 some
nodes to fix it. Why doesn't IBM fix their server code instead?

Could someone please combine these to get the necessary output? Client
level, node name, amount backed up and I think group by client level might
work.

Select for client level:

select node_name,cast (client_version as char(1)) || '.' || cast
(client_release as char(1)) || '.' || cast (client_level as char(1)) || '.'
|| trim(cast(client_sublevel as char(2))) as "Level" from nodes order by
|| 2,1


Select for amount backed up:

select summary.entity as "NODE NAME", nodes.domain_name as "DOMAIN",
nodes.platform_name as "PLATFORM", cast((cast(sum(summary.bytes) as float) /
1024 / 1024) as decimal(10,2)) as MBYTES , count(*) as "CONNECTIONS" from
summary ,nodes where summary.entity=nodes.node_name and
summary.activity='BACKUP' and start_time >current_timestamp - 1 day group by
entity, domain_name, platform_name order by MBytes desc


Geoff Gill
TSM Administrator
NT Systems Support Engineer
SAIC
E-Mail:   gillg AT saic DOT com
Phone:  (858) 826-4062
Pager:   (877) 905-7154



> -----Original Message-----
> From: Michel Engels [mailto:Michel.Engels AT DEVOTEAM DOT BE]
> Sent: Wednesday, December 11, 2002 6:22 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Re: Select Statement
>
>
> When I do remember well this problem appeared when I installed TSM
> Clients 5.1.0. There was a maintenance (I do not remember if it was
> 5.1.2 or 5.1.3) that solved the problem. Be sure you do an "audit
> license" regularly. I did not install a V5.1.5 yet, so I do not know
> if the problem reappears in that version.
>
> Hope this helps,
>
> Michel Engels
> Software Consultant
> Devoteam Belgium
>
>
>
>
>
>
> "Gill, Geoffrey L." <GEOFFREY.L.GILL AT SAIC DOT COM> on 12/10/2002 11:16:02
> PM
>
> Please respond to "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
>
> To:   ADSM-L AT VM.MARIST DOT EDU
> cc:    (bcc: Michel Engels/BE/Devoteam)
>
> Subject:  Select Statement
>
>
>
> I got this somewhere and I know it used to work on my 4x server. Now
> that I'm on 5.1.5.2 I get a lot of nodes report back with 0 Megabytes
> but they obviously sent files.
>
> Anyone who can make it work since my SQL stills are pretty much non
> existant?
>
> /*--------------------------------------------*/
> /* Specify a date on the run line as follows      */
> /* run q_backup 2001-09-30                        */
> /*--------------------------------------------*/
> select entity as node_name, date(start_time) as date, - cast(activity
> as varchar(10)) as activity, time(start_time)as start, -
> time(end_time) as end, cast(bytes/1000000 as decimal(6,0))as
> megabytes, - cast(affected as decimal(7,0)) as files, successful from
> summary - where date(start_time)='$1' and activity='BACKUP' order by
> megabytes desc
>
>  NODE_NAME: NODEA
>       DATE: 2002-12-09
>   ACTIVITY: BACKUP
>      START: 20:00:51
>        END: 20:48:07
> MEGABYTES: 0
>      FILES: 100
> SUCCESSFUL: YES
>
>
>
> This one does the same thing with some nodes. It also only reports
> about half the nodes even though it looks like it's supposed to be
> going back a full day.
>
> NODEA           NT_DOM                 WinNT
>       0.00
> 1
> select summary.entity as "NODE NAME", nodes.domain_name as "DOMAIN",
> nodes.platform_name as "PLATFORM",
> cast((cast(sum(summary.bytes) as float) / 1024 / 1024) as
> decimal(10,2)) as MBYTES , count(*) as "CONECTIONS" from summary
> ,nodes where summary.entity=nodes.node_name and
> summary.activity='BACKUP' and start_time >current_timestamp - 1 day
> group by entity, domain_name, platform_name order by MBytes desc
>
> A better one would also work.
> Geoff Gill
> TSM Administrator
> NT Systems Support Engineer
> SAIC
> E-Mail:    <mailto:gillg AT saic DOT com> gillg AT saic DOT com
> Phone:  (858) 826-4062
> Pager:   (877) 905-7154
>

<Prev in Thread] Current Thread [Next in Thread>