licensecalcSQL.aspx showing incorrect results

[njordur]

SQL Licensing numbers are incorrect.
On this page https://lansweeper/licenses/licensecalcSQL.aspx the number of instances are counted instead of servers. This is not useful for me as I only pay for one instance per server, as does everybody else.
This representation of the numbers are incorrect when having more than one instance. Like SQL Active/Active/Passive setups on clusters. I fixed it for my environment.

This can probably be done better but it works for me, hopefully this will benefit someone else.

Always take backups before changing this.

In this table [lansweeperdb].[dbo].[tsysDBobjects]
Check for web50SQLlicenses
Update the query to this: (you need to put double quotes where there are single quotes for the updates to work)

SELECT licenseview.LicenseID,
       licenseview.License,
       licenseview.SubCount AS ServersInUse,
       licenseview.NrServerlicenses AS ServersPurchased,
       CASE
           WHEN (tblSqlLicenses.LicenseType = 1
                 OR tblSqlLicenses.LicenseType = 4) THEN (licenseview.SubCount - licenseview.NrServerlicenses)
           ELSE '0'
       END AS ServersMissing,
       CASE
           WHEN tblSqlLicenses.LicenseType = 1 THEN '0'
           WHEN (tblSqlLicenses.LicenseType = 5
                 AND datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
           WHEN (tblSqlLicenses.LicenseType = 5) THEN '0'
           WHEN (tblSqlLicenses.LicenseType = 4
                 AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms
           ELSE tblSqlLicenses.NrOfTerms * licenseview.SubCount
       END AS InUse,
       CASE
           WHEN (tblSqlLicenses.LicenseType = 5) THEN (CASE
                                                           WHEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0 THEN '0'
                                                           ELSE datediff(m, getdate(), tblSqlLicenses.LicenseExpiration) * licenseview.SubCount
                                                       END)
           ELSE licenseview.Nrlicenses
       END AS Purchased,
       CASE
           WHEN (tblSqlLicenses.LicenseType = 5) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
           WHEN (tblSqlLicenses.LicenseType = 4
                 AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms - licenseview.Nrlicenses
           ELSE (tblSqlLicenses.NrOfTerms * licenseview.SubCount) - licenseview.Nrlicenses
       END AS Missing,
       tsysSqlLicenseTypes.LicenseTypeName AS TYPE,
       tsysSqlLicenseTypes.LicenseType AS TypeID,
       tblSqlLicenses.PricePerServer,
       tblSqlLicenses.PricePerTerm,
       Usedservers.NAME,
       tblSqlLicenses.LicenseContact,
       tblSqlLicenses.LicenseOwner,
       Usedservers.NAME AS SubName,
       Usedservers.nrofservers
FROM tblSqlLicenses
INNER JOIN
  (SELECT DISTINCT Purchased.LicenseID,
                   Purchased.NAME AS License,
                   (CASE WHEN Purchased2.nrlicenses IS NULL THEN '0' ELSE Purchased2.nrlicenses END) AS Nrlicenses,
                   (CASE WHEN Purchased.nrserverlicenses IS NULL THEN '0' ELSE Purchased.nrserverlicenses END) AS NrServerlicenses,
                   Purchased.PricePerServer,
                   Nametotal.nrofservers AS SubCount
   FROM tblSqlSubServers
   INNER JOIN
     (SELECT LicenseID,
             count(nrofservers) AS nrofservers
      FROM
        (SELECT DISTINCT tblSqlSubServers_1.LicenseID,
                         tblAssets.AssetID AS nrofservers
         FROM tblAssetCustom
         INNER JOIN tblSqlServers ON tblAssetCustom.AssetID = tblSqlServers.AssetID
         INNER JOIN tblSqlSubServers AS tblSqlSubServers_1 ON tblSqlServers.displayVersion + ' ' + tblSqlServers.skuName LIKE tblSqlSubServers_1.NAME
         INNER JOIN tblAssets ON tblAssetCustom.AssetID = tblAssets.AssetID
         AND tblSqlServers.AssetID = tblAssets.AssetID
         AND tblAssets.AssetName = tblSqlSubServers_1.AssetName
         WHERE (tblAssetCustom.STATE = 1)) T
      GROUP BY LicenseID) AS Nametotal ON Nametotal.LicenseID = tblSqlSubServers.LicenseID
   RIGHT JOIN
     (SELECT tblSqlLicenses_1.NAME,
             tblSqlLicenses_1.PricePerServer,
             tblSqlLicenses_1.LicenseID,
             SUM(nrserverlicenses.Nrlicenses) AS nrserverlicenses
      FROM tblSqlLicenses AS tblSqlLicenses_1
      LEFT JOIN tblSqlLicensesServerOrders AS nrserverlicenses ON nrserverlicenses.LicenseID = tblSqlLicenses_1.LicenseID
      GROUP BY tblSqlLicenses_1.NAME,
               tblSqlLicenses_1.PricePerServer,
               tblSqlLicenses_1.LicenseID) AS Purchased ON tblSqlSubServers.LicenseID = Purchased.LicenseID
   RIGHT JOIN
     (SELECT tblSqlLicenses_3.NAME,
             tblSqlLicenses_3.LicenseID,
             SUM(nrlicenses_3.Nrlicenses) AS nrlicenses,
             tblSqlLicenses_3.PricePerTerm
      FROM tblSqlLicenses AS tblSqlLicenses_3
      LEFT JOIN tblSqlLicensesOrders AS nrlicenses_3 ON nrlicenses_3.LicenseID = tblSqlLicenses_3.LicenseID
      GROUP BY tblSqlLicenses_3.LicenseID,
               tblSqlLicenses_3.NAME,
               tblSqlLicenses_3.LicenseID,
               tblSqlLicenses_3.PricePerTerm) AS Purchased2 ON tblSqlSubServers.LicenseID = Purchased2.LicenseID
   GROUP BY Purchased.LicenseID,
            Purchased.NAME,
            Purchased2.nrlicenses,
            Purchased.nrserverlicenses,
            Purchased.PricePerServer,
            Nametotal.nrofservers) AS licenseview ON tblSqlLicenses.LicenseID = licenseview.LicenseID
INNER JOIN tsysSqlLicenseTypes ON tblSqlLicenses.LicenseType = tsysSqlLicenseTypes.LicenseType
INNER JOIN
  (SELECT NAME,
          LicenseID,
          count(assetid) AS nrofservers
   FROM
     (SELECT DISTINCT RTRIM(LTRIM(COALESCE(tblSqlServers_1.displayVersion, N'') + ' ' + COALESCE(tblSqlServers_1.skuName, N''))) AS NAME,
                                                                                                                                    tblSqlSubServers_2.LicenseID,
                                                                                                                                    tblAssetCustom_1.AssetID
      FROM tblAssetCustom AS tblAssetCustom_1
      INNER JOIN tblSqlServers AS tblSqlServers_1 ON tblAssetCustom_1.AssetID = tblSqlServers_1.AssetID
      INNER JOIN tblSqlSubServers AS tblSqlSubServers_2 ON tblSqlServers_1.displayVersion + ' ' + tblSqlServers_1.skuName LIKE tblSqlSubServers_2.NAME
      INNER JOIN tblAssets AS tblAssets_1 ON tblAssetCustom_1.AssetID = tblAssets_1.AssetID
      AND tblSqlServers_1.AssetID = tblAssets_1.AssetID
      AND tblAssets_1.AssetName = tblSqlSubServers_2.AssetName
      WHERE (tblAssetCustom_1.STATE = 1)) T
   GROUP BY Name,
            LicenseID) AS Usedservers ON Usedservers.LicenseID = tblSqlLicenses.LicenseID
   ORDER BY TypeID,
         licenseview.License,
         tblSqlLicenses.Name

Original code is here:

SELECT licenseview.LicenseID,
       licenseview.License,
       licenseview.SubCount AS ServersInUse,
       licenseview.NrServerlicenses AS ServersPurchased,
       CASE
           WHEN (tblSqlLicenses.LicenseType = 1
                 OR tblSqlLicenses.LicenseType = 4) THEN (licenseview.SubCount - licenseview.NrServerlicenses)
           ELSE '0'
       END AS ServersMissing,
       CASE
           WHEN tblSqlLicenses.LicenseType = 1 THEN '0'
           WHEN (tblSqlLicenses.LicenseType = 5
                 AND datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
           WHEN (tblSqlLicenses.LicenseType = 5) THEN '0'
           WHEN (tblSqlLicenses.LicenseType = 4
                 AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms
           ELSE tblSqlLicenses.NrOfTerms * licenseview.SubCount
       END AS InUse,
       CASE
           WHEN (tblSqlLicenses.LicenseType = 5) THEN (CASE
                                                           WHEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) > 0 THEN '0'
                                                           ELSE datediff(m, getdate(), tblSqlLicenses.LicenseExpiration) * licenseview.SubCount
                                                       END)
           ELSE licenseview.Nrlicenses
       END AS Purchased,
       CASE
           WHEN (tblSqlLicenses.LicenseType = 5) THEN datediff(m, tblSqlLicenses.LicenseExpiration, getdate()) * licenseview.SubCount
           WHEN (tblSqlLicenses.LicenseType = 4
                 AND tblSqlLicenses.SharedCAL = 1) THEN tblSqlLicenses.NrOfTerms - licenseview.Nrlicenses
           ELSE (tblSqlLicenses.NrOfTerms * licenseview.SubCount) - licenseview.Nrlicenses
       END AS Missing,
       tsysSqlLicenseTypes.LicenseTypeName AS TYPE,
       tsysSqlLicenseTypes.LicenseType AS TypeID,
       tblSqlLicenses.PricePerServer,
       tblSqlLicenses.PricePerTerm,
       Usedservers.Name,
       tblSqlLicenses.LicenseContact,
       tblSqlLicenses.LicenseOwner,
       Usedservers.Name AS SubName,
       Usedservers.nrofservers
FROM tblSqlLicenses
INNER JOIN
  (SELECT DISTINCT Purchased.LicenseID,
                   Purchased.Name AS License,
                   (CASE WHEN Purchased2.nrlicenses IS NULL THEN '0' ELSE Purchased2.nrlicenses END) AS Nrlicenses,
                   (CASE WHEN Purchased.nrserverlicenses IS NULL THEN '0' ELSE Purchased.nrserverlicenses END) AS NrServerlicenses,
                   Purchased.PricePerServer,
                   Nametotal.nrofservers AS SubCount
   FROM tblSqlSubServers
   INNER JOIN
     (SELECT tblSqlSubServers_1.LicenseID,
             COUNT(tblAssets.AssetID) AS nrofservers
      FROM tblAssetCustom
      INNER JOIN tblSqlServers ON tblAssetCustom.AssetID = tblSqlServers.AssetID
      INNER JOIN tblSqlSubServers AS tblSqlSubServers_1 ON tblSqlServers.displayVersion + ' ' + tblSqlServers.skuName LIKE tblSqlSubServers_1.Name
      INNER JOIN tblAssets ON tblAssetCustom.AssetID = tblAssets.AssetID
      AND tblSqlServers.AssetID = tblAssets.AssetID
      AND tblAssets.AssetName LIKE tblSqlSubServers_1.AssetName
      WHERE (tblAssetCustom.State = 1)
      GROUP BY tblSqlSubServers_1.LicenseID) AS Nametotal ON Nametotal.LicenseID = tblSqlSubServers.LicenseID
   RIGHT OUTER JOIN
     (SELECT tblSqlLicenses_1.Name,
             tblSqlLicenses_1.PricePerServer,
             tblSqlLicenses_1.LicenseID,
             SUM(nrserverlicenses.Nrlicenses) AS nrserverlicenses
      FROM tblSqlLicenses AS tblSqlLicenses_1
      LEFT OUTER JOIN tblSqlLicensesServerOrders AS nrserverlicenses ON nrserverlicenses.LicenseID = tblSqlLicenses_1.LicenseID
      GROUP BY tblSqlLicenses_1.Name,
               tblSqlLicenses_1.PricePerServer,
               tblSqlLicenses_1.LicenseID) AS Purchased ON tblSqlSubServers.LicenseID = Purchased.LicenseID
   RIGHT OUTER JOIN
     (SELECT tblSqlLicenses_3.Name,
             tblSqlLicenses_3.LicenseID,
             SUM(nrlicenses_3.Nrlicenses) AS nrlicenses,
             tblSqlLicenses_3.PricePerTerm
      FROM tblSqlLicenses AS tblSqlLicenses_3
      LEFT OUTER JOIN tblSqlLicensesOrders AS nrlicenses_3 ON nrlicenses_3.LicenseID = tblSqlLicenses_3.LicenseID
      GROUP BY tblSqlLicenses_3.LicenseID,
               tblSqlLicenses_3.Name,
               tblSqlLicenses_3.LicenseID,
               tblSqlLicenses_3.PricePerTerm) AS Purchased2 ON tblSqlSubServers.LicenseID = Purchased2.LicenseID
   GROUP BY Purchased.LicenseID,
            Purchased.Name,
            Purchased2.nrlicenses,
            Purchased.nrserverlicenses,
            Purchased.PricePerServer,
            Nametotal.nrofservers) AS licenseview ON tblSqlLicenses.LicenseID = licenseview.LicenseID
INNER JOIN tsysSqlLicenseTypes ON tblSqlLicenses.LicenseType = tsysSqlLicenseTypes.LicenseType
INNER JOIN
  (SELECT RTRIM(LTRIM(COALESCE (tblSqlServers_1.displayVersion, N'') + ' ' + COALESCE (tblSqlServers_1.skuName, N''))) AS Name,
          tblSqlSubServers_2.LicenseID,
          COUNT(tblAssets_1.AssetID) AS nrofservers
   FROM tblAssetCustom AS tblAssetCustom_1
   INNER JOIN tblSqlServers AS tblSqlServers_1 ON tblAssetCustom_1.AssetID = tblSqlServers_1.AssetID
   INNER JOIN tblSqlSubServers AS tblSqlSubServers_2 ON tblSqlServers_1.displayVersion + ' ' + tblSqlServers_1.skuName LIKE tblSqlSubServers_2.Name
   INNER JOIN tblAssets AS tblAssets_1 ON tblAssetCustom_1.AssetID = tblAssets_1.AssetID
   AND tblSqlServers_1.AssetID = tblAssets_1.AssetID
   AND tblAssets_1.AssetName LIKE tblSqlSubServers_2.AssetName
   WHERE (tblAssetCustom_1.State = 1)
   GROUP BY RTRIM(LTRIM(COALESCE (tblSqlServers_1.displayVersion, N'') + ' ' + COALESCE (tblSqlServers_1.skuName, N''))),
            tblSqlSubServers_2.LicenseID) AS Usedservers ON Usedservers.LicenseID = tblSqlLicenses.LicenseID
ORDER BY TypeID,
         licenseview.License,
         tblSqlLicenses.Name

[Bert_D]

I took the liberty of adjusting your post.

Thanks for sharing!

[njordur]

You're correct I just copy pasted it into ssms and it fails, I just have to remove the [ h] and [ /h] in the code and it works.

[Bert_D]

I just tried running the report but i get a bunch of errors.

Could you please check/update your modified report.

Thx

[Susan_A · Accepted solution]

Just an FYI for everyone: we'll do a complete overhaul of the compliance modules in one of our future releases, to make them more accurate and flexible.