Software Compliance Report

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


          • 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 ...