cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
fjca
Champion Sweeper II
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

3 REPLIES 3
fjca
Champion Sweeper II
And that worked just fine, thank you...
RCorbeil
Honored Sweeper II
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
Champion Sweeper II
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...