Hi all,

I'm fairly new to bespoke queries within SMS so any assistance would be much appreciated....

What I'm trying to do is create a new collection with clients that do not have the current version of a specific application or do not have the application at all. Querying clients for an old version isn't a problem but the query for clients without the application doesn't seem to work.

I've tried various different options, ie. product name is not like "app name" or "is not equal" or not in add remove programs but the query just comes back with the full estate...

Thanks. R
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2
So, you just need to create your query for what you do want to have as far as software critera but use a "not in" construction. Here's one I put together for Windows Update Client.

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_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId

where Client = 1 and Active = 1 and Obsolete = 0 and
SMS_G_System_SYSTEM.Name not in

(select SMS_G_System_SYSTEM.Name from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "wuauclt.exe" and SMS_G_System_SoftwareFile.FileVersion >= "5.8.0.2678" and ( SMS_G_System_SoftwareFile.FilePath like "C:\\WINNT\\system32\\" or SMS_G_System_SoftwareFile.FilePath like "C:\\WINDOWS\\system32\\") )

Regards,
William



ORIGINAL: sennen

Hi all,

I'm fairly new to bespoke queries within SMS so any assistance would be much appreciated....

What I'm trying to do is create a new collection with clients that do not have the current version of a specific application or do not have the application at all. Querying clients for an old version isn't a problem but the query for clients without the application doesn't seem to work.

I've tried various different options, ie. product name is not like "app name" or "is not equal" or not in add remove programs but the query just comes back with the full estate...

Thanks. R
Answered 11/02/2007 by: williamp
Orange Belt

Please log in to comment
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Thanks William that's just what I was after, much appreciated.
Answered 11/06/2007 by: sennen
Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity