Query Issue

Monday, November 02, 2009 8:36 PMBrennan1

0
Sign In to Vote
I am trying to create a collectionf or machine's that do not have a certain application installed.

To do this I created a Query for all machines that contain a certain service and .exe. I then created a Sub-Selected Query that includes all machines that "Is Not In" in the first query.
So now I am trying to create a collection using the 2nd query and I get the following error. The problem is both queries work fine on there own.

"the query statement that you entered is not valid. Please enter a valid statement"

Here is my query that it doesnt seem to like.

select SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "Pointsec" and SMS_G_System_SoftwareFile.FileName = "PointsecForPC.exe"

Any dieas on why it doesnt like my query? I came across a post that suggested deleting anything but the Resource.ID from the query but am not sure I understand exactly which lines to delete.

http://www.eggheadcafe.com/forumarchives/smsswdist/Nov2005/post24301547.asp
Lossless Audio Addict
- Reply
- Quote

Answers

Monday, November 02, 2009 10:19 PMMatthew Hudson [MVP]MVP, Moderator

0
Sign In to Vote
The easiest way to check for an application not installed is to use the Add/remove programs list.
Below is the query to find machines that don't have Java 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 "Java(TM) 6 Update 16")

To use your method should look like 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_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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "Pointsec" and SMS_G_System_SoftwareFile.FileName = "PointsecForPC.exe")
http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
- Proposed As Answer bySherry KissingerMVPMonday, November 02, 2009 10:21 PM
- Marked As Answer byMatthew Hudson [MVP]MVP, ModeratorTuesday, November 03, 2009 3:59 PM
- Reply
- Quote

All Replies

Monday, November 02, 2009 10:19 PMMatthew Hudson [MVP]MVP, Moderator

0
Sign In to Vote
The easiest way to check for an application not installed is to use the Add/remove programs list.
Below is the query to find machines that don't have Java 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 "Java(TM) 6 Update 16")

To use your method should look like 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_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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.Name = "Pointsec" and SMS_G_System_SoftwareFile.FileName = "PointsecForPC.exe")
http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
- Proposed As Answer bySherry KissingerMVPMonday, November 02, 2009 10:21 PM
- Marked As Answer byMatthew Hudson [MVP]MVP, ModeratorTuesday, November 03, 2009 3:59 PM
- Reply
- Quote
Tuesday, November 03, 2009 5:14 PMBrennan1

0
Sign In to Vote
I used your suggested method and was able to create the collection. Thanks!!!
Lossless Audio Addict
- Reply
- Quote
Tuesday, November 03, 2009 8:20 PMMatthew Hudson [MVP]MVP, Moderator

0
Sign In to Vote
You're very welcome. Creating the proper Collection can be trouble sometimes..
http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
- Reply
- Quote