
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-03-2019 11:47 PM
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.
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?
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?
Labels:
- Labels:
-
Report Center
3 REPLIES 3

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-07-2019 03:59 PM
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.
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-15-2021 09:26 PM
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-07-2019 03:35 PM
Cast(tblLinuxHardDisks.Size / 1024 / 1024 As numeric) As TotalSizeGB
That should work.
That should work.
