Duplicate Records in disk list

[LCadmin]

I'm looking for a way to generate a list of all our server (all starting with the name "Server" or "VEEAM" to display there disk size and free disk space (only local disks).

I keep getting the result i want accept all records are duplicated ( so 2 times the C:, 😧 etc.)

The code i'm using is:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tDiskdrives.freespace,
tDiskdrives.Size,
tblDiskdrives.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (Select tblDiskdrives.AssetID,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 /
1024 As Numeric) As freespace,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As
Size
From tblDiskdrives
Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID =
tDiskdrives.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where (tblAssets.AssetName Like '%Server%' And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Like '%VEEAM%')
Order By tblAssets.AssetName

[MikeMc · Accepted solution]

I took away the extra join you had for tblDiskDrives.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tDiskdrives.freespace,
  tDiskdrives.Size,
  tDiskdrives.Caption
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (Select tblDiskdrives.AssetID,
    tblDiskdrives.Caption,
    Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 /
    1024 As Numeric) As freespace,
    Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As Numeric) As
    Size
  From tblDiskdrives
  Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID =
    tDiskdrives.AssetID
Where (tblAssets.AssetName Like '%Server%' Or tblAssets.AssetName Like
    '%VEEAM%') And tblAssetCustom.State = 1
Order By tblAssets.AssetName