‎04-02-2024 05:22 PM
trying to see if anyone can help me on this custom report im trying to create.
requirements are
so far i am able to generate name, # of cpu, memory (but not in GB) and hard disk space but it is shwoing as individual drive. wondering if anyone can help
Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName As Name,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.NrProcessors As NumCpu,
tblAssets.Memory As MemoryGB,
Cast(tblVolume.Capacity / 1024 / 1024 / 1024 As numeric) As HardDiskSizeGB
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVolume On tblAssets.AssetID = tblVolume.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where (tblAssets.IPAddress Like '10.51.%' Or tblAssets.IPAddress Like '10.31.%')
And tblVolume.DriveLetter Is Not Null
Order By Name
‎04-03-2024 05:32 AM - edited ‎04-03-2024 05:33 AM
Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
1024 As bigint)) From tblLinuxHardDisks
Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
(tblLinuxHardDisks.filesystem Like '/dev/s%' Or
tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
1024 As bigint)) From tblDiskdrives
Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize
‎04-02-2024 05:37 PM
reason for this report is for internal charges/cost allocation.
thanks
‎04-02-2024 05:27 PM
i can use Cast(tblAssets.Memory / 1024 As numeric) As MemoryGB to convert the memory but it would not let me to do the following cast for disk space
Cast(tblVolume.Capacity / 1024 / 1024 / 1024 As numeric) As HardDiskSizeGB
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now