SELECT Location.SAN, HDS_Device_Assignments_WWN.SID, Location.Alias, Location.Type AS [Array Type], HDS_Device_Assignments_WWN.Hostname, HDS_Device_Assignments_WWN.Device AS DevNum, "Local RAID5" AS Type, "Tier 4" AS Tier, "Silver" AS [Level], Get_Rep_Type("Tier 4") AS [Replication Type], "Local RAID5_" & "Silver" & "_" & [Replication Type] AS Description, [Size(KB)]/(1024*1024) AS [Allocated GB], [HDS_Device_Assignments_WWN].[SID] & "_" & [HDS_Device_Assignments_WWN].[Device] AS Device, [Size(KB)]/(1024*1024) AS [Consumed Capacity (GB)], tblReplicationSite.[Replication Site]
FROM ((HDS_Device_List INNER JOIN HDS_Device_Assignments_WWN ON (HDS_Device_List.Device = HDS_Device_Assignments_WWN.Device) AND (HDS_Device_List.SID = HDS_Device_Assignments_WWN.SID)) INNER JOIN tblReplicationSite ON HDS_Device_Assignments_WWN.SID = tblReplicationSite.Array) INNER JOIN Location ON HDS_Device_Assignments_WWN.SID = Location.SID
GROUP BY Location.SAN, HDS_Device_Assignments_WWN.SID, Location.Alias, Location.Type, HDS_Device_Assignments_WWN.Hostname, HDS_Device_Assignments_WWN.Device, "Local RAID5", "Tier 4", "Silver", Get_Rep_Type("Tier 4"), "Local RAID5_" & "Silver" & "_" & [Replication Type], [Size(KB)]/(1024*1024), [HDS_Device_Assignments_WWN].[SID] & "_" & [HDS_Device_Assignments_WWN].[Device], [Size(KB)]/(1024*1024), tblReplicationSite.[Replication Site]
HAVING (((HDS_Device_Assignments_WWN.SID)="75189" Or (HDS_Device_Assignments_WWN.SID)="78998") AND ((HDS_Device_Assignments_WWN.Hostname) Is Not Null));