Duplicate Licenses Report

[SMServerSupport]

Hello all,

We use LANSweeper in an enterprise environment, where our Microsoft products and many others are volume licenses, but we're still stuck with some products like Adobe, AutoDesk, etc., that are single license. I'd like to have a report that tells us if any two or more computers in our network are using the same license for one of these single license software suites.

I've been trying to configure the "License: Software licensekey overview" report to show this, but I'm not fluent in SQL, so I haven't had any luck yet.

If anyone has an idea of how to do that, I'd be VERY appreciative!

Thanks,

Colin

[RCorbeil]

It's down to the WHERE clause.

  tblAssetCustom.State = 1
  AND Keys.KeyCount > 1
  AND tblSerialnumber.Product LIKE '%Adobe%'
  OR tblSerialnumber.Product LIKE '%Auto%'

The boolean operators have an order of precedence, just like math. Think "A * B - C" versus "A * (B - C)". As with the subtraction, you want the OR to be evaluated before the AND.

Try changing it to

  tblAssetCustom.State = 1
  AND Keys.KeyCount > 1
  AND (tblSerialnumber.Product LIKE '%Adobe%'
       OR tblSerialnumber.Product LIKE '%Auto%')

IF the asset is active
AND there's more than one key in use
AND ( the product name contains "Adobe" OR the product name contains "Auto")

[RCorbeil]

Try this:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSerialnumber.Product,
  tblSerialnumber.ProductID,
  tblSerialnumber.ProductKey,
  Keys.KeyCount
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
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join (SELECT
                 s.Product,
                 s.ProductKey,
                 Count(*) AS KeyCount
               FROM
                 tblSerialnumber AS s
               GROUP BY
                 s.Product,
                 s.ProductKey
              ) AS Keys ON Keys.Product=tblSerialNumber.Product AND Keys.ProductKey=tblSerialNumber.ProductKey
WHERE
  tblAssetCustom.State = 1
  AND Keys.KeyCount > 1
  AND tblSerialnumber.Product <> 'Internet Explorer'
  AND tblSerialnumber.Product NOT LIKE 'Windows%Enterprise'
  AND tblSerialnumber.Product NOT LIKE 'Microsoft Office%Volume license'
  AND tblSerialnumber.Product NOT LIKE 'Windows Server%'
  AND tblSerialnumber.Product NOT LIKE 'SQL Server%'
ORDER BY
  tblSerialnumber.Product,
  tblSerialNumber.ProductKey,
  tblAssets.AssetName

I've taken the liberty of filtering out a few things that you're probably not interested in seeing.

I've used the product name and license key as the unique factors when counting duplicates. If you want to refine that further by including the product ID, replace the last JOIN with this:

  Inner Join (SELECT
                 s.Product,
                 s.ProductID,
                 s.ProductKey,
                 Count(*) AS KeyCount
               FROM
                 tblSerialnumber AS s
               GROUP BY
                 s.Product,
                 s.ProductID,
                 s.ProductKey
              ) AS Keys ON Keys.Product=tblSerialNumber.Product AND Keys.ProductID=tblSerialNumber.ProductID AND Keys.ProductKey=tblSerialNumber.ProductKey

[SMServerSupport]

RC62N wrote:
Try this:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSerialnumber.Product,
  tblSerialnumber.ProductID,
  tblSerialnumber.ProductKey,
  Keys.KeyCount
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
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join (SELECT
                 s.Product,
                 s.ProductKey,
                 Count(*) AS KeyCount
               FROM
                 tblSerialnumber AS s
               GROUP BY
                 s.Product,
                 s.ProductKey
              ) AS Keys ON Keys.Product=tblSerialNumber.Product AND Keys.ProductKey=tblSerialNumber.ProductKey
WHERE
  tblAssetCustom.State = 1
  AND Keys.KeyCount > 1
  AND tblSerialnumber.Product <> 'Internet Explorer'
  AND tblSerialnumber.Product NOT LIKE 'Windows%Enterprise'
  AND tblSerialnumber.Product NOT LIKE 'Microsoft Office%Volume license'
  AND tblSerialnumber.Product NOT LIKE 'Windows Server%'
  AND tblSerialnumber.Product NOT LIKE 'SQL Server%'
ORDER BY
  tblSerialnumber.Product,
  tblSerialNumber.ProductKey,
  tblAssets.AssetName

I've taken the liberty of filtering out a few things that you're probably not interested in seeing.

I've used the product name and license key as the unique factors when counting duplicates. If you want to refine that further by including the product ID, replace the last JOIN with this:

  Inner Join (SELECT
                 s.Product,
                 s.ProductID,
                 s.ProductKey,
                 Count(*) AS KeyCount
               FROM
                 tblSerialnumber AS s
               GROUP BY
                 s.Product,
                 s.ProductID,
                 s.ProductKey
              ) AS Keys ON Keys.Product=tblSerialNumber.Product AND Keys.ProductID=tblSerialNumber.ProductID AND Keys.ProductKey=tblSerialNumber.ProductKey

Thanks, RC62N!

That got me most of the way there. The two products I'm currently interested in seeing this for are Adobe and AutoCAD, so I edited it this way: (As I said before, I'm not very handy in SQL, so this is probably a bit of a hack job.)

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSerialnumber.Product,
  tblSerialnumber.ProductID,
  tblSerialnumber.ProductKey,
  Keys.KeyCount
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
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join (SELECT
                 s.Product,
                 s.ProductKey,
                 Count(*) AS KeyCount
               FROM
                 tblSerialnumber AS s
               GROUP BY
                 s.Product,
                 s.ProductKey
              ) AS Keys ON Keys.Product=tblSerialNumber.Product AND Keys.ProductKey=tblSerialNumber.ProductKey
WHERE
  tblAssetCustom.State = 1
  AND Keys.KeyCount > 1
  AND tblSerialnumber.Product LIKE '%Adobe%''
  OR tblSerialnumber.Product LIKE '%Auto%'
ORDER BY
  tblSerialnumber.Product,
  tblSerialNumber.ProductKey,
  tblAssets.AssetName

I ran the report and it seemed to work, but I think there's still something I'm missing. It's currently showing me all Adobe and AutoCAD software, not just ones with more than 1 instance of the same license. Do you know of a way to report only duplicates?

I'm attaching a picture of some of the AutoCAD items in the list (key blurred out), showing that there's only one instance, but it's still reporting them.

Thanks again for your help!

Colin