create collection from query

Glenn Maxwell 11,096 Reputation points
2021-04-07T14:57:37.337+00:00

Hi Experts

i have a collection lets say TEST which has windows 2012R2 Servers, Windows 2016 Servers and Windows 2019 servers.
Some servers in this collection have browsers installed. i want to pull all the servers which have Google Chrome installed from this collection and create a new collection
of servers having chrome installed lets say TEST_Chrome. in the same way i want to do for firefox as well. will the below query fetch me the browser information and how to create a collection from query.

in Add/Remove programs for chrome i can see Google Chrome, for firefox on one server i can see Mozilla Firefox 80.0.1 (x64 en-US)(servers may have different versions)

For firefox will the below query work(in Add/Remove Programs its Mozilla Firefox 80.0.1 (x64 en-US) on one server)

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%Firefox%"

For Chrome will the below query work(in Add/Remove Programs its Google Chrome)

SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%Google Chrome%"

if there is any 32bit version how do i pull that also

Microsoft Configuration Manager Application
Microsoft Configuration Manager Application
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Application: A computer program designed to carry out a specific task other than one relating to the operation of the computer itself, typically to be used by end users.
480 questions
Microsoft Configuration Manager
0 comments No comments
{count} votes

Accepted answer
  1. Sherry Kissinger 4,466 Reputation points
    2021-04-10T20:25:43.74+00:00

    Thanks for the explanation of your goals. With that in mind, here are some various and assorted suggestions (any and all might work).

    Best Solution: If you have any kind of budget at all for buying add-ons for ConfigMgr, Do Not Pass Go, Do Not Collect $200, and go straight here: https://patchmypc.com/third-party-patch-management-for-sccm-and-intune . learn about it, grab their demo; justify the cost to whomever holds the budget. If you can afford to get that, once it's setup and running, you'll likely never worry about how to patch Firefox or Chrome again.

    Other suggestions:
    Yes, doing what you are attempting to do; make collections so you can specifically target applications you create to deploy/update Chrome and Firefox will work. It's just a lot of manual work (by you) to do that. Nothing wrong with that approach. It's just you have to do the collections (and verify they are correct), you have to make the applications (and test they work).

    Another "possibility" (if you can't do PatchMyPC, and you potentially want to reduce Collections); but it also means YOU have to do a lot of verification yourself. You "could" not have collections at all. If you design your Google Chrome application (for example), to have a custom Global Condition, where that Global Condition looks for, let's say... "%programFiles%\whatever\chrome.exe must exist", then when you do a Simulated Deployment (for testing your application logic), deployed to "All Servers", and look at Deployment Reports, you'll see that machines that don't have chrome.exe in that folder would report back "Requirements Not Met", so chrome wouldn't install. and then of course your Deployment Type would have the installation for the latest chrome, and the Verification would be the MSI and version of that version of Chrome. (Since you are in the stage of making collections, I'm guessing you already have a working application; but maybe not; sorry if I'm over-explaining something you already know and are rolling your eyes right now)

    Personally, I'd try to see if you can get the budget approved for PatchMyPC, and get that working. PatchMyPC isn't only for Firefox/Chrome--they also provide third party patches for many other things as well.

    PS: I don't work for them or anything; I just know that currently, they are the best of the breed for 3rd party patching integrated with ECM (there are other vendors, so if you have to do comparisons for your company internal processes, you can look for the other vendors that integrate w/CM for 3rd party patching to compare and contrast)

    0 comments No comments

6 additional answers

Sort by: Most helpful
  1. Sherry Kissinger 4,466 Reputation points
    2021-04-07T20:50:20.67+00:00

    I think Garth's link is for, for example "two wql queries for Firefox... where one is for the 64 bit, and the other is for the 32bit"

    PS: personally, I find it beyond annoying when people use like '%something%' with that leading %, you are making SQL/WQL work much harder than you have to.

    use like 'Mozilla Firefox%' and like 'Google Chrome%', without the leading %. It's just a pet peeve of mine, but I try to point that out anytime I see someone use like '%something%' that there is a kinder gentler way to be nice to your system; and still get the results you want.

    1 person found this answer helpful.
    0 comments No comments

  2. Garth 5,801 Reputation points
    2021-04-07T16:37:36.433+00:00
    0 comments No comments

  3. Glenn Maxwell 11,096 Reputation points
    2021-04-07T17:21:46.39+00:00

    i dont want to combine two queries iwant to create separate collection one for Firefox and one for chrome

    0 comments No comments

  4. Glenn Maxwell 11,096 Reputation points
    2021-04-08T08:24:51.587+00:00

    should i use the query in this way, can anyone validate if this query is correct

    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like 'Mozilla Firefox%'
    
    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like 'Google Chrome%'
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.