Duplicate entires

[chrisjones]

Hi im having an issue with duplicate entries on tblSoftware. Essentially i want to show all the software installed on the various computers and who was the last user to log on. I have tried something similar to the link below but with no success. Any ideas appraciated

Thanks in advance

http://www.lansweeper.com/Forum/yaf_postst10786_Adding-Active-Directory-user-information-to-a-report.aspx#post40397


Select Top 1000000 tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblOperatingsystem.Caption As [OS name],
tblAssets.Domain,
tblAssets.Lastseen,
tblAssetCustom.Department,
tblADusers.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Full Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName

[Susan_A · Accepted solution]

Your report includes one of the software tables, but doesn't list any of the software fields, which is why the output looks strange. You're getting a line for every software package, but the actual software name isn't listed. This should provide better results:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tblOperatingsystem.Caption As [OS name],
  tblAssets.Domain,
  tblAssets.Lastseen,
  tblAssetCustom.Department,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblADusers.Name,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblADusers On tblAssets.Username = tblADusers.Username And
    tblAssets.Userdomain = tblADusers.Userdomain
  Left Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
  Left Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  Software