Thursday, March 5, 2009

Using collections in Reverse

We all know you can create a collection to uninstall software but how about a collection Authorized software. This basically due to Google Earth. We run our computers in a locked down user only mode, so they can't install software. Well Google Earth gets around this by installing to the applications folder and in the profile. Users download it and then never update it so we create a collection to allow only authorized users to keep it. Here is how we did it.

Parent Collection (Computers allowed to run Google Earth)
Child Collection (Query and find every machine that has it not in the parent and uninstall every day.

Parent Collection is based on some queries and some direct memberships. Collection id is SYS00447
Child collection:

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 = "Google Earth" and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_SYS00447)

The child is limited to All Worstations so I can pull in all the computers and then we use the "not in parent collection ID"

Have fun with it....