Hi All,

i am looking for a query that will fetch the machines that do not have lync 2013 installed in them.

The conditions that should be used.

OS :  Windows 7

Domain: abc.test.com or Workgroup

Appwiz entry : Microsoft Lync 2013

You could try this but am not 100% sure about the workgroup and also for the application you could make a collection that as all the computer that have lync 2013 install and exclude it in this one if this doesn't work.

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_R_System.FullDomainName in ("abc.test.com","Workgroup") and SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%" and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%lync 2013%"

This will give you all machines that have the client installed that do not have lync 2013

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 = "lync 2013") and SMS_R_System.Client = 1

You could then just limit the results to a specific collection you already have setup.

 and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName not like "%lync 2013%"

That won't work because you need a subselect query then: See  http://www.mssccmfaq.de/2009/12/14/subselect-not-in-abfr

Here is a collection query, since I assume you are going to try to target these devices with Lync. Since you're looking for something not in the state of having Lync, that section uses a subselect 'not in' query. For more info on subselect queries, try this article.

(** Frederick's query won't work.  Everyone has some sort of software that is 'not like' Lync. Instead, we use a subselect to filter out the people who do have Lync.)

 

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_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId
where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%"
and SMS_R_System.ResourceDomainORWorkgroup in ("ABC.TEST.COM","WORKGROUP")
and SMS_G_System_WORKSTATION_STATUS.LastHardwareScan > "1/1/2001 12:00:00 AM"
and 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 like "Microsoft Lync%2013"
)

This query is checking for the OS and Domain from Discovery data, but I added a check for the last time there was a hardware scan to filter out device that have not sent Hardware Inventory data. If you omit such a check, people who don't have any Hardware Inventory data reported would get caught up in this query, even though they may have the app already installed but we just don't know about it yet.

I hope that helps!

 

Nash

yeah that what i read thanks for the confirmation. That why i said he might need to make a collection that as all the client with Lync 2013 install and use that one to with a exclusion in the new one.

Could simply make a Desired base line configuration that validate if it`s install and make a collection that is fill with the non compliant.

nice query!

• Edited by Richard.Knight 16 hours 15 minutes ago

Just threw in an edit to have it look for ABC.TEST.COM domain or WORKGROUP instead of my pilot/test environment.  As this is using the ResourceDomainOrWorkgroup attribute, be sure to check what actually shows up for your domain, as it may just be ABC.

I'd typically not use a separate collection / exclusion rule as it complicates the update of this collection. If I need a list of folks who do have it, it would be a separate unrelated collection for me - I want this one to be able to stand on its own especially since I'm likely to target application deployments to it.

I'm not sure what value adding a compliance rule / baseline for this app would bring since you are already collecting this data via hardware inventory. I'd only go that route if I'm trying to validate something that isn't available through inventory data, that I need validated more often than inventory is running, or that I want to use automatic remediation with (registry values, etc).

 

I hope that helps!

 

Nash

Keep in mind that all of the above queries will show you computers with x64 version of Lync. All of them will need to be adjusted to filter out x64 version of lync.

This blog post will get you started.

http://www.enhansoft.com/blog/subselect-wql-query-to-find-pcs-that-do-not-have-either-x86-or-x64-versions-of-software-installed

Thanks for the details Query Nash. i always wanted to create a single dynamic collection and don't want to create 2 separate collection. We have Windows 7 64 bit machines in our environment and have rolled out 32 bit version of Office suite and Lync 2013. I believe the above query will still fetch the correct results. i ma not worried if the package is pushed to machines with Lync already in them. We did a test deployment and found the package did not reinstall and it identified the machine is already compliant.