cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
julianhaines
Engaged Sweeper

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

1 REPLY 1
KevinA-REJIS
Champion Sweeper II

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

 

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now