PC with Office 2003 or Lower

[jasonwch]

I'm trying to create a report showing all PCs with MS Office version lower than 2003. But I've had duplicate records shown.

My query is simple to just filter any software name like MS Office%, so it's showing duplicate records if that PC have software named like MS Office (MS Office Web Component, etc)

I think DISTINCT didn't help me but only using views. However, I don't know how to create views in SQL statement. Please advise

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.Username,
  tblSoftware.softwareVersion
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like 'Microsoft Office%' And
  tblSoftware.softwareVersion Not Like '14.%'

[Hemoco · Accepted solution]

What you're describing are not "duplicate" records. You're trying to list only full Office installations, but are also seeing Office components in your report output. Lansweeper software scanning recreates Add/Remove Programs on your client machines. There is no "component" field in the database for software, so there is no way to know whether a software entry is a "full installation" or a "component".

You could try the query below. Keep in mind however that we're assuming "full installation" records end in a year and "component" records do not. This may not be correct 100% of the time.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Username,
tblSoftware.softwareVersion,
tsysOS.Image As icon
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
Where tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftware.softwareVersion Not Like '14.%' And
SubString(tblSoftwareUni.softwareName, Len(tblSoftwareUni.softwareName) - 4,
3) = ' 20'
Order By tblAssets.Domain,
tblAssets.AssetUnique

[jasonwch]

Lansweeper wrote:
What you're describing are not "duplicate" records. You're trying to list only full Office installations, but are also seeing Office components in your report output. Lansweeper software scanning recreates Add/Remove Programs on your client machines. There is no "component" field in the database for software, so there is no way to know whether a software entry is a "full installation" or a "component".

You could try the query below. Keep in mind however that we're assuming "full installation" records end in a year and "component" records do not. This may not be correct 100% of the time.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Username,
tblSoftware.softwareVersion,
tsysOS.Image As icon
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
Where tblSoftwareUni.softwareName Like 'Microsoft Office%' And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftware.softwareVersion Not Like '14.%' And
SubString(tblSoftwareUni.softwareName, Len(tblSoftwareUni.softwareName) - 4,
3) = ' 20'
Order By tblAssets.Domain,
tblAssets.AssetUnique

Understand your meaning. How about another way to still retreive all records with Microsoft Office, but NOT showing duplicated AssetID?

Thanks

[Hemoco]

jasonw wrote:
Understand your meaning. How about another way to still retreive all records with Microsoft Office, but NOT showing duplicated AssetID?

As stated previously, what you're describing is not "duplication". If a machine has multiple software packages whose name include "Microsoft Office", there will be multiple lines per machine in your original query's output. This is expected behavior.

You can try the report we posted to list only "full" Office installations, but the report output may not be 100% correct. We specified that only software records that end in a year should be included in the report.