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
Query to extract the “Software-License Additional Fields” along with the existing additional fields (PGSQL)
(Applicable for builds 14300 & above) Below query has been modified to add a LEFT JOIN between the additional fields table and the table from which you extracted the softwares: QUERY: SELECT SUM(scd.ALLOCATED) AS "Allocated", ...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 ...Script to send multiple license expiry notification in Software License/Agreement
We can set only 1 notification for the license expiry. Using custom schedule multiple expiry notifications like 90 days before, 60 days before and 30 days before can be achieved using script. Prerequisites: 1. Python is a third party software that ...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", ...