One of the most asked question in various newsgroups is: How do I create a query that shows all computers without “something”. To create a query like that you start by creating a query that find the opposite and then a query that finds all computers not part of the first result.

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.

Originally posted to http://agerlund.spaces.live.com/blog/cns!3A51A2B50B5C1F51!357.entry