→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SMServerSupport
Engaged Sweeper
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
3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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
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