Software report on Workstations not with the disposed status
Hi,
I will like to be able to run a report similar to the 'Software Reports - Purchased Vs Installed software' but for all workstations except 'disposed' ones. At the moment this report include installed software on disposed workstations also.
I have tried to add (Resource.RESOURCESTATEID <> 5) to the query below but did not work.
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 SoftwareType.SOFTWARETYPE = 'Managed' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID
Can you please provide a query to exclude 'disposed' workstations and to also keep 'Managed' software where purchased=0.
Many thanks
CS
I will like to be able to run a report similar to the 'Software Reports - Purchased Vs Installed software' but for all workstations except 'disposed' ones. At the moment this report include installed software on disposed workstations also.
I have tried to add (Resource.RESOURCESTATEID <> 5) to the query below but did not work.
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 SoftwareType.SOFTWARETYPE = 'Managed' GROUP BY SoftwareGroupMember.PARENTSOFTWAREID
Can you please provide a query to exclude 'disposed' workstations and to also keep 'Managed' software where purchased=0.
Many thanks
CS