WQL Query to find an application not in ADD/Remove

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

WQL Query to find an application not in ADD/Remove

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> WQL Query to find an application not in ADD/Remove

Page: [1]

Message

<< Older Topic Newer Topic >>

WQL Query to find an application not in ADD/Remove - 10/3/2008 6:39:56 AM

drake21

Posts: 21
Score: 0
Joined: 9/6/2006
Status: offline

Does anyone know the syntax for a WQL query to return all the machines where an application/patch is not displayed in ADD/Remove programs? I'm trying to create the query and it's returns every machine.

Thanks

Post #: 1

RE: WQL Query to find an application not in ADD/Remove - 10/3/2008 7:01:29 AM

Rickym61

Posts: 92
Score: 4
Joined: 4/4/2007
Status: offline

Might be easier to do the following.

#1
Create a collection for all machines with the patch showing in Add/Remove
#2
Create a collection with all machines, but do not show those with the patch installed

#1
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 where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB946983%" order by SMS_R_System.OperatingSystemNameandVersion

#2
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 where SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{90120000-0011-0000-0000-0000000FF1CE}" and SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_P0001340)

The above uses a subselect to not shows machines from a certain collection i.e. #1

I did this to install a load of Hotfixes for Office 2007, but had to make sure they didnt have a certain patch installed. It has worked a treat for me.

Might get the SQL guru aka #2 provide you with a better query, but the above should work.

(in reply to drake21)

Post #: 2

RE: WQL Query to find an application not in ADD/Remove - 10/3/2008 9:08:29 AM

mhudson

Posts: 541
Score: 12
Joined: 4/1/2007
From: College Station, TX
Status: offline

To do this MUST use a sub select statment to find all computers that have it and then exclude them. Here is an example

Find all computers that DO NOT have Office Project:

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 "%Microsoft Office Project%")

_____________________________

Matthew Hudson
http://sms-hints-tricks.blogspot.com/
http://www.sccm-tools.com

(in reply to Rickym61)

Post #: 3

RE: WQL Query to find an application not in ADD/Remove - 10/3/2008 11:30:12 AM

drake21

Posts: 21
Score: 0
Joined: 9/6/2006
Status: offline

Got it working. Thanks!

(in reply to drake21)

Post #: 4