Bytes to GB

mbaker_wv

So I am green to SQL and SWQL. Im trying to build a custom report which looks at each Virtual Machine and returns some values. The report generates data but two of these values return Bytes and not GB. I would like to convert them to GB. Is there a quick way to convert the Freespace, and Capacity?

SELECT

OVM.VirtualMachineID,
OVM.Name,
OVM.VirtualVolumes.MountPoint,
OVM.VirtualVolumes.FreeSpace,
OVM.VirtualVolumes.Capacity

 

FROM Orion.VIM.VirtualMachines OVM

 

KMSigma.SWI

However if you are doing this outside of the web writer, then you need to get a little more creative.

SELECT [OVM].VirtualMachineID
     , [OVM].Name
     , [OVM].VirtualVolumes.MountPoint
     , CASE
         -- Terabytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.FreeSpace > 1099511627776.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.FreeSpace / 1099511627776.0, 2), ' TB')
         -- Gigabytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.FreeSpace > 1073741824.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.FreeSpace / 1073741824.0, 2), ' GB')
         -- Megabytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.FreeSpace > 1048576.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.FreeSpace / 1048576.0, 2), ' MB')
         -- Kilobytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.FreeSpace > 1024.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.FreeSpace / 1024.0, 2), ' MB')
         -- Otherwise bytes
         ELSE CONCAT([OVM].VirtualVolumes.FreeSpace, ' B')
       END AS [FreeSizeDataUnits]
     , CASE
         -- Terabytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.Capacity > 1099511627776.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.Capacity / 1099511627776.0, 2), ' TB')
         -- Gigabytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.Capacity > 1073741824.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.Capacity / 1073741824.0, 2), ' GB')
         -- Megabytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.Capacity > 1048576.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.Capacity / 1048576.0, 2), ' MB')
         -- Kilobytes in binary notation (1024)
         WHEN [OVM].VirtualVolumes.Capacity > 1024.0
         THEN CONCAT(ROUND([OVM].VirtualVolumes.Capacity / 1024.0, 2), ' MB')
         -- Otherwise bytes
         ELSE CONCAT([OVM].VirtualVolumes.Capacity, ' B')
       END AS [CapacityDataUnits]
FROM Orion.VIM.VirtualMachines AS [OVM]