What queries can I use to determine how many licenses are in use by managed and unmanaged computers in Patch Management?
Answer
This will give a grid listing of different resource states and their count of Patch Licenses:
SELECT (CASE IsManaged WHEN 1 THEN 'Managed' ELSE 'Unmanaged' END) as State, Status, count(*) FROM LicenseInUse liu
LEFT OUTER JOIN vFixedAssetResourceStatus ars ON liu.ResourceGuid = ars.Guid
WHERE ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
GROUP BY Status, IsManaged
This will give a count of all Patch licenses consumed by unmanaged resources:
SELECT count(*) as 'Number of stale licenses' FROM LicenseInUse liu
LEFT OUTER JOIN vComputerResource cr on liu.ResourceGuid = cr.Guid
AND IsManaged = 1
WHERE ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
AND cr.Guid IS NULL
Find licenses most recently issued:
select i.Name, i2.Name, ir.Guid, ir.IsManaged, ir.Deleted, liu.ModifiedDate, liu.CreatedDate from LicenseInUse liu
left join ItemResource ir on liu.ResourceGuid = ir.Guid
join Item i on ir.Guid = i.Guid
join Item i2 on ir.ResourceTypeGuid = i2.Guid where liu.ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
order by liu.ModifiedDate desc
Find Deleted Resources still holding a license:
select count(*) 'Deleted resources with a license'
from LicenseInUse liu
join ItemResource ir on liu.ResourceGuid = ir.Guid where liu.ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
and ir.Deleted = 1
Unmanaged (Retired) Resources still holding a license:
select count(*) 'Unmanaged resources with a license'
from LicenseInUse liu
join ItemResource ir on liu.ResourceGuid = ir.Guid where liu.ProductGuid = 'B1338338-5575-4A27-9808-23BEC40D79FA'
and ir.IsManaged = 0