Linux Disk Size Report

[tcilmo]

I have build a Linux disk size report, but the size is calculated in MB's. I would like to have the size appear as GB's. How would I go about doing so? Here is my base code.

Select Top 1000000 tblAssets.AssetID,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblLinuxSystem.OSRelease,
  tblAssets.Memory,
  tblAssets.NrProcessors,
  tblLinuxHardDisks.Size
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID
Where tsysAssetTypes.AssetTypename <> 'router'

I have tried changing

tblLinuxHardDisks.Size

to:

Cast(Cast(tblLinuxHardDisks.Size As bigint) / 1024 / 1024 / 1024 As numeric) As TotalSizeGB

However, I receive this error: Error converting data type nvarchar to bigint.

Does anyone have any idea how to get the conversion to work? Thoughts?

[tcilmo]

I tried:

Cast(tblLinuxHardDisks.Size / 1024 / 1024 As numeric) As TotalSizeGB

and got the error:

Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '2147483648' overflowed an int column.

I also discovered that I have a few Linux assets that are reporting disk size as "1K-Blocks", this might be a problem.

97605	86171	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2017-06-14 17:34:14.910
189963	118969	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2018-01-16 11:41:05.890
504822	331371	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2018-10-02 10:10:11.290
667996	360675	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2019-01-03 10:02:28.820

[rader]

tcilmo wrote:
I tried:

Cast(tblLinuxHardDisks.Size / 1024 / 1024 As numeric) As TotalSizeGB

and got the error:

Msg 248, Level 16, State 1, Line 1
The conversion of the nvarchar value '2147483648' overflowed an int column.

I also discovered that I have a few Linux assets that are reporting disk size as "1K-Blocks", this might be a problem.

97605	86171	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2017-06-14 17:34:14.910
189963	118969	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2018-01-16 11:41:05.890
504822	331371	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2018-10-02 10:10:11.290
667996	360675	Filesystem	1K-blocks	Used	Available	Use%	Mounted on	2019-01-03 10:02:28.820

I too was having a heck of a time trying to convert my nvarchar to numeric and into GB for my reports and charts. Here's my code to fix that conversion problem.

Cast((Convert(numeric(12),tblLinuxHardDisks.Size) / 1024 / 1024) As numeric)
As TotalGB

Nearest I can understand it is that you have to convert tblLinuxHardDisks.Size (and .Used and .Available in my case - I wanted all three) to numeric(##), then divide by 1024 twice and then Cast as TotalGB (or UsedGB and FreeGB in my case).

Here's my code for my chart version:

Select Top 1000000 tblAssets.AssetName,
  Cast((Convert(numeric(12),tblLinuxHardDisks.Size) / 1024 / 1024) As numeric)
  As TotalGB,
  Cast((Convert(numeric(12),tblLinuxHardDisks.Used) / 1024 / 1024) As numeric)
  As UsedGB,
  Cast((Convert(numeric(12),tblLinuxHardDisks.Available) / 1024 /
  1024) As numeric) As FreeGB
From tblLinuxHardDisks
  Inner Join tblAssets On tblLinuxHardDisks.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Where tblLinuxHardDisks.MountedOn Like '/volume_' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

Here's the screenshot of the completed chart with a report following without the conversion. One Synology NAS has two volumes and thus the duplicated name. Also the reports only show the /volume_ of the NAS's in question. This limits the system partitions and only shows the partitions I'm interested in.

[Esben_D]

Cast(tblLinuxHardDisks.Size / 1024 / 1024 As numeric) As TotalSizeGB

That should work.