Remove duplicate

[tboisver]

Hi there !


I've seen on some other post people asking for help on removing duplicate. I tried to replicate some of the answer given but I'm not really good with these queries. Can someone help me remove duplicate from this report either by assetname or username.

Thanks,


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysOS.Image As icon,
tblAssetCustom.State
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblSoftwareUni.softwareName Like 'Microsoft Office 365%' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
Software

[Nick_VDB · Accepted solution]

You can try the report below, we simply added a distinct to it. Do note that it is expected behaviour to get back multiple rows if there are multiple rows stored in the DB for the same software. This information is scanned on the target machine so it may be possible the machine has the same software installed.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysOS.Image As icon,
  tblAssetCustom.State
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblSoftwareUni.softwareName Like 'Microsoft Office 365%' And
  tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Software