The existing Purchased vs. Installed software report is pretty decent across the enterprise. I need to add/adjust the query so that I can display the same results with;
1) The site name displayed with the rest of the output
2) The output of the existing report grouped by site name.
If I can get what I need for number 1, I can do 2 using external tools.
The SQL statement for the current report is inserted below:
SELECT Max(SoftwareList.SOFTWARENAME) "Software",Max(swmfg.NAME) "Manufacturer",Max(SoftwareType.SOFTWARETYPE) "Type",Max(SoftwareCategory.SOFTWARECATEGORY) "Category",FLOOR(sum(SoftwareLicenses.INSTALLATIONSCOUNT)/( CASE WHEN COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) = 0 THEN 1 ELSE COUNT(DISTINCT(SoftwareInfo.SOFTWAREINFOID)) END )) '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 SoftwareManufacturer swmfg ON SoftwareList.SWMANUFACTURERID = swmfg.SWMANUFACTURERID where SoftwareType.SOFTWARETYPE = 'Managed' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID