Multiple Licence Keys

[TheBododos]

Hello,

i need a report that shows me multiple aktivated office keys.

For Example:

PC1 | User | ProductName | ProductID | ProductKey1 (visible in the report)
PC2 | User | ProductName | ProductID | ProductKey1 (visible in the report)
PC3 | User | ProductName | ProductID | ProductKey2
PC4 | User | ProductName | ProductID | ProductKey4 (visible in the report)
PC5 | User | ProductName | ProductID | ProductKey3
PC6 | User | ProductName | ProductID | ProductKey4 (visible in the report)

Report Output:

PC1 | User | ProductName | ProductID | ProductKey1 (visible in the report)
PC2 | User | ProductName | ProductID | ProductKey1 (visible in the report)
PC4 | User | ProductName | ProductID | ProductKey4 (visible in the report)
PC6 | User | ProductName | ProductID | ProductKey4 (visible in the report)

I want to know if there are any keys in multiple use, because of a Microsoft License Test, thank you.

[Susan_A · Accepted solution]

I've included a sample report below, which you can add to your Lansweeper installation by following these instructions. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSerialnumber.Product,
  tblSerialnumber.ProductID,
  tblSerialnumber.productkey
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblSerialnumber On tblAssets.AssetID = tblSerialnumber.AssetID
  Inner Join (Select Top 1000000 tblSerialnumber.Product,
    tblSerialnumber.productkey,
    Count(tblAssetCustom.AssetID) As Count
  From tblAssetCustom
    Inner Join tblSerialnumber On tblSerialnumber.AssetID =
      tblAssetCustom.AssetID
  Where tblAssetCustom.State = 1
  Group By tblSerialnumber.Product,
    tblSerialnumber.productkey) SubQuery On SubQuery.Product =
    tblSerialnumber.Product And SubQuery.productkey = tblSerialnumber.productkey
Where tblAssetCustom.State = 1 And SubQuery.Count > 1
Order By tblSerialnumber.productkey,
  tblSerialnumber.Product,
  tblAssets.Domain,
  tblAssets.AssetName