These are two license compliance reports that I use. The first one lists the Software and Licenses in the same way you would see it on the Asset screen. The second one lists all the PC's that have a license asset assigned to them as well as the names of the licenses associated with them.
select A.NAME as 'License', GROUP_CONCAT(distinct(SVDA.APPLICATION_NAME) SEPARATOR ' , ') as 'Software', count(distinct(M.ID)) as '# of Machines', A7.FIELD_1 as 'License Count', (A7.FIELD_1 - count(distinct(M.ID))) as LICENSE_VARIANCE from MACHINE M
left join SAM_MACHINE_JT SMJ on SMJ.MACHINE_ID = M.ID
left join SAM_VIEW_DISCOVERED_APPLICATIONS SVDA on SVDA.ID = SMJ.SAM_CATALOG_ID
left join ASSET_CATALOG_ASSOCIATION ACA on ACA.ASSOCIATED_CATALOG_ID = SMJ.SAM_CATALOG_ID
left join ASSET A on A.ID = ACA.ASSET_ID
left join ASSET_DATA_7 A7 on A7.ID = A.ASSET_DATA_ID
where A.ASSET_TYPE_ID = 7
GROUP BY A.NAME
ORDER BY LICENSE_VARIANCE
(The items in bold need to be changed to match your KBOX Database)
select distinct(M.NAME) as 'Machine', M.USER_FULLNAME as 'User', M.CS_DOMAIN as 'Domain', M.IP as 'IP Address', GROUP_CONCAT(distinct(A.NAME) SEPARATOR ' , ') as 'License' from MACHINE M
left join SAM_MACHINE_JT SMJ on SMJ.MACHINE_ID = M.ID
left join SAM_VIEW_DISCOVERED_APPLICATIONS SVDA on SVDA.ID = SMJ.SAM_CATALOG_ID
left join ASSET_CATALOG_ASSOCIATION ACA on ACA.ASSOCIATED_CATALOG_ID = SMJ.SAM_CATALOG_ID
left join ASSET A on A.ID = ACA.ASSET_ID
left join ASSET_DATA_7 A7 on A7.ID = A.ASSET_DATA_ID
where A.ASSET_TYPE_ID = 7
GROUP BY M.NAME
(The items in bold need to be changed to match your KBOX Database)
Very informative post.
Though, to get software compliance report in my hectic schedule, I use Lepide software asset management (http://www.lepide.com/asset-management.html ) that do the job in a very proficient way and provides a clear picture about the used and available software licenses. The software helps generating various reports that help organizing available licenses. For example, if you have not specified the license type for some software then you can do this after optimizing the software licenses. -
andrewcamary
9 years ago
This website uses cookies.
By continuing to use this site and/or clicking the "Accept" button you are providing consent
Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our
websites or when you do business with us. For more information about our
Privacy Policy and our data protection
efforts, please visit
GDPR-HQ
Though, to get software compliance report in my hectic schedule, I use Lepide software asset management (http://www.lepide.com/asset-management.html ) that do the job in a very proficient way and provides a clear picture about the used and available software licenses. The software helps generating various reports that help organizing available licenses. For example, if you have not specified the license type for some software then you can do this after optimizing the software licenses. - andrewcamary 9 years ago