Drive Space Report

[ToMonkey]

Hi, Never used Lansweeper before or SQL and I've put together a report based on others work in the forum plus the standard reports. I want quite a lot of detail to load into a spreadsheet which then provides disaster recovery plans.

Essentially it kind of works but for some machines the drives come out twice so 2 c drives and 2 d drives etc. I have no idea why, could anyone help. I'm brand new to this.



Select Top 1000000 tblAssets.AssetID,
tblAssetCustom.Branchoffice As Facility,
tblAssetCustom.Building As Region,
tblAssetCustom.Department As Country,
tblAssets.AssetName,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
'Disk Size GB',
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
'Disk Free GB',
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As Numeric) -
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As Numeric) As
'Disk Used GB',
tblAssets.IPAddress As 'IP',
tblAssets.Domain,
tblAssets.FQDN,
tblAssetCustom.Custom1 As 'Backup System',
tblAssetCustom.Custom3 As 'Backup Server',
tblAssetCustom.Model,
tblAssets.AssetUnique,
tsysOS.OSname As OS,
Coalesce(SubQuery1.VMwareHost, SubQuery2.HyperVhost) As Host
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Left Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join (Select tblAssets.AssetID,
tblAssets1.AssetName As VMwareHost
From tblAssets
Inner Join tblAssetMacAddress On tblAssetMacAddress.AssetID =
tblAssets.AssetID
Inner Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareGuest.AssetID) SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
tblAssets1.AssetName As HyperVhost
From tblAssets
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Inner Join tblHyperVGuest On TblHyperVGuestNetwork.HyperVGuestID =
tblHyperVGuest.hypervguestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblHyperVGuest.AssetID) SubQuery2 On SubQuery2.AssetID = tblAssets.AssetID
Where tblAssetGroups.AssetGroup = 'Servers' And tblDiskdrives.DriveType = 3
Order By Region,
Country,
Facility,
tblAssets.AssetName,
tblDiskdrives.Caption