Collection Query Logic

[mqh7]

I saw some old collection query code from SMS 2003, it does not work when I drop it into a SCCM 2007 collection. I looked at the code and I did not find all the matching data. Here is the code:

 

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 Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=15) and AgentName = "SMS_AD_SYSTEM_DISCOVERY_AGENT")) and Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=45) and AgentName = "Heartbeat Discovery"))

 

This is supposed to populate a collection with machines that have not been active in AD for 15 days. What I'm trying to figure out is how to identify broken clients in SCCM 2007. I know SCCM reads AD each night and finds all kinds of machines that are no longer alive. So this code above would be great but how do I code this for a SCCM 2007 collection?

 

 

 

 

 

 

 

 

[GarthMJ]

It looks fine, other than it is missing the opening "select" word... can we assume that you missed cut & pasted it?

[mqh7]

Yes, we can now assume that :-) But I added the SELECT and it does return many machines, all of which are showing as Live Clients. They have a site code, are approved, are assigned and client=Yes. If this code should show machines that are not active in AD how come they all show up as healthy SCCM clients?

[GarthMJ]

There are a lot of variable here.

• What are your maintenance task set to?
• Do you really want to know about PCs that haven't provide HW inventory in 45 days?
• Do you really want to know about PCs that haven't updated their record in 15 days?
• What make a PC inactive in AD?