count specific versions of the different products
I am looking for a query that could be used as part of a report that could count specific versions of the different products as specified in the query? As an example I would count two versions of both Adobe Reader and Win Zip. I have an existing query, but I am not sure if it can be altered enough to accomplish what I am trying to do. I will post it anyways, but I suspect an entirely new twist would need to be done on it to accomplish what I am looking to do. By the way this query was provided to me and I can confirm it works in 7.0, but I am not sure with 7.1.
vCom.[Name] as [Computer Name]
,cs.[Identifying Number] AS [Serial Number]
,cs.[Total Physical Memory (Bytes)]
,ac.[Last Logon User]
,[Sophos Installed] = (
WHEN sarp.[DisplayName] IS NULL THEN 'No'
,[Sophos Version] = (
WHEN sarp.[DisplayVersion] IS NULL THEN ''
,vCom.[Domain] AS [Domain Name]
,vCom.[OS Name] AS [Operating System]
,vCom.[OS Revision] AS [Service Pack]
vComputer vCom -- 1-1
FULL JOIN CollectionMembership cm ON cm.[ResourceGuid] = vCom.[Guid] -- 1-Many
FULL JOIN vCollection vColl ON cm.[CollectionGuid] = vColl.[Guid] -- 1-Many
FULL JOIN Inv_AeX_AC_Identification ac on ac.[_ResourceGuid] = vCom.[Guid] -- 1-1
FULL JOIN vHWComputerSystem cs ON cs.[_ResourceGuid] = vCom.[Guid] -- 1-1
arp.[DisplayName] LIKE 'Sophos SafeGuard 6.00.0 Client'
arp.[DisplayName] LIKE 'Sophos SafeGuard 6.00.1 Client'
) sarp ON sarp.[_ResourceGuid] = vCom.[Guid]
vCom.[IsManaged] = 1
vColl.[Name] = 'Windows 2000/XP/2003/Vista/2008/7 Computers'
vCom.[Name] not like '%-%'
vCom.[IP Address] Not like '%10.4.254%'
vCom.[IP Address] Not like '%192.168%'
cs.[Identifying Number] is Not Null
ORDER BY vCom.[Name]
Any suggestions appreciated and thanks.