SCCM SQL Query to Determine Installed Browsers
I recently wrote the following SQL Query to help identify which browsers are installed across our environment. A straight Add/Remove Programs (v_AddRemovePrograms) query didn't really return the data in such a way that made it easy to determine major version of Firefox or Chrome, and doesn't include Internet Explorer since that's not listed in Add/Remove Programs. This view alone also does not capture user based installations of Chrome. You may need to modify slightly for your environment.
1/* Find Firefox, Chrome (System), Opera, and Safari */
2select distinct v_R_System.Name0
3 , v_add_remove_programs.Publisher0
4 , v_add_remove_programs.displayname0
5 , case when charindex('Firefox', v_add_remove_programs.displayname0) > 0 then 'Mozilla Firefox'
6 when charindex('Chrome', v_add_remove_programs.displayname0) > 0 then 'Google Chrome'
7 when charindex('Opera', v_add_remove_programs.displayname0) > 0 then 'Opera'
8 when charindex('Safari', v_add_remove_programs.displayname0) > 0 then 'Apple Safari'
9 else v_add_remove_programs.displayname0
10 end BrowserTitle
11 , v_add_remove_programs.Version0
12 , case when charindex('.',v_add_remove_programs.version0) > 0 then substring(v_add_remove_programs.version0, 1, charindex('.',v_add_remove_programs.version0)-1)
13 else v_add_remove_programs.version0
14 end version_major
15 , v_r_user.departmentnumber0
16 , v_r_user.Full_User_Name0
17 , v_r_user.Mail0
18 , v_r_user.title0
19 , v_r_user.company0
20 , v_r_user.employeeID0
21 from v_R_System
22 join v_Add_Remove_Programs
23 on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID
24 left join v_GS_SYSTEM_CONSOLE_USAGE
25 on v_r_system.resourceid = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
26 left join v_R_User
27 on v_r_user.Unique_User_Name0 = v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0
28 where v_r_system.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1%' and
29 (
30 (v_add_remove_programs.displayname0 like '%Firefox%' and v_Add_Remove_Programs.Publisher0 like '%Mozilla%')
31 or (v_add_remove_programs.displayname0 like '%Safari%' and v_Add_Remove_Programs.Publisher0 like '%Apple%')
32 or (v_add_remove_programs.displayname0 like '%Opera%' and v_Add_Remove_Programs.Publisher0 like '%Opera%')
33 or v_add_remove_programs.displayname0 = 'Google Chrome'
34 )
35
36 union all
37
38/* Union IE Users by File Name */
39select distinct v_R_System.Name0
40 , 'Microsoft' as Publisher0
41 , 'Internet Explorer' as Displayname0
42 , 'Internet Explorer' as BrowserTitle
43 , v_GS_SoftwareFile.FileVersion as Version0
44 , case when charindex('.',v_GS_SoftwareFile.FileVersion) > 0 then substring(v_GS_SoftwareFile.FileVersion, 1, charindex('.',v_GS_SoftwareFile.FileVersion)-1)
45 else v_GS_SoftwareFile.FileVersion
46 end version_major
47 , v_r_user.departmentnumber0
48 , v_r_user.Full_User_Name0
49 , v_r_user.Mail0
50 , v_r_user.title0
51 , v_r_user.company0
52 , v_r_user.employeeID0
53 from v_R_System
54 join v_GS_SoftwareFile
55 on v_GS_SoftwareFile.ResourceID = v_R_System.ResourceID
56 join v_GS_OPERATING_SYSTEM
57 on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
58 join v_GS_WORKSTATION_STATUS
59 on v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceID
60 join v_biib_system_ou
61 on v_R_System.Netbios_Name0 = v_biib_system_ou.Netbios_Name0
62 left join v_GS_SYSTEM_CONSOLE_USAGE
63 on v_r_system.resourceid = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
64 left join v_R_User
65 on v_r_user.Unique_User_Name0 = v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0
66 where v_r_system.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1%'
67 and v_GS_SoftwareFile.FileName = 'iexplore.exe'
68 and v_GS_SoftwareFile.FilePath like '%:\Program Files%\Internet Explorer%'
69 and v_GS_SoftwareFile.FilePath not like '%Recycle%'
70
71union all
72
73/* Union Google (User) */
74select distinct v_R_System.Name0
75 , 'Google' as Publisher0
76 , 'Google Chrome' as Displayname0
77 , 'Google Chrome' as BrowserTitle
78 , v_GS_SoftwareFile.FileVersion as Version0
79 , case when charindex('.',v_GS_SoftwareFile.FileVersion) > 0 then substring(v_GS_SoftwareFile.FileVersion, 1, charindex('.',v_GS_SoftwareFile.FileVersion)-1)
80 else v_GS_SoftwareFile.FileVersion
81 end version_major
82 , v_r_user.departmentnumber0
83 , v_r_user.Full_User_Name0
84 , v_r_user.Mail0
85 , v_r_user.title0
86 , v_r_user.company0
87 , v_r_user.employeeID0
88 from v_R_System
89 join v_GS_SoftwareFile
90 on v_GS_SoftwareFile.ResourceID = v_R_System.ResourceID
91 join v_GS_OPERATING_SYSTEM
92 on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
93 join v_GS_WORKSTATION_STATUS
94 on v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceID
95 join v_biib_system_ou
96 on v_R_System.Netbios_Name0 = v_biib_system_ou.Netbios_Name0
97 left join v_GS_SYSTEM_CONSOLE_USAGE
98 on v_r_system.resourceid = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
99 left join v_R_User
100 on v_r_user.Unique_User_Name0 = v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0
101 where v_r_system.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1%'
102 and v_GS_SoftwareFile.FileName = 'chrome.exe'
103 and v_GS_SoftwareFile.FilePath like 'C:\Users\%\AppData\Local\Google\Chrome%'
104 and v_GS_SoftwareFile.FilePath not like '%Recycle%'
105 and v_GS_SoftwareFile.FilePath not like '%Temporary Internet Files%'
106 and v_GS_SoftwareFile.FilePath not like '%Chrome Frame%'
107 and v_r_system.ResourceID not in (select ResourceID from v_Add_Remove_Programs where DisplayName0 = 'Google Chrome')
3 comments
Thanks for putting this together! Nice work!
How could this be converted to Collection for getting Chrome information (Both machine and user installs)?
Desperately need this as a collection, struck out with everything I have tried so far.