License Compliance Report by email

[gritchie]

Hi,
Is there a report I can create to mimic the license compliance section? I'd like to have a report emailed each month of our currently licenses but I can't seem to create something that works.

Thanks

Graham

[gritchie]

Sorry to bump this, but I have found this report that partially works

Select Top 1000000 tblLicenses.softwareName As [Software Name],
  InUseCount.InUseLicenses As [In Use],
  LicenseCount.PurchasedLicenses As Purchased,
  (InUseCount.InUseLicenses - LicenseCount.PurchasedLicenses) As Shortfall,
  InUseCount.LicenseidID,
  tblLicenses.LicenseidID As LicenseidID1
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 [Software Name]

The issue is that while it shows all the purchased licences I have added, it is only showing some of the in use. For example I have 476 Office 2010 licences and 414 in use. But this report does not pick up any in use. It does pick up other software in use so I am guessing its something to do with name matching.

Any help would be greatly appreciated.