Look into subselect queries. You can't do a 'not installed'. You have too say any machines not in this query, this query being the one with the software installed.
-Daniel Ratliff -----Original Message----- From: Johns, Damon (DoJ) [damon.jo...@justice.tas.gov.au<mailto:damon.jo...@justice.tas.gov.au>] Sent: Tuesday, February 04, 2014 06:01 PM Eastern Standard Time To: 'mssms@lists.myitforum.com' Subject: [mssms] Query for x86 and x64 software that is 'not' installed Hi Everyone, I need to build a query for a collection to determine which computers are missing some software. This particular app has an x86 and x64 installer. The software inventory picks up the presence of this app and stores the data in the usual SMS_G_System_ADD_REMOVE_PROGRAMS and in SMS_G_System_ADD_REMOVE_PROGRAMS_64 Does anyone have a query to hand that will do this for me? I have plenty of queries which will do one but not both. I have found this: 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 inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "SSRPM User Client Software" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "SSRPM User Client Software x64" Which lists when it is present on computers – but my understanding of SQL queries is pretty woeful so I’m uncertain as to how to alter the join and logic so it returns clients that do not have the software installed. Cheers Damon ________________________________ CONFIDENTIALITY NOTICE AND DISCLAIMER The information in this transmission may be confidential and/or protected by legal professional privilege, and is intended only for the person or persons to whom it is addressed. If you are not such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received the transmission in error, please immediately contact this office by telephone, fax or email, to inform us of the error and to enable arrangements to be made for the destruction of the transmission, or its return at our cost. No liability is accepted for any unauthorised use of the information contained in this transmission. The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.