You could use one of the available "client health scripts": http://blogs.catapultsystems.com/jsandys/archive/2010/10/19/configmgr-client-startup-script.aspx, but there are also others available.Torsten Meringer | http://www.mssccmfaq.de

Hello, I have two queries that i need to combine to get all systems that do not have the client installed and are windows 7 systems, I have the two queries but i do not know how to combine them. I have seen people using INTER JOIN but i do not know how to use it. Here are my two queries All systems with out a 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 where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.Client = 1) All Windows 7 Systems 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 OperatingSystemNameandVersion like '%Workstation 6.1.7600' I sure this is not that difficult but any help would be greatly appreciated. Thanks

Since it's all one table (SMS_R_SYSTEM), you can just add the additional statement to your WHERE clause 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 where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System where SMS_R_System.Client = 1) and OperatingSystemNameandVersion like '%Workstation 6.1.7600' However, it'll run a little faster if you replace your subselect in the first WHERE statement so that it doesn't have to run another query to get the records where client = 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 where SMS_R_System.Client <> 1 and OperatingSystemNameandVersion like '%Workstation 6.1.7600'

Thank you all for you responses, these queries did pull back what i was looking for although i have another problem. I wanted that query to get all of the systems that had a corrupt SCCM client on the system and i had thought that all of the corrupt clients were reporting to SCCM that the client was not installed. I have found that a majority of the systems that have corrupt clients say that the client is installed in the SCCM console. One way that i can identify quickly if the client is corrupt is i go to the client and on the General tab i look at the site mode and if the site mode says Unknown then the client is corrupt and if the site mode says Mixed Mode that the client is OK. So i need a query that will pull clients that have the site mode as Unknown so i can get a good idea of how many corrupt clients are out there. Unfortunately i don’t know where to even start with this one, any thoughts?

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 where SMS_R_System.Client = 1) and SMS_R_SYSTEM.OperatingSystemNameandVersion like "%Workstation 6.1.7600"

Do a subselect. Like this (untested) 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 where SMS_R_System.Client = 1) and resourceID in (select SMS_R_SYSTEM.ResourceID from sms_r_system where OperatingSystemNameandVersion like '%Workstation 6.1.7600')John Marcum | http://myitforum.com/cs2/blogs/jmarcum/|

That won't work, because "corrupt" clients won't most likely send any inventory data to ConfigMgr any longer.Torsten Meringer | http://www.mssccmfaq.de

Any ideas on how i can figure out how many corrupt clients are out there?

Hi, I would say , You can use below report to find out clients whose LastHWScan date > x # days (ex. 30 days) so that you can fix/repair them... Report Category: SMS Site - Discovery and Inventory Information Report Name : Clients that have not reported recently (in a specified number of days) Regards,