Software license reports

Please find the Software license reports below .

PURCHASED VS INSTALLED :

1 Site based :

Software Manufacturer Type Category Purchased Installed Max Used Site

SELECT Max(SoftwareList.SOFTWARENAME) "Software",Max(SoftwareList.FILEVENDOR) "Manufacturer",Max(SoftwareType.SOFTWARETYPE) "Type",Max(SoftwareCategory.SOFTWARECATEGORY) "Category",COUNT(DISTINCT(SoftwareLicenses.RESOURCEID)) 'Purchased',COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) 'Installed' ,COUNT(DISTINCT(MaxUsedSwInfo.SOFTWAREINFOID)) 'Max Used' , max(aaov.NAME) "Site" FROM SoftwareGroupMember LEFT JOIN SoftwareList ON SoftwareGroupMember.PARENTSOFTWAREID=SoftwareList.SOFTWAREID LEFT JOIN ComponentDefinitionSoftware ON SoftwareGroupMember.PARENTSOFTWAREID=ComponentDefinitionSoftware.SOFTWAREID LEFT JOIN Resources ON ComponentDefinitionSoftware.COMPONENTID=Resources.COMPONENTID LEFT JOIN SoftwareLicenses ON Resources.RESOURCEID=SoftwareLicenses.RESOURCEID LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID LEFT JOIN SWComplianceType ON SoftwareList.SWCOMPLIANCETYPEID=SWComplianceType.SWCOMPLIANCETYPEID LEFT JOIN SoftwareCategory ON SoftwareList.SOFTWARECATEGORYID=SoftwareCategory.SOFTWARECATEGORYID LEFT JOIN SoftwareInfo ON SoftwareGroupMember.SOFTWAREID=SoftwareInfo.SOFTWAREID LEFT JOIN WmiSWUsageInfo ON SoftwareInfo.SOFTWAREINFOID=WmiSWUsageInfo.SOFTWAREINFOID LEFT JOIN MaxUsedSwInfo ON WmiSWUsageInfo.SOFTWAREINFOID=MaxUsedSwInfo.SOFTWAREINFOID LEFT JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID=siteDef.SITEID LEFT JOIN SDOrganization aaov ON siteDef.SITEID=aaov.ORG_ID where SoftwareType.SOFTWARETYPE = 'Managed' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID order by 8

2 For a particular software :

Edit the software name before executing the query.

Software Manufacturer Type Category Purchased Installed Max Used Site

SELECT MAX(SoftwareList.SOFTWARENAME) 'Software',MAX(SoftwareList.FILEVENDOR) 'Manufacturer',MAX(SoftwareType.SOFTWARETYPE) 'Type', MAX(SoftwareCategory.SOFTWARECATEGORY) 'Category',COUNT(DISTINCT(SoftwareLicenses.RESOURCEID)) 'Purchased',COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) 'Installed' ,COUNT(DISTINCT(MaxUsedSwInfo.SOFTWAREINFOID)) 'Max Used' FROM SoftwareGroupMember LEFT JOIN SoftwareList ON SoftwareGroupMember.PARENTSOFTWAREID=SoftwareList.SOFTWAREID LEFT JOIN ComponentDefinitionSoftware ON SoftwareGroupMember.PARENTSOFTWAREID=ComponentDefinitionSoftware.SOFTWAREID LEFT JOIN Resources ON ComponentDefinitionSoftware.COMPONENTID=Resources.COMPONENTID LEFT JOIN SoftwareLicenses ON Resources.RESOURCEID=SoftwareLicenses.RESOURCEID LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID LEFT JOIN SWComplianceType ON SoftwareList.SWCOMPLIANCETYPEID=SWComplianceType.SWCOMPLIANCETYPEID LEFT JOIN SoftwareCategory ON SoftwareList.SOFTWARECATEGORYID=SoftwareCategory.SOFTWARECATEGORYID LEFT JOIN SoftwareInfo ON SoftwareGroupMember.SOFTWAREID=SoftwareInfo.SOFTWAREID LEFT JOIN WmiSWUsageInfo ON SoftwareInfo.SOFTWAREINFOID=WmiSWUsageInfo.SOFTWAREINFOID LEFT JOIN MaxUsedSwInfo ON WmiSWUsageInfo.SOFTWAREINFOID=MaxUsedSwInfo.SOFTWAREINFOID where SoftwareList.SOFTWARENAME like 'Microsoft%' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID order by Category

3 For department:

Department Software Manufacturer User Purchased Installed Max Used

SELECT COALESCE(ddef.DEPTNAME,ddef1.DEPTNAME) 'Department',SoftwareList.SOFTWARENAME 'Software',SoftwareList.FILEVENDOR 'Manufacturer',AaaUser.first_name 'User',COUNT(DISTINCT(SoftwareLicenses.RESOURCEID)) 'Purchased',COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) 'Installed' ,COUNT(DISTINCT(MaxUsedSwInfo.SOFTWAREINFOID)) 'Max Used' FROM SoftwareGroupMember INNER JOIN SoftwareInfo ON SoftwareGroupMember.SOFTWAREID=SoftwareInfo.SOFTWAREID INNER JOIN SystemInfo ON SoftwareInfo.WORKSTATIONID=SystemInfo.WORKSTATIONID LEFT JOIN Resources ON SystemInfo.WORKSTATIONID=Resources.RESOURCEID LEFT JOIN ResourceOwner ON SystemInfo.WORKSTATIONID=ResourceOwner.RESOURCEID LEFT JOIN SDUser ON ResourceOwner.USERID=SDUser.USERID LEFT JOIN AaaUser ON SDUser.USERID=AaaUser.USER_ID LEFT JOIN SoftwareList ON SoftwareInfo.SOFTWAREID=SoftwareList.SOFTWAREID LEFT JOIN WmiSWUsageInfo ON SoftwareInfo.SOFTWAREINFOID=WmiSWUsageInfo.SOFTWAREINFOID LEFT JOIN SWInstallationKeyInfo ON SoftwareInfo.SOFTWAREINFOID=SWInstallationKeyInfo.SOFTWAREINFOID LEFT JOIN ResourceLocation ON SoftwareInfo.WORKSTATIONID=ResourceLocation.RESOURCEID LEFT JOIN SDOrganization ON ResourceLocation.SITEID=SDOrganization.ORG_ID LEFT JOIN RegionDefinition ON ResourceLocation.REGIONID=RegionDefinition.REGIONID LEFT JOIN SoftwareLicenses ON Resources.RESOURCEID=SoftwareLicenses.RESOURCEID LEFT JOIN MaxUsedSwInfo ON WmiSWUsageInfo.SOFTWAREINFOID=MaxUsedSwInfo.SOFTWAREINFOID LEFT JOIN DepartmentDefinition ddef on ddef.DEPTID=ResourceOwner.DEPTID LEFT JOIN UserDepartment udef on udef.USERID=ResourceOwner.USERID LEFT JOIN DepartmentDefinition ddef1 on ddef1.DEPTID=udef.DEPTID LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID WHERE SoftwareType.SOFTWARETYPE = 'Managed' group by COALESCE(ddef.DEPTNAME,ddef1.DEPTNAME),SoftwareList.SOFTWARENAME,SoftwareList.FILEVENDOR ,AaaUser.first_name order by 2

4 Underlicensed :

Software Manufacturer Type Category Purchased Installed Max Used Under licensed

SELECT Max(SoftwareList.SOFTWARENAME) "Software",Max(SoftwareList.FILEVENDOR) "Manufacturer",Max(SoftwareType.SOFTWARETYPE) "Type",Max(SoftwareCategory.SOFTWARECATEGORY) "Category",COUNT(DISTINCT(SoftwareLicenses.RESOURCEID)) 'Purchased',COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) 'Installed' ,COUNT(DISTINCT(MaxUsedSwInfo.SOFTWAREINFOID)) 'Max Used',(COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID))- COUNT(DISTINCT(SoftwareLicenses.RESOURCEID))) 'Under licensed' FROM SoftwareGroupMember LEFT JOIN SoftwareList ON softwareGroupMember.PARENTSOFTWAREID=SoftwareList.SOFTWAREID LEFT JOIN ComponentDefinitionSoftware ON SoftwareGroupMember.PARENTSOFTWAREID=ComponentDefinitionSoftware.SOFTWAREID LEFT JOIN Resources ON ComponentDefinitionSoftware.COMPONENTID=Resources.COMPONENTID LEFT JOIN SoftwareLicenses ON Resources.RESOURCEID=SoftwareLicenses.RESOURCEID LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID LEFT JOIN SWComplianceType ON SoftwareList.SWCOMPLIANCETYPEID=SWComplianceType.SWCOMPLIANCETYPEID LEFT JOIN SoftwareCategory ON SoftwareList.SOFTWARECATEGORYID=SoftwareCategory.SOFTWARECATEGORYID LEFT JOIN SoftwareInfo ON SoftwareGroupMember.SOFTWAREID=SoftwareInfo.SOFTWAREID LEFT JOIN WmiSWUsageInfo ON SoftwareInfo.SOFTWAREINFOID=WmiSWUsageInfo.SOFTWAREINFOID LEFT JOIN MaxUsedSwInfo ON WmiSWUsageInfo.SOFTWAREINFOID=MaxUsedSwInfo.SOFTWAREINFOID where SoftwareType.SOFTWARETYPE = 'Managed' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID

5 For domain :

Domain Software Category User Purchased Installed Max Used

SELECT dominf.DOMAINNAME 'Domain' , SoftwareList.SOFTWARENAME 'Software',Max(SoftwareCategory.SOFTWARECATEGORY) "Category" ,au.first_name 'User',COUNT(DISTINCT(SoftwareLicenses.RESOURCEID)) 'Purchased',COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) 'Installed' ,COUNT(DISTINCT(MaxUsedSwInfo.SOFTWAREINFOID)) 'Max Used' FROM SoftwareGroupMember LEFT JOIN SoftwareList ON SoftwareGroupMember.PARENTSOFTWAREID=SoftwareList.SOFTWAREID LEFT JOIN ComponentDefinitionSoftware ON SoftwareGroupMember.PARENTSOFTWAREID=ComponentDefinitionSoftware.SOFTWAREID LEFT JOIN Resources ON ComponentDefinitionSoftware.COMPONENTID=Resources.COMPONENTID LEFT JOIN SoftwareLicenses ON Resources.RESOURCEID=SoftwareLicenses.RESOURCEID LEFT JOIN SoftwareType ON SoftwareList.SOFTWARETYPEID=SoftwareType.SOFTWARETYPEID LEFT JOIN SWComplianceType ON SoftwareList.SWCOMPLIANCETYPEID=SWComplianceType.SWCOMPLIANCETYPEID LEFT JOIN SoftwareCategory ON SoftwareList.SOFTWARECATEGORYID=SoftwareCategory.SOFTWARECATEGORYID LEFT JOIN SoftwareInfo ON SoftwareGroupMember.SOFTWAREID=SoftwareInfo.SOFTWAREID LEFT JOIN WmiSWUsageInfo ON SoftwareInfo.SOFTWAREINFOID=WmiSWUsageInfo.SOFTWAREINFOID LEFT JOIN MaxUsedSwInfo ON WmiSWUsageInfo.SOFTWAREINFOID=MaxUsedSwInfo.SOFTWAREINFOID LEFT JOIN SystemInfo sysi on softwareinfo.workstationid=sysi.workstationid LEFT JOIN ResourceOwner reso on reso.RESOURCEID=sysi.WORKSTATIONID LEFT JOIN DepartmentDefinition ddef on ddef.DEPTID=reso.DEPTID LEFT JOIN UserDepartment udef on udef.USERID=reso.USERID LEFT JOIN DepartmentDefinition ddef1 on ddef1.DEPTID=udef.DEPTID left join sduser sdu on sdu.userid=reso.userid left join aaauser au on au.user_id=sdu.userid LEFT JOIN AaaLogin ON Au.USER_ID=AaaLogin.USER_ID LEFT JOIN SystemInfoDomain sysInfod ON sysi.WORKSTATIONID=sysInfod.WORKSTATIONID LEFT JOIN DomainInfo dominf ON sysInfod.DOMAINID=dominf.DOMAINID where SoftwareType.SOFTWARETYPE = 'Managed' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID, SoftwareList.SOFTWARENAME,au.first_name, dominf.DOMAINNAME order by 1

Rachana

Servicedeskplus