→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎11-29-2013 11:08 AM
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.%'
Solved! Go to Solution.
‎11-29-2013 02:18 PM
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
‎11-29-2013 02:18 PM
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
‎12-02-2013 04:55 AM
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
‎12-02-2013 01:39 PM
jasonw wrote:
Understand your meaning. How about another way to still retreive all records with Microsoft Office, but NOT showing duplicated AssetID?
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now