Working with a customer today and all of their existing machines went back on lease. There was not a proper process in place to decomission the old AD machine accounts and SCCM records out of the site database.
To assist with the short term need to cleanup the old records, we first created the “whenchanged” attribute in AD System Discovery. This attribute once collected can then be used to create a query against all systems that have not had their machine account password changed in x amount of days. In this specific environment, we specified 60 days as the threshold. The query below was used to pull back all machines whose password’s have not changed in the last 60 days:
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(day, whenChanged, GetDate()) > 60)
Hope you find this useful!