License compliance report

[moarsmor]

Hi,

Any way to create a report that gives me the same result as the software license compliance does ?
Only the main numbers for each license, not the individual version information..

[moarsmor]

Nice. It works great.

[RCorbeil]

A small change to the [Cost] column to account for software being recorded but no purchases associated with that software:

  CASE
    WHEN (InUseCount.InUseLicenses - (CASE WHEN LicenseCount.PurchasedLicenses IS NULL
                                           THEN 0
                                           ELSE LicenseCount.PurchasedLicenses
                                      END)) > 0
    THEN (InUseCount.InUseLicenses - (CASE WHEN LicenseCount.PurchasedLicenses IS NULL
                                           THEN 0
                                           ELSE LicenseCount.PurchasedLicenses
                                      END)) * tblLicenses.Priceperlicense
    ELSE NULL
  END AS [Cost],

[RCorbeil · Accepted solution]

Try this to get you going:

SELECT
  tblLicenses.softwareName        AS [Software Name],
  tsyslicensetype.LicenseTypeName AS [License Type],
  tblLicenses.Priceperlicense     AS [Unit Price],
  InUseCount.InUseLicenses        AS [In Use],
  LicenseCount.PurchasedLicenses  AS [Purchased],
  (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) AS [Shortfall],
  CASE
    WHEN (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) > 0
    THEN (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) * tblLicenses.Priceperlicense
    ELSE NULL
  END AS [Cost],
  tblLicenses.LicenseOwner        AS [Owner],
  tblLicenses.LicenseContact      AS [Contact]
FROM
  tblLicenses
  LEFT JOIN tsyslicensetype ON tsyslicensetype.LicenseType=tblLicenses.LicenseType
  LEFT JOIN ( SELECT
                tblSublicensesOrders.LicenseidID,
                Sum(tblSublicensesOrders.NrLicenses) AS PurchasedLicenses
              FROM
                tblSublicensesOrders
              GROUP BY
                tblSublicensesOrders.LicenseidID
            ) AS LicenseCount ON LicenseCount.LicenseidID=tblLicenses.LicenseidID
  LEFT JOIN ( SELECT
                tblsublicenses.LicenseidID,
                Count(*) AS InUseLicenses
              FROM
                tblsublicenses
                INNER JOIN ( SELECT
                               tblSoftwareUni.softwareName,
                               tblSoftware.SoftwareVersion
                             FROM
                               tblSoftware
                               INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID=tblSoftware.softID
                               INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID=tblSoftware.AssetID
                             WHERE
                               tblAssetCustom.State=1
                           ) AS Software ON Software.softwareName=tblsublicenses.softwareName
                                            AND Software.softwareVersion=tblsublicenses.softwareVersion
              GROUP BY
                tblsublicenses.LicenseidID
            ) AS InUseCount ON InUseCount.LicenseidID=tblLicenses.LicenseidID
ORDER BY
  tblLicenses.softwareName