Missing Software Collection
Hello all,I am trying to create a collection for machines that do not have a piece of software installed (ie, machine is a member of collection X but does not have software X installed, so place in collection Y). Below is the query I have come up with, solely using the designer views in the Query and New Collection setups. I get a "Query is not valid" when I try to hit OK on the criteria window. Is something wrong here (I'm pretty bad with subselect queries, so hopefully I missed something obvious).select * from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select * 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 = "Software_Name")
March 13th, 2010 11:31pm

Hi,Don't have a console at hand right now, but your query should roughly be built like this:1. Select all ResourceID's from the Add Remove Programs table that have the DisplayName = 'Software Name' -> this is the subselect part and the key is to keep it simple and work with the ResourceID's.2. Select all from SMS_R_System that are NOT IN 1.HTH,Serge
Free Windows Admin Tool Kit Click here and download it now
March 14th, 2010 3:18am

In your subset query you can only have 1 column returned. Try this select * from SMS_R_System R inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = R.ResourceId where R.ResourceID not in ( select ARP.ReSourceID from SMS_G_System_ADD_REMOVE_PROGRAMS ARP where ARP.DisplayName = "Software_Name")http://www.enhansoft.com/
March 14th, 2010 4:48pm

Here is a query I use for machines that don't have quicktime installed: 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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select distinct SMS_G_System_COMPUTER_SYSTEM.Name from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId 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 like "%Quicktime%") and SMS_R_System.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 5.1" You don't have to use the last "and" part as I only grab Windows XP Machines. You can just replace the %quicktime% with whatever you would like to match for the software you are looking for. :)
Free Windows Admin Tool Kit Click here and download it now
March 16th, 2010 10:25pm

Sorry, I had forgotten I had this question here (It's coming time to TrueUp so my head's been filled with reports). Thanks for all of your replies, however. I ended up being able to use 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 where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId 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 = "software") AND SMS_R_System.Client = "1" One other question I had... I am trying to narrow down all of our Office 2007 installs.. I am writing a report to find them all with the exception of computers who belong to a specific collection (an affiliate company on our domain that has their own licensing terms with Microsoft). When I run, the report, the machines in that collection still appear. Any idea why? select distinct SYS.Name0, SYS.Resource_Domain_OR_Workgr0, ARP.DisplayName0 from dbo.v_R_System SYS inner join dbo.v_GS_ADD_REMOVE_PROGRAMS ARP on SYS.ResourceID = ARP.ResourceID where ARP.DisplayName0 = 'Microsoft Office Enterprise 2007' OR ARP.DisplayName0 = 'Microsoft Office Professional Plus 2007' AND SYS.ResourceID NOT IN (Select FCM.ResourceID from v_FullCollectionMembership FCM where FCM.CollectionID = 'xxx') order by SYS.Name0
March 29th, 2010 9:29pm

That should get you started: SELECT v_R_System.Name0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0FROM v_R_System INNER JOINv_GS_ADD_REMOVE_PROGRAMS ON v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceIDWHERE (v_R_System.ResourceID NOT IN (SELECT ResourceID FROM v_FullCollectionMembership WHERE ( CollectionID = 'sms000ds'))) AND (v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%microsoft office%')
Free Windows Admin Tool Kit Click here and download it now
March 29th, 2010 11:19pm

You are a lifesaver, Torsten. I modified it a bit to be exact for the software titles, and it worked perfectly. SELECT distinct SYS.Name0, ARP.DisplayName0 FROM dbo.v_R_System SYS INNER JOIN dbo.v_GS_ADD_REMOVE_PROGRAMS ARP ON SYS.ResourceID = ARP.ResourceID WHERE (SYS.ResourceID NOT IN (SELECT ResourceID FROM dbo.v_FullCollectionMembership FCM WHERE (CollectionID = 'xxx'))) AND (ARP.DisplayName0 = 'Microsoft Office Enterprise 2007' OR ARP.DisplayName0 = 'Microsoft Office Professional Plus 2007')
March 29th, 2010 11:48pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics