chad_small
ADSM.ORG Moderator
- Joined
- Dec 17, 2002
- Messages
- 2,262
- Reaction score
- 52
- Points
- 0
- Location
- Gilbert, AZ
- Website
- www.tsmadmin.com
I am trying to create a 30 day report that gathers the sum of the data for specific nodes that shows the following
NODE NAME, FILES BACKED UP, TOTAL MB BACKED UP, TOTAL FILES IN TSM, CURRENT OCCUPANCY IN TSM
I am using the following select and the sum of the NUM_FILES and LOGICAL_MB is not accurate due to my join being off. If anyone knows what else I could do to make it accurate then I'd appreciate the help.
select -
varchar(a.node_name,45) as entity, -
cast(sum(b.bytes/1073741824) as decimal(12,2)) AS GB_BACKED_UP, -
cast(sum(b.affected)as dec(12,0)) as AFFECTED, -
cast(sum(a.NUM_FILES)as dec(15,0)) as TOTAL_FILES_STORED, -
cast(sum(a.LOGICAL_MB/1024)as dec(12,2)) as GB_TSM_OCCUPANCY -
from occupancy a, summary b -
where -
a.node_name=b.entity -
and a.node_name in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and b.end_time>=(current_timestamp - 30 days) -
and b.activity='BACKUP' -
group by a.node_name -
order by a.node_name
I've tried different orders for the tables and used entity instead of node_name but nothing I adjust changes the incorrect results. (It's summing more records than it should for the occupancy fields).
NODE NAME, FILES BACKED UP, TOTAL MB BACKED UP, TOTAL FILES IN TSM, CURRENT OCCUPANCY IN TSM
I am using the following select and the sum of the NUM_FILES and LOGICAL_MB is not accurate due to my join being off. If anyone knows what else I could do to make it accurate then I'd appreciate the help.
select -
varchar(a.node_name,45) as entity, -
cast(sum(b.bytes/1073741824) as decimal(12,2)) AS GB_BACKED_UP, -
cast(sum(b.affected)as dec(12,0)) as AFFECTED, -
cast(sum(a.NUM_FILES)as dec(15,0)) as TOTAL_FILES_STORED, -
cast(sum(a.LOGICAL_MB/1024)as dec(12,2)) as GB_TSM_OCCUPANCY -
from occupancy a, summary b -
where -
a.node_name=b.entity -
and a.node_name in (select node_name from nodes where domain_name<>'MN-STD-POL-DOM') -
and b.end_time>=(current_timestamp - 30 days) -
and b.activity='BACKUP' -
group by a.node_name -
order by a.node_name
I've tried different orders for the tables and used entity instead of node_name but nothing I adjust changes the incorrect results. (It's summing more records than it should for the occupancy fields).