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