So working on a capcity report/dashboard item related to our Datastores. I found the capacity value was stored in bits, so converted that to TB/GB/MB/KB and created a column for that label. I have tried a couple different approaches to Combining the 2 so I would have 1.5TB instead of a 1.5 column and a TB column. Everytime I do a Concat or try to merge the values together, I end up with several floating 0 trailing the value. Any thoughts on how to clean that up? I did steal some thoughts from KMSigma on including a conversion calculator in the script, and I saw a note of him recommending multiplying the values by 1.0 to convert from an integer to decimal, so I have that added in.
I was able to include a Replace before the ConCat to clean it up, but wasn't sure if there was a better/cleaner way?
SELECT [DS].Name
, [DS].DetailsUrl
, [DS].Status
, [DS].ClusterCount
, round([DS].SpaceUtilization,2) as SpaceUtilization
, round([DS].ProvisionedSpaceAllocation,2) as ProvisionedSpaceAllocation
, [DS].FreeSpace
, [DSC].FreeSpaceConvertedValue
, [DSC].FreeSpaceUnit
, Replace(Concat([DSC].FreeSpaceConvertedValue,' ',[DSC].FreeSpaceUnit),'0000 ', ' ') as DisplayFreeSpace
, [DS].Capacity
, [DSC].CapacityConvertedValue
, [DSC].CapacityUnit
, Replace(CONCAT([DSC].CapacityConvertedValue,' ',[DSC].CapacityUnit),'0000 ',' ') as DisplayCapacity
, [DS].IOPSRead
, [DS].IOPSTotal
, [DS].IOPSWrite
, [DS].LatencyRead
, [DS].LatencyTotal
, [DS].LatencyWrite
FROM Orion.Vim.Datastores [DS]
Left Outer Join ( --Converts DataStore Capacity/Free space from Bytes to other values
SELECT [SDS].DataStoreID
,[SDS].Capacity
------------------------------
----------Bytes Calc----------
-- TB: 1024^4 = 1,099,511,627,776
-- GB: 1024^3 = 1,073,741,824
-- MB: 1024^2 = 1,048,576
-- KB: 1024
------------------------------
, CASE
When Sum( 1.0 * [SDS].Capacity) > 1099511627776.0 Then Round(Sum( 1.0 * [SDS].Capacity) / 1099511627776, 2)
When Sum( 1.0 * [SDS].Capacity) > 1073741824.0 then Round(Sum( 1.0 * [SDS].Capacity) / 1073741824, 2)
When Sum( 1.0 * [SDS].Capacity) > 1048576.0 Then Round(Sum( 1.0 * [SDS].Capacity) / 1048576, 2)
When Sum( 1.0 * [SDS].Capacity) > 1024.0 then Round(Sum( 1.0 * [SDS].Capacity) / 1024, 2)
Else 'Bytes'
End as CapacityConvertedValue
, CASE
When Sum( 1.0 * [SDS].Capacity) > 1099511627776.0 Then 'TB'
When Sum( 1.0 * [SDS].Capacity) > 1073741824.0 then 'GB'
When Sum( 1.0 * [SDS].Capacity) > 1048576.0 Then 'MB'
When Sum( 1.0 * [SDS].Capacity) > 1024.0 then 'KB'
Else 'Bytes'
End as CapacityUnit
, [SDS].FreeSpace
, CASE
When Sum( 1.0 * [SDS].FreeSpace) > 1099511627776.0 Then Round(Sum( 1.0 * [SDS].FreeSpace) / 1099511627776, 2)
When Sum( 1.0 * [SDS].FreeSpace) > 1073741824.0 then Round(Sum( 1.0 * [SDS].FreeSpace) / 1073741824, 2)
When Sum( 1.0 * [SDS].FreeSpace) > 1048576.0 Then Round(Sum( 1.0 * [SDS].FreeSpace) / 1048576, 2)
When Sum( 1.0 * [SDS].FreeSpace) > 1024.0 then Round(Sum( 1.0 * [SDS].FreeSpace) / 1024, 2)
Else 'Bytes'
End as FreeSpaceConvertedValue
, CASE
When Sum( 1.0 * [SDS].FreeSpace) > 1099511627776.0 Then 'TB'
When Sum( 1.0 * [SDS].FreeSpace) > 1073741824.0 then 'GB'
When Sum( 1.0 * [SDS].FreeSpace) > 1048576.0 Then 'MB'
When Sum( 1.0 * [SDS].FreeSpace) > 1024.0 then 'KB'
Else 'Bytes'
End as FreeSpaceUnit
From Orion.VIM.DataStores [SDS]
Group By [SDS].DataStoreID, [SDS].Capacity, [SDS].FreeSpace
) As [DSC] on [DS].DataStoreID = [DSC].DataStoreID
WHERE 1=1
-- And [DS].CustomProperties. Like '%Property%' Used for DataStore filtering
And 2=2
