Jump to content


mqh7

Collection Query Logic

Recommended Posts

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?

 

 

 

 

 

 

 

 

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...