→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
gritchie
Engaged Sweeper
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
1 REPLY 1
gritchie
Engaged Sweeper
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.