Find computers with duplicate Office entries on tblSerialNumber

[fjca]

Hi all,

Due to some mishap (still being investigated) during a Microsoft Office upgrade, we ended up with several machines with duplicate Office entries on the Software->License Keys table, we have the old version entry and the new version there, for instance, there is the (old) Microsoft Office 2010 OEM entry, and a Microsoft Office 2013 Volume entry...

I've tried to make a report that lists those machines so we can do a complete Office removal, but my SQL skills are not yet up to the task...

I've adapted this one from another report, but it lists machines with only one Office install, what I wanted was only the machines with two or more entries... can somebody give a helping hand on this ?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblSerialnumber.Product,
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblAssetCustom.State,
  tblADComputers.Lastchanged As AD_LastChanged
From tblAssets
  Inner Join tblComputerSystemProduct On tblAssets.AssetID =
    tblComputerSystemProduct.AssetID
  Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
    tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSerialnumber.Product Like '%office%' And tblAssetCustom.State = 1 And
  (Select Count(*) From tblSerialnumber As a
  Where a.Product like '%office%') > 1
Order By tblAssets.AssetName

[fjca]

And that worked just fine, thank you...

[RCorbeil · Accepted solution]

Give this a try:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSerialnumber.Product,
  tblSerialnumber.ProductKey
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblSerialnumber ON tblAssets.AssetID = tblSerialnumber.AssetID
                                AND tblSerialnumber.Product LIKE 'Microsoft Office%'
                                AND tblSerialnumber.Product NOT LIKE '%Visio%'
                                AND tblSerialnumber.Product NOT LIKE '%Project%'
WHERE
  tblAssetCustom.State = 1
  AND tblAssets.Assettype = -1
  AND ( SELECT Count(*)
        FROM tblSerialnumber
        WHERE
          tblSerialnumber.AssetID = tblAssets.AssetID
          AND tblSerialnumber.Product LIKE 'Microsoft Office%'
          AND tblSerialnumber.Product NOT LIKE '%Visio%'
          AND tblSerialnumber.Product NOT LIKE '%Project%'
      ) > 1

[fjca]

Hi all,

So, no ideias on this one ? I've tried some other combinations/reports, but no luck...
It seems one of the issues is that similar reports look for identical entries, instead of similar ones...