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
>
|