PowerShell script to query SCCM "stale" records?

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Justin King Justin King 8 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #34233
    Profile photo of Ryane Helder
    Ryane Helder
    Participant

    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.

    #34234
    Profile photo of Ryane Helder
    Ryane Helder
    Participant

    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

    #34274
    Profile photo of Ryane Helder
    Ryane Helder
    Participant

    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!!! 🙂

     
    
    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
    
    #34285
    Profile photo of Justin King
    Justin King
    Participant

    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).

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.