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


                    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",  ...
                      • Unauthorized Access CSV Report for Users Not Present in the Application

                        Report Details: Currently, the application has not been recording data regarding login attempts when the username used is not available in the application. This report will store the login attempts with usernames who do not exist in the application. ...