Monday, February 13, 2012

SCCM: creating a collection based on installed software

I found this tip here (Thank you Kent);
http://blog.coretech.dk/kea/query-to-find-computers-without-net-framework-35-sp1-installed/

Query to all computers with .Net Framework SP1 installed

select distinct SMS_R_System.Name 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 .NET Framework 3.5 SP1"

Query to find all computer without .Net Framework SP1 installed

select distinct SMS_R_System.Name from  SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name 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 .NET Framework 3.5 SP1")
The tricky part of this query is creating the second part. You must use a subselected value where the attribute class is system resource and the attribute is Name. In Operator select Is not in and Browse to the query you created first.

2 comments: