locked
Create a collection and exclude members from a different collection RRS feed

  • Question

  • I am trying to push Microsoft Forefront to all computers in a computers in our company. I'm new with SCCM, so i'm still trying to figure it out.

     

    I have already pushed the software to about 35% of all of our computers and management is ready to let me deploy it to the rest. I already have a collection that contains all computers with the software by searching for the .exe file. I need to create a collection to deploy the software to the rest of the computers, but I want to exclude all the computers that already have the .exe file.

     

    I'm not even close to an expert with SQL, but I know the basics well enough to read through a query.

     

    Any ideas how I should procede?

     

     

     

    Here's the query statement for the computers that have Forefront:

     

    select 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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "MSASCui.exe"

    Saturday, November 22, 2008 2:45 PM

Answers

  •  

    Here's two queries that I used for two collections that just so happen to also be for forefront. The first is all the machines with FCS the second is all the machines not in the first. I have these collections limted to other collections that just contain valid clients. Also you need to change the COLID in the second one to match the COLID of the first one in your environment.

     

     

    select 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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Client Security State Assessment Service"

     

     

     

     

     

    select 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 where SMS_R_System.ResourceId not in (Select ResourceID from SMS_CM_RES_COLL_CCC005E6)

    Saturday, November 22, 2008 10:21 PM

All replies

  •  

    Here's two queries that I used for two collections that just so happen to also be for forefront. The first is all the machines with FCS the second is all the machines not in the first. I have these collections limted to other collections that just contain valid clients. Also you need to change the COLID in the second one to match the COLID of the first one in your environment.

     

     

    select 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 on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Forefront Client Security State Assessment Service"

     

     

     

     

     

    select 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 where SMS_R_System.ResourceId not in (Select ResourceID from SMS_CM_RES_COLL_CCC005E6)

    Saturday, November 22, 2008 10:21 PM
  •  

    That's exactly what I was looking for! Thanks!

     

    Sunday, November 23, 2008 3:22 AM
  • Hello,

    In fact, you would note that John has suggested that you use Add Remove Programs rather than software inventory to target your quarry.

    Another thing is if you are using the same advertisement, you can expand the scope of your collection to include everything or an subset of everything else, the need for an exclusive collection is really moot because if the advertisement was successful it would not reRun except if you reschedule it to reRun.

    You can also create new collections and use linked collections whilst deploying the said package through the same advertisement to sub-collections.

    The query suggestion you were offered is neater and contextually, do not create a new advertisement for the same package and command-line just play around better with the collections and trust in the ability of system to recognise successful deployments, failed deployments and pending deployments.

    Regards,

    Akin
    Sunday, November 23, 2008 11:51 AM
  • @ John,

    This was very helpful to get things started for me, however I would like to exclude members from multiple collections not just a single one as shown in your example. I have tried a few things and keep getting synax errors. Hopefully you are still monitoring this post.

    Tuesday, October 5, 2010 12:25 AM