Software Compliance Report
Number of products out of compliance
select swct.compliancetype "Compliance Type",count(swcd.softwareid) "Number of Products" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid where swct.compliancetype='Under Licensed' group by swct.compliancetype
Number of products over licensed
select swct.compliancetype "Compliance Type",count(swcd.softwareid) "Number of Products" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid where swct.compliancetype='Over Licensed' group by swct.compliancetype
Number of vendors out of compliance
select swct.compliancetype "Compliance Type",count(distinct swm.swmanufacturerid) "Number of Vendors" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid left join softwarelist sl on sl.softwareid=swcd.softwareid left join softwaremanufacturer swm on sl.swmanufacturerid=swm.swmanufacturerid where swct.compliancetype='Under Licensed' group by swct.compliancetype
Number of vendors over licensed
select swct.compliancetype "Compliance Type",count(distinct swm.swmanufacturerid) "Number of Vendors" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid left join softwarelist sl on sl.softwareid=swcd.softwareid left join softwaremanufacturer swm on sl.swmanufacturerid=swm.swmanufacturerid where swct.compliancetype='Over Licensed' group by swct.compliancetype
Spend on Over Licensing (Note: Only if Cost data is available in the license, this report will be useful).
select sl.softwarename "Product",sum(rsc.cost) "Total Cost",(sum(rsc.cost)/swcd.purchased) "Unit Cost",(sum(rsc.cost)/swcd.purchased)*swcd.available "Spend on Over Licensing" from softwarelicenses slc left join resources rs on slc.resourceid=rs.resourceid left join componentdefinition cd on rs.componentid=cd.componentid left join componentdefinitionsoftware cds on cds.componentid=cd.componentid left join resourcecost rsc on rsc.resourceid=rs.resourceid left join softwarelist sl on sl.softwareid=cds.softwareid left join swcompliancedetails swcd on swcd.softwareid=sl.softwareid where rsc.cost!='0.00' and swcd.swcompliancetypeid=3 group by sl.softwarename,swcd.purchased,swcd.available
New to ADSelfService Plus?
Related Articles
How to export software data in CSV format?
Steps to export Software data in CSV format 1. Login to ServiceDesk Plus/AssetExplorer. 2. Click on the Reports tab and then click on New Query Report button. 3. Copy and paste the following query and click Run Report. select swm.name "Scanned ...Software Licenses
SELECT resource.resourcename "License Name", Max(softwarelist.softwarename) "Software_Name", Max(resource.acquisitiondate) "Acquisition Date", ...Software details
SELECT systeminfo.workstationname "Workstation", Max(aaauser.first_name) "User", ( softwarelist.softwarename ) "Software_Name", Max(softwaretype.softwaretype) "Software_Type", ...Specific Software
SELECT ( softl.softwarename ) "Software Name", Max(workstation.workstationname) "Workstation", Max(workstation.model) "Model", Max(aaauser.first_name) "User", ...Report on Organizational roles
DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 select r.orgrolename "orgrolename", r.description "Description", au.FIRST_NAME "Name", DepartmentDefinition.DEPTNAME "Department", SDOrganization.NAME "Site" from aaauser au ...