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:[EMAIL PROTECTED]] Sent: Wednesday, December 11, 2002 10:56 AM To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] Phone: (858) 826-4062 Pager: (877) 905-7154 > -----Original Message----- > From: Michel Engels [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, December 11, 2002 6:22 AM > To: [EMAIL PROTECTED] > 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." <[EMAIL PROTECTED]> on 12/10/2002 11:16:02 > PM > > Please respond to "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] > 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:[EMAIL PROTECTED]> [EMAIL PROTECTED] > Phone: (858) 826-4062 > Pager: (877) 905-7154 >
