Custom report to find office installations returns the same installations multiple times

[marshall-o]

Hey guys,

I am trying to write a custom report that will show a breakdown of the MS Office installations on every Laptop. This is what I have thus far:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.Username,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssetCustom.Department,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate,
  tblOperatingsystem.Caption As OS,
  tblAssets.Lastseen,
  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 tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblAssetCustom.Model <> 'Virtual Machine' And
  tblSoftwareUni.softwareName Like 'Microsoft Office%' And
  tblSoftwareUni.softwareName Not Like '%viewer%' And
  tblSoftwareUni.softwareName Not Like '%validation%' And
  tblSoftwareUni.softwareName Not Like '%Runtime%' And
  tblSoftwareUni.softwareName Not Like '%Connector%' And
  tblSoftwareUni.softwareName Not Like '%Installer%' And
  tblSoftwareUni.softwareName Not Like '%engine%' And
  tblSoftwareUni.softwareName Not Like '%Communicator%' And
  tblSoftwareUni.softwareName Not Like '%Live%' And
  tblSoftwareUni.softwareName Not Like '%Interop%' And
  tblSoftwareUni.softwareName Not Like '%Tools%' And
  tblSoftwareUni.softwareName Not Like '%Language%' And
  tblSoftwareUni.softwareName <> 'Microsoft Office' And
  (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or
    TsysChassisTypes.Chassistype = 10 Or TsysChassisTypes.Chassistype = 12)
Order By tblAssets.AssetName

This MOSTLY works, except for one strange occurrence. It is returning some computers more than once. For the most part, this is because those computers have multiple different Office installations on them (for example Pro 2010 and also Visio or Project). However, in not a few cases, some computers are appearing with the exact same displayed info, like this:

John-D COM-US-L1234 192.168.1.2 Latitude E5570 Dell Inc. HN12345 01/01/2016 Microsoft Windows 7 Professional 01/01/2016 Microsoft Office Professional Plus 2010

John-D COM-US-L1234 192.168.1.2 Latitude E5570 Dell Inc. HN12345 01/01/2016 Microsoft Windows 7 Professional 01/01/2016 Microsoft Office Professional Plus 2010

Any idea why?

Thanks,

[marshall-o · Accepted solution]

So I figured this out.

It was giving me certain installations twice because I was calling Chassistype 12 which is our laptop docks here. So for every laptop that was docked at the time, it was reporting the same information twice (once for the laptop and once for the dock).

Removing the dock Chassistype fixes the issue. This report returns every MS Office installation on a Laptop. Code below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.Username,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tblAssetCustom.Department,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate,
  tblOperatingsystem.Caption As OS,
  tblAssets.Lastseen,
  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 tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblAssetCustom.Model <> 'Virtual Machine' And
  tblSoftwareUni.softwareName Like 'Microsoft Office%' And
  tblSoftwareUni.softwareName Not Like '%viewer%' And
  tblSoftwareUni.softwareName Not Like '%validation%' And
  tblSoftwareUni.softwareName Not Like '%Runtime%' And
  tblSoftwareUni.softwareName Not Like '%Connector%' And
  tblSoftwareUni.softwareName Not Like '%Installer%' And
  tblSoftwareUni.softwareName Not Like '%engine%' And
  tblSoftwareUni.softwareName Not Like '%Communicator%' And
  tblSoftwareUni.softwareName Not Like '%Live%' And
  tblSoftwareUni.softwareName Not Like '%Interop%' And
  tblSoftwareUni.softwareName Not Like '%Tools%' And
  tblSoftwareUni.softwareName Not Like '%Language%' And
  tblSoftwareUni.softwareName <> 'Microsoft Office' And
  (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or
    TsysChassisTypes.Chassistype = 10)
Order By tblAssets.AssetName

You can remove or alter the last Where condition to get assets of different types (like Desktops) or all assets.