This code shows me the Microsoft software installed on a computer, also tell me how many are installed.
example:
software version guid total
microsoft office 2003 1.3.65.54. as4a-sdf87e5-8fsd4f 250
the problem is, that if there is an office 2003 in Spanish and one in English, repeats and counted it as another
example:
software version guid total
microsoft office 2003 (español) 1.3.65.54. as4a-sdf87e5-8fsd4f 15
microsoft office 2003 (ingles) 1.3.65.54. as4a-sdf87e5-8fsd4f 8
microsoft office 2003 1.3.65.54. as4a-sdf87e5-8fsd4f 8
--------------------------prevent count as separate. I put them together regardless of language
SELECT DISTINCT
dca5_AddRemoveProgram.DisplayName AS Software,
dca5_AddRemoveProgram._SoftwareComponentGuid AS GUID,
dca5_AddRemoveProgram.DisplayVersion AS Version,
dca5_AddRemoveProgram.Publisher AS Compañia,
COUNT(dca5_AddRemoveProgram.DisplayName) AS Total
FROM
Inv_AddRemoveProgram AS dca5_AddRemoveProgram
INNER JOIN
vComputer AS vComputer ON vComputer.Guid = dca5_AddRemoveProgram._ResourceGuid
WHERE
dca5_AddRemoveProgram.DisplayName LIKE 'microsoft%' AND --dca5_AddRemoveProgram.DisplayName LIKE 'microsoft%' AND
((dca5_AddRemoveProgram.DisplayName NOT LIKE '%web%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%MUI%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%primary%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%security%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%runtime%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%activation%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%update%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%live%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%components%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%viewer%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%programs%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%connector%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%service pack%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%trial%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%labs%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%plugin%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%proof%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%library%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%evaluation%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%sample%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%templante%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%lenguage%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%engine%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%add-in%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%inside%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%resource kit%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%chinese%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%Viewer%') AND
(dca5_AddRemoveProgram.DisplayName NOT LIKE '%Library%') AND
(dca5_AddRemoveProgram.InstallFlag LIKE '1'))
GROUP BY dca5_AddRemoveProgram.DisplayName,
dca5_AddRemoveProgram.DisplayVersion,
dca5_AddRemoveProgram.Publisher,
dca5_AddRemoveProgram._SoftwareComponentGuid