SCCM: Collection Query to Find Machines Discovered via AD System Discovery in the last day without latest SCCM Client

In the final days I have with my current employer, I’ve been doing some client cleanup. As you may know, Active Directory System Discovery can make a mess out of your SCCM environment if AD isn’t kept clean. We have a lot of records in our DB that just don’t have the SCCM Client for a variety of reasons (not enough disk space, WMI is broken, etc).

The good thing about AD System Discovery though is that for each record it finds in AD, it’ll look to DNS to see if there’s a corresponding DNS record. If there is, it’ll create a DDR for that machine. So if you have a lot of junk in AD, and DNS scavenging is set to a reasonable amount of time, you should be seeing machines in your SCCM hierarchy that are actually on your corporate network.

So what I set out to do was look for all the machines that have reported back an AD¬†System Discovery record¬†within the last day (technically, the query below is referencing 23 hours) that doesn’t have the latest version of the SCCM client (or the client version is null). Here’s the query.

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 ((DATEDIFF(hh, SMS_R_SYSTEM.AgentTime, getdate()) < 23) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”) and (SMS_R_System.ClientVersion < “4.00.6487.2000” or SMS_R_System.ClientVersion is null)

This gives me all the machines that I need to look into to fix. These are machines I wasn’t able to hit using Client Push installations, or at least I never got a successful client installation for them.

SCCM: Collection Query to get all machines who haven’t rebooted in X amount of Days

Here’s a query that I use to see all machines who haven’t rebooted in 7 days. You can change the 7 to however many days you want.

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where DATEDIFF(DD, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GETDATE()) > 7