Microsoft Office Version Audit Report

[julianhaines]

I am trying to run a report on Office versions and found a report here https://www.lansweeper.com/report/microsoft-office-version-audit/ When I run the report all my computers show up multiple times, I am not great at SQL but I think it has to do with only showing unique assets but don't know how to modify the SQL.

Does anyone know how to modify or have working SQL?

Thanks

[KevinA-REJIS]

Turns out there's a small error in line 32 of the posted report, here's my fixed version:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  tblsoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.Version As OSVersion,
  tblAssets.SP,
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblsoftware On tblAssets.AssetID = tblsoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblsoftware.softID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
      Max(tblErrors.Teller) As ErrorID
    From tblErrors
    Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
      ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
      tblErrors.ErrorType
Where (tblSoftwareUni.softwareName Like '%Microsoft Office%20[0-9][0-9]%' Or
    tblSoftwareUni.softwareName Like 'Microsoft 365%') And
  tblSoftwareUni.softwareName Not Like '%web components%' And
  tblSoftwareUni.softwareName Not Like '%database%' And
  tblSoftwareUni.softwareName Not Like '%interop%' And
  tblSoftwareUni.SoftwarePublisher Like '%Microsoft%' And tblState.Statename =
  'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName