remove repeated users in lansweeper report

[Samane_rashidi]

I produce a report for users who have Microsoft office software, but each user is repeated more than 10 times because they have different types of Microsoft office software. Is there a way that I can limit them. I want each user will be appear only once.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Description,
  LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') + N'  ' +
  Coalesce(tblAssetCustom.Model, N''))) As CompModel,
  tsysOS.OSname,
  tblLanguages.Language,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Memory,
  tblAssets.Processor,
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  [HDD Free],
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [HDD Size],
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName
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 tblLanguages On tblLanguages.LanguageCode =
    tblOperatingsystem.OSLanguage
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID,
  tblSoftwareUni
Where tblAssets.Lastseen > GetDate() - 60 And tblSoftwareUni.softwareName Like
  '%Microsoft Office%' And tblSoftwareUni.softwareName Not Like
  'Microsoft Office Professional Plus 2016' And tblAssetCustom.State = 1 And
  tblComputersystem.Domainrole <= 1 And tblDiskdrives.Caption = 'C:'

[RCorbeil]

You've got two problems. First, you haven't linked tblAssets to tblSoftwareUni, so you're going to get a mess of results. You need to link tblAssets -> tblSoftware -> tblSoftwareUni.

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 tblLanguages On tblLanguages.LanguageCode = tblOperatingsystem.OSLanguage
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID

Second, too many pieces of software are named "Microsoft Office something-or-other". Blame Microsoft's lack of creativity for that one. You're going to need to either set your sites on specific names or create a bunch of "not X", "not Y", "not Z" conditions to eliminate the stuff you know you don't want to see. This has come up more than once before. Take at look at this thread, for example.