Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

report sql

Created: 25 Feb 2013 • Updated: 13 Mar 2013 | 1 comment
This issue has been solved. See solution.

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