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.
Thanks very much. I was looking for this only.
I have the same software with three names in the add and remove, because of the different versions and need to figure out when it is not installed. Could you help with this issue? Tks!
Because of the different versions, I have the same software with three names in the add and remove and need to figure out when it is not installed so if the name does not exist in any of the three names requested then the machine will appear in the report but when at least one name is present then this machine should not appear.
Could you help with this difficult question for me?
Thank you!