SCCM Collection–how to identify devices that have old AD system discovery timestamp ?

SCCM has multiple discovery methods help you discover devices on your network, devices and users from Active Directory, or users from Azure Active Directory (Azure AD). Read more about the discovery methods in SCCM

AD system discovery help to discover computer resources that can be used to create collections and queries. You can also install the SCCM Client client on a discovered device by using client push installation.

In order to successfully discover the computer (by creating the DDR Record) in domain by AD system Discovery , it must be able to identify the computer account and then successfully resolve the computer name to an IP address (DNS name resolution).

You can check Active Directory System Discovery logs which gets recorded in the log file adsysdis.log in the <InstallationPath>\LOGS folder on the site server.

In this blog post, we will see how to create SCCM device collection to identify devices that have old AD system discovery time stamp (older than 14 days) . This will help us to find the devices that are having issues while discovering though AD system discovery(SMS_AD_SYSTEM_DISCOVERY_AGENT) ,like the devices that are deleted from Active Directory but still in SCCM or having DNS name resolution.

Create a new collection ,edit the query and past the following and click.

Collection (WQL 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(dd, SMS_R_SYSTEM.AgentTime, getdate()) > 14) and AgentName ="SMS_AD_SYSTEM_DISCOVERY_AGENT")

Devices might appear in this device collection may have SCCM agent installed and healthy but they are failed to discovery through AD system discovery from its last discovery date is older.

If the devices are deleted in AD but still in SCCM means with no active client, they are yet to be cleanup using site maintenance tasks.

If you want to find the devices that are not reported via specific agent with old timestamp , then simply replace the AgentName in the above collection.

There are different discovery agents available in SCCM, listed below.

SQL Query for list of discovery agents:

select AgentName from v_AgentDiscoveries

group by AgentName




Heartbeat Discovery








If you want SCCM Collection for active inactive computers using Last Logon timestamp and troubleshooting ,you can refer this post

Hope it helps!

Leave a Reply