Hmm I'll play with it tomorrow when I'm in front of a pc. That query should work. Glad you got a workaround though!
-Daniel Ratliff -----Original Message----- From: Johns, Damon (DoJ) [[email protected]<mailto:[email protected]>] Sent: Tuesday, February 04, 2014 07:23 PM Eastern Standard Time To: '[email protected]' Subject: RE: [mssms] Query for x86 and x64 software that is 'not' installed Ok I have managed to resolve this by first creating collections for all x86 and x64 based systems, Them creating a collection targeting clients without the x64 based app limiting it to the x64 based system query, then doing the same for the x86 based app. If I included both queries in 1 collection they sort of cancelled each other out and I ended up with all the computers. Thanks Daniel. From: [email protected] [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Wednesday, 5 February 2014 10:30 AM To: '[email protected]'; '[email protected]' Subject: RE: [mssms] Query for x86 and x64 software that is 'not' installed Yeah just duplicate your first where. ...where resourceid not in (query for x86) and resourceid not in (query for x64) -Daniel Ratliff -----Original Message----- From: Johns, Damon (DoJ) [[email protected]<mailto:[email protected]>] Sent: Tuesday, February 04, 2014 06:23 PM Eastern Standard Time To: '[email protected]' Subject: RE: [mssms] Query for x86 and x64 software that is 'not' installed Yeah I need to deploy a package (not an app) within CM 2012 R2 to clients that don’t have it installed thus the request for a query to ultimately have a collection with only those clients that are missing the software. So it’s not about producing a list of devices more about having a way of targeting them. We have been going through a process of removing x86 instances of Windows 7 for around 18 months as we replace computers. We have at present around a 60/40 mix of x64 and x86 Windows 7 Operating Systems. Daniel – just as an example I have a query that I have used in the past to determine is something isn’t installed: select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from SMS_R_System as sys where sys.ResourceId not in (SELECT ARP.ResourceID FROM SMS_G_System_ADD_REMOVE_PROGRAMS AS ARP WHERE ARP.DisplayName = "Update for Windows XP (KB943729)") please ignore the xp thing – it’s an old query that I dug out my scripts folder. This one returns clients without that hotfix present in the SMS_G_SYSTEM_ADD_REMOVE_PROGRAMS table – surely you can do one that does both this table and the SMS_G_SYSTEM_ADD_REMOVE_PROGRAMS_64 table ? From:[email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Dzikowski, Michael Sent: Wednesday, 5 February 2014 10:07 AM To: '[email protected]'; '[email protected]' Subject: RE: [mssms] Query for x86 and x64 software that is 'not' installed I know you asked for a query, but what about using the application deployment simulation (assuming you're on CM12)? http://technet.microsoft.com/en-us/library/hh508773.aspx Sent with Good (www.good.com<http://www.good.com>) -----Original Message----- From: Johns, Damon (DoJ) [[email protected]<mailto:[email protected]>] Sent: Tuesday, February 04, 2014 05:00 PM Central Standard Time To: '[email protected]' 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. ________________________________ 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. ________________________________ 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.
