PowerShell script to query SCCM "stale" records?

Is there a way using PowerShell to query SCCM for “old” records that are no longer needed? In other words, I would like to be able to query and/or delete any records of devices in SCCM that have not “checked in” with SCCM in the last 90 days. I am also going to cross reference this with Active Directory, and I know how to do that…but I am wondering if I can first check SCCM for “stale” records, or if this is even the best way to go about it. Ideally I want to just start with a list of devices (in a specific collection) that have not checked in to SCCM in 90+ days, and I have seen several possibilities - Get-CMDevice, get-wmiobject, etc. - I would prefer to NOT have to use the SCCM cmdlets, and would like to use get-wmi if possible so it can be managed from any machine. Thank you.

To be more specific, here is the logic of what I want to accomplish:

QUERY DEVICE COLLECTION IN SCCM
GET LAST HARDWARE SCAN DATE OF EACH DEVICE
IF LAST HARDWARE SCAN DATE IS OLDER THAN 90 DAYS
DOES DEVICE EXIST IN ACTIVE DIRECTORY?
NO - DELETE FROM SCCM
YES - PING DEVICE
DOES NOT RESPOND? DELETE FROM AD AND SCCM
RESPONDS? WRITE COMPUTER NAME TO .CSV

Okay - here is what I have done so far. I actually created a new collection in SCCM and applied a WQL query to it, so I have a collection of computers that have not registered a heartbeat with SCCM in the last 30 days. Then, I use that collection to run my script against. Here is the code. Please tell me what you think!!! :slight_smile:

 

Import-Module ActiveDirectory

#Create file for logging TPOS
$logfile = "C:\temp\SCCMTPOSLog.txt"

#Create variables for SCCM
$SCCMServer = "server.company.net"
$sitename = "P60"
$CollectionName = 'ECO - TPOS Older than 30 Days' 

Write-Host "Retrieving register names from SCCM..."

#Retrieve SCCM collection by name 
$Collection = get-wmiobject -ComputerName $siteServer -NameSpace "ROOT\SMS\site_$SiteCode" -Class SMS_Collection | where {$_.Name -eq "$CollectionName"} 
#Retrieve members of collection 
$registerNames = Get-WmiObject -ComputerName $SiteServer -Namespace  "ROOT\SMS\site_$SiteCode" -Query "SELECT * FROM SMS_FullCollectionMembership WHERE CollectionID='$($Collection.CollectionID)' order by name" | select Name
        
Write-Host "Checking SCCM records against AD..."

#Iterate through each of the register names
foreach ($registerName in $registerNames) {

    Write-Host "Processing $registerName"

    #Check if register has a corresponding record in Active Directory
    $tempVar = Get-ADComputer $registerName
            
        if ($tempVar -like "Get-ADComputer : Cannot find an object with identity" ) { 
      
            Write-Host "Computer object NOT FOUND for $registerName"
            Write-Host "Removing SCCM record for $registerName" 

	        # Get the resourceID from SCCM
	        $resID = Get-WmiObject -computername $SCCMServer -query "select resourceID from sms_r_system 	where name like `'$registerName`'" -Namespace "root\sms\site_$sitename"
	        $computerID = $resID.ResourceID
            
            $comp = [wmi]"\\$SCCMServer\root\sms\site_$($sitename):sms_r_system.resourceID=$($resID.ResourceId)"

		    #Delete the computer account
    		$comp.psbase.delete()

            }

        else {
                            
            Write-Host "Computer object $registerName exists"

            #Ping register name to see if it is currently on the network
            if (Test-Connection -ComputerName $registerName -Quiet -count 1) {

                Write-Host "$registerName is online"
                Add-Content $logfile "$registerName"

                }

            else {

                Write-Host "$registerName is offline and will be removed from AD and SCCM"
                Remove-ADComputer -Identity "$registerName"

		        #Delete the computer account
    		    $comp.psbase.delete()

                }

        } #else {

} #foreach ($registerName in $registerNames) {

Send-MailMessage -from "Store EcoSystem " -to "Ryane Helder ", "Jeff Dunlap " -subject "Stale TPOS Records" -body "Please review and distribute to RITM as needed." -Attachment "$logfile" -smtpServer smtp.company.com

You know what they say about what “R&D” means: rip off and duplicate:

https://gallery.technet.microsoft.com/Cleanup-in-AD-deleted-b80e452d

Also, know stale object cleanup is built into SCCM maintenance, you just have to enable it (look in site maintenance for the task). It’s very conservative because object deletion impacts reporting (remember SCCM has no data warehouse so deleting a computer object deletes it’s history).