All Assets Report

[mmeetze]

We are implementing an Asset Management software and I have been asked to pull in a lot of information. Here is my query however I am returning the same machine literally thousands of times, is there a way to only list each machine only once?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.Image As icon,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Memory,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
tblAssets.Uptime,
tblAssets.SP,
tblBIOS.Version,
tblCPlogoninfo.logontime,
tblFloppy.SerialNumber
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetID

[mmeetze]

Thanks Mike, that will work, i had a feeling it would be one of the tables containing multiple entries but I wasn't sure exactly which one it was.

[MikeMc · Accepted solution]

I removed your joins to the tblCPlogoninfo and tblFloppy tables. These tables can have multiple references to a single asset causing multiple results for the same asset.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.Image As icon,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Memory,
Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
numeric) As nvarchar) As [Disk size],
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssetCustom.Serialnumber As Serial,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Lastseen,
tblAssets.Uptime,
tblAssets.SP,
tblBIOS.Version
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetID