Trevor Sullivan's Tech Room

Minding the gap between administration and development

PowerShell: AD / SCCM Workstation Cleanup Script Version 3.0

Posted by Trevor Sullivan on 2010/08/17


I just realized, I still haven’t posted the script that removes SCCM resources, alongside Active Directory cleanup. I had written a version 3.0 of a script I previously posted, but never posted it. So, here it is (I haven’t tested it in a while):

Disclaimer: I am not responsible for what you do with this script.

Update (2010-08-18): Shay Levy (PowerShell MVP) has noted that using Replace(“/”, “-“) will not work in all cultures. Rather, he suggests using this format: (Get-Date -f ‘M-d-yyyy hMMss tt’)

##############################################################################
#
# Author: Trevor Sullivan
#
# Date: October 28, 2009
#
# Lessons learned:
#
# 1. ADSI property names are lower case using DirectorySearcher or DirectoryEntry
# 2. Must explicitly cast 64-bit integers from AD
# 3. The Excel API is terrible (already knew that)
#
#
# Change Log:
#    2009-11-06
#        -Added: function to delete objects from SCCM (untested)
#        -Added: User variables at top of script to ease usage
#        -Added: function to auto-detect SCCM site code, based on server name
#        -Added: Windows Vista accounts to search criteria
#        -Fixed: Replaced -bxor operator with -bor to prevent computer accounts
#                from being re-enabled
#        -Fixed: Casted [void] from loading Excel Interop assembly to prevent
#                Assembly object from being written to pipeline
#
##############################################################################


### Populate these variables please. ###
$ExcelLog = $Env:USERPROFILE + '\' + (Get-Date).ToString().Replace("/","-").Replace(":","") + " AD Workstation Cleanup.xlsx" # Full path to save Excel log to
$TargetDn = 'cn=computers,dc=ts,dc=loc' # Top-level distinguishedName 
$DisabledDn    = 'ou=Disabled,ou=Workstations,dc=ts,dc=;pc' # OU to place disabled accounts into
$DisableAge = 60 # Age (in days) of computer account, to be disabled
$DeleteAge = 30 # Age of computer account (<DisabledDate> + X) to delete computer accounts
$SccmServer    = 'sccm01' # The server on which your SMS Provider component is installed
$BreakStuff = $false # If set to $true, the script WILL TAKE ACTION!
$Debug = $false # Enables additional logging to file and stdout
###  ### ############################# ###  ###

function DisableOldAccounts(${TargetDn}, ${DisableAge} = 60)
{
    ${Computers} = GetComputerList ${TargetDn}

    foreach (${Computer} in ${Computers})
    {
        # PwdLastSet is a 64-bit integer that indicates the number of 100-nanosecond intervals since 12:00 AM January 1st, 1601
        # The FromFileTime method converts a 64-bit integer to datetime
        # http://www.rlmueller.net/Integer8Attributes.htm
        ${PwdLastSet} = [DateTime]::FromFileTime([Int64]"$(${Computer}.Properties['pwdlastset'])")
        ${CompAge} = ([DateTime]::Now - $PwdLastSet).Days
        if (${CompAge} -gt ${DisableAge})
        {
            LogMessage "$($Computer.Properties['cn']) age is ${CompAge}. Account will be disabled" 2
            WriteDisabledEntry $Computer.Properties['cn'].Item(0) $CompAge $Computer.Properties['distinguishedname'].Item(0) $DisabledDn
            DisableAccount $Computer.Properties['distinguishedname'].Item(0)
        }
        else
        {
            LogMessage "$($Computer.Properties['cn'].Item(0)) age is ${CompAge}, $($Computer.Properties['pwdlastset'].Item(0)), ${PwdLastSet}" 1
        }
    }
}

# Gets a full list of computer accounts from the target distinguishedName defined at the top of the script
function GetComputerList($TargetDn)
{
    # Define the LDAP search syntax filter to locate workstation objects.
    # See this link for info: http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx
    ${tFilter} = '(&(objectClass=computer)(|(operatingSystem=Windows 2000 Professional)(operatingSystem=Windows XP*)(operatingSystem=*Vista*)(operatingSystem=Windows 7*)))'

    # Create a DirectorySearcher using filter defined above
    ${Searcher} = New-Object System.DirectoryServices.DirectorySearcher $tFilter
    # Set the search root to the distinguishedName specified in the function parameter
    ${Searcher}.SearchRoot = "LDAP://${TargetDn}"
    # Search current container and all subcontainers
    ${Searcher}.SearchScope = [System.DirectoryServices.SearchScope]::Subtree
    # See this link for info on why this next line is necessary: http://www.eggheadcafe.com/software/aspnet/32967284/searchall-in-ad-ldap-f.aspx
    ${Searcher}.PageSize = 1000
    $Results = $Searcher.FindAll()
    LogMessage "Found $($Results.Count) computer accounts to evaluate for disablement" 1
    return $Results
}

# Set description on computer account, disable it, and move it to the Disabled OU
function DisableAccount($dn)
{
#    LogMessage "DisableAccount method called with param: ${dn}" 1
    # Get a reference to the object at <distinguishedName>
    $comp = [adsi]"LDAP://${dn}"
    # Disable the account
#    LogMessage "userAccountControl ($($comp.Name)) is: $($comp.userAccountControl)"
    $comp.userAccountControl = $comp.userAccountControl.Value -bor 2
    # Write the current date to the description field
    if ($comp.Description -ne '') { LogMessage "Description attribute of ($comp.Name) is set to: $($comp.Description)" 2 }
    $comp.Description = "$(([DateTime]::Now).ToShortDateString())"

    # Uncomment these lines to write changes to Active Directory
    if ($BreakStuff)
    {
        [Void] $comp.SetInfo()
        $comp.psbase.MoveTo("LDAP://${DisabledDn}")
    }
}

# Parameter ($DeleteAge): Days from disable date to delete computer account
function DeleteDisabledAccounts($DeleteAge)
{
    # Get reference to OU for disabled workstation accounts
    ${DisabledOu} = [adsi]"LDAP://${DisabledDn}"

    ${Searcher} = New-Object System.DirectoryServices.DirectorySearcher '(objectClass=computer)'
    ${Searcher}.SearchRoot = ${DisabledOu}
    ${Searcher}.SearchScope = [System.DirectoryServices.SearchScope]::Subtree
    # Page size is used to return result count > default size limit on domain controllers.
    # See: http://geekswithblogs.net/mnf/archive/2005/12/20/63581.aspx
    ${Searcher}.PageSize = 1000
    LogMessage "Finding computers to evaluate for deletion in container: ${DisabledDn}" 1
    ${Computers} = ${Searcher}.FindAll()

    foreach (${Computer} in ${Computers})
    {
        ${DisableDate} = [DateTime]::Parse(${Computer}.Properties['description'])
        trap {
            LogMessage "Couldn't parse date for $($Computer.Properties['cn'])" 3
            continue
        }
        ${CurrentAge} = ([DateTime]::Now - ${DisableDate}).Days
        if (${CurrentAge} -gt ${DeleteAge})
        {
            LogMessage "$(${Computer}.Properties['cn']) age is ${CurrentAge} and will be deleted" 2
            WriteDeletedEntry $Computer.Properties['cn'].Item(0) $CurrentAge $Computer.Properties['distinguishedname'].Item(0) "Note"
            if ($BreakStuff)
            {
                $DisabledOu.Delete('computer', 'CN=' + ${Computer}.Properties['cn'])
            }
            RemoveFromSccm ${Computer}.Properties['cn'] $SccmServer
        }
        else
        {
            LogMessage "$(${Computer}.Properties['cn']) age is ${CurrentAge} and will not be deleted" 1
        }
    }
}

# Purpose: This function deletes a resource from the Configuration Manager database
function RemoveFromSccm($tPcName, $tSiteServer)
{
    $tSysQuery = "select * from SMS_R_System where Name = '$tPcName'"
    $tWmiNs = "root\sms\site_" + $Global:SccmSiteCode
    if ($Debug)
    {
        LogMessage "Site code is: $Global:SccmSiteCode" 1
        LogMessage $tSysQuery 1
        LogMessage "tSiteServer is: $tSiteServer" 1
        LogMessage "tWmiNs is: $tWmiNs" 1
    }
    $Resources = Get-WmiObject -ComputerName $tSiteServer -Namespace $tWmiNs -Query $tSysQuery

    if ($Resources -eq $null) { return; }

    foreach ($Resource in $Resources)
    {
        $AgentTime = $($Resource.AgentTime | Sort-Object | Select-Object -Last 1)
        $UserName = $Resource.LastLogonUserDomain + '\' + $Resource.LastLogonUserName
        # Log the deleted SCCM resource to the Excel log
        WriteSccmDeletionEntry $Resource.ResourceID $Resource.Name $AgentTime $UserName
        # This line deletes records from the ConfigMgr database
        if ($BreakStuff)
        {
            # Delete the resource from the ConfigMgr site server
            $resource.Delete()
        }
    }
}

# Purpose: This function looks up the site code for the SMS Provider, given a server name
function GetSiteCode($tSiteServer)
{
    # Dynamically obtain SMS provider location based only on server name
    $tSiteCode = (Get-WmiObject -ComputerName $tSiteServer -Class SMS_ProviderLocation -Namespace root\sms).NamespacePath
    # Return only the last 3 characters of the NamespacePath property, which indicates the site code
    return $tSiteCode.SubString($tSiteCode.Length - 3).ToLower()
}

# This function logs a message to the console and a log file.
# Params:
#    $tMessage = A string representing the message to be logged to the file & console
#    $Severity = A integer from 1-3 representing the severity of the message: Info, Warning, Error
function LogMessage(${tMessage}, ${Severity})
{
    switch(${Severity})
    {
        1 {
            $LogPrefix = "INFO"
            $fgcolor = [ConsoleColor]::Blue
            $bgcolor = [ConsoleColor]::White
        }
        2 {
            $LogPrefix = "WARNING"
            $fgcolor = [ConsoleColor]::Black
            $bgcolor = [ConsoleColor]::Yellow
        }
        3 {
            $LogPrefix = "ERROR"
            $fgcolor = [ConsoleColor]::Yellow
            $bgcolor = [ConsoleColor]::Red
        }
        default {
            $LogPrefix = "DEFAULT"
            $fgcolor = [ConsoleColor]::Black
            $bgcolor = [ConsoleColor]::White
        }
    }

    if ($Debug)
    {
        Add-Content -Path "AD-Workstation-Cleanup.log" -Value "$((Get-Date).ToString()) ${LogPrefix}: ${tMessage}"
        Write-Host -ForegroundColor $fgcolor -BackgroundColor $bgcolor -Object "$((Get-Date).ToString()) ${LogPrefix}: ${tMessage}"
    }
}

function SetupExcel()
{
    LogMessage "Setting up Excel logging" 1
    [void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
    $Global:Excel = New-Object Microsoft.Office.Interop.Excel.ApplicationClass
    $Excel.Visible = $true
    $Global:Workbook = $Excel.Workbooks.Add()

    # Setup worksheet for deleted SCCM resource records
    $Global:SccmResourceLog = $Workbook.Worksheets.Item("Sheet3")
    $SccmResourceLog.Name = "SCCM Resources"
    $SccmResourceLog.Tab.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorAccent3
    $SccmResourceLog.Cells.Item(1, 1).Value2 = "Date"
    $SccmResourceLog.Cells.Item(1, 2).Value2 = "Resource ID"
    $SccmResourceLog.Cells.Item(1, 3).Value2 = "Name"
    $SccmResourceLog.Cells.Item(1, 4).Value2 = "Last Agent Time"
    $SccmResourceLog.Cells.Item(1, 5).Value2 = "Username"
    $Global:tSccmResRow = 2

    # Setup worksheet for disabled accounts
    $Global:DisabledLog = $Workbook.Worksheets.Item("Sheet2")
    $DisabledLog.Tab.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorAccent2
    $DisabledLog.Name = "Disabled"
    $DisabledLog.Cells.Item(1, 1).Value2 = "Date"
    $DisabledLog.Cells.Item(1, 2).Value2 = "Name"
    $DisabledLog.Cells.Item(1, 3).Value2 = "Age"
    $DisabledLog.Cells.Item(1, 4).Value2 = "Source Container"
    $DisabledLog.Cells.Item(1, 5).Value2 = "Destination Container"
    $Global:tDisabledRow = 2

    # Setup worksheet for deleted accounts log
    $Global:DeletedLog = $Workbook.Worksheets.Item("Sheet1")
    $DeletedLog.Tab.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorAccent5
    $DeletedLog.Name = "Deleted"
    $DeletedLog.Cells.Item(1, 1).Value2 = "Date"
    $DeletedLog.Cells.Item(1, 2).Value2 = "Name"
    $DeletedLog.Cells.Item(1, 3).Value2 = "Age"
    $DeletedLog.Cells.Item(1, 4).Value2 = "DN"
    $DeletedLog.Cells.Item(1, 5).Value2 = "Note"
    $Global:tDeletedRow = 2
}

# Writes an entry to the global variable used to reference the log for disabled accounts
function WriteDisabledEntry([string] $tName, $tAge, [string] $tSourceDn, [string] $tDestinationDn)
{
    #LogMessage "Writing disabled computer to Excel log: $tName" 1
    #Write-Host "Value of tname is $tName"
    #Write-Host "Value of tage is $tAge"
    #Write-Host "Value of tsourcedn is $tSourceDn"
    #Write-Host "Value of tDestinationDn is $tDestinationDn"
    $tArrContainer = $tSourceDn.Split(",")
    $tContainer = [string]::Join(",", ($tArrContainer | select -Last ($tArrContainer.Length - 1)))
    $DisabledLog.Cells.Item($tDisabledRow, 1).Value2 = [DateTime]::Now.ToString()
    $DisabledLog.Cells.Item($tDisabledRow, 2).Value2 = $tName
    $DisabledLog.Cells.Item($tDisabledRow, 3).Value2 = $tAge
    $DisabledLog.Cells.Item($tDisabledRow, 4).Value2 = $tContainer
    $DisabledLog.Cells.Item($tDisabledRow, 5).Value2 = $tDestinationDn
    $Global:tDisabledRow++
}

# Writes an entry to the global variable used to reference the log for deleted accounts 
function WriteDeletedEntry($tName, $tAge, $tDN, $tNote)
{
    #LogMessage "Writing deleted computer to Excel log: $tName" 1
    #Write-Host "Value of tName is $tName"
    #Write-Host "Value of tAge is $tAge"
    #Write-Host "Value of tDN is $tDN"
    #Write-Host "Value of tNote is $tNote"
    $DeletedLog.Cells.Item($tDeletedRow,1).Value2 = [DateTime]::Now.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,2).Value2 = $tName.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,3).Value2 = $tAge.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,4).Value2 = $tDN.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,5).Value2 = $tNote.ToString()
    $Global:tDeletedRow++
    return
}

function WriteSccmDeletionEntry($tResourceId, $tName, $tLastAgentTime, $tUserName)
{
    $SccmResourceLog.Cells.Item($tSccmResRow, 1).Value2 = [DateTime]::Now.ToString()
    $SccmResourceLog.Cells.Item($tSccmResRow, 2).Value2 = $tResourceId
    $SccmResourceLog.Cells.Item($tSccmResRow, 3).Value2 = $tName
    $SccmResourceLog.Cells.Item($tSccmResRow, 4).Value2 = $tLastAgentTime
    $SccmResourceLog.Cells.Item($tSccmResRow, 5).Value2 = $tUserName
    $Global:tSccmResRow++
    return
}

function CloseExcel()
{
    # AutoFit the columns

    foreach ($tSheet in $Workbook.Worksheets)
    {
        $tSheet.Activate()
        [Void] $Excel.ActiveCell.CurrentRegion.Columns.AutoFit()
        [Void] $Excel.ActiveCell.CurrentRegion.Select()
        $Global:ListObject = $Excel.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
        $ListObject.Name = "TableData"
        $ListObject.TableStyle = "TableStyleLight9"
    }

    LogMessage "Saving and closing Excel workbook" 1
    $Global:Workbook.SaveAs($ExcelLog)
    $Global:Excel.Quit()
}

function Main()
{
    Clear-Host
    LogMessage "Beginning workstation account cleanup script" 1

    # Retrieve SCCM site code from site server specified by user
    $Global:SccmSiteCode = GetSiteCode $SccmServer

    # Setup Excel logging
    SetupExcel

    # Delete accounts that have been disabled for X days
    DeleteDisabledAccounts $DeleteAge

    # Disable accounts that are older than X days
    DisableOldAccounts $TargetDn $DisableAge

    CloseExcel
    LogMessage "Completed workstation account cleanup script" 1
}

Main
Advertisements

11 Responses to “PowerShell: AD / SCCM Workstation Cleanup Script Version 3.0”

  1. […] Update (2010-08-25): I have posted a newer version of this script. […]

  2. […] previously written several versions of an Active Directory cleanup script, but if you’re not seeking something that complicated, you can simply leverage the cmdlets […]

  3. Steve said

    Could this script be modified to only delete SCCM objects that are DISABLED in AD? (Sorry, I am not a scripting guy)
    We are in the middle of a migration, and our upper management wants accurate numbers, and they don’t want to wait for the PCs to “drop out” of SCCM
    Thanks,
    Steve

    • Trevor Sullivan said

      Steve,

      That’s how the script currently works. It only deletes SCCM objects that have already been disabled in a previous iteration of the script.

      Let me know if I can help further.

      Cheers,
      Trevor

  4. Erik Cramer said

    What are prerecs for this script do i need to install certain things first other then powershell ?
    Because if i run it it ogives me a bunsh of errors that it can not call methods

  5. Dennis said

    I assume what I need would be somewhat easier but I have been trouble finding the powershell commands I need to accomplish it. We have a process where machines are brought into maintenance and the barcode (which is part of the computername) is entered into a database.

    So, I have a list of computer accounts I want to delete in a txt or CSV file. We are positive these are not on the floor, but they may be going out soon, so we want to clean them from AD and SCCM, so waiting for them to expire won’t work and they will have the same name they had before. That makes SCCM cluttered.

    I have no problem with creating a new OU to dump the accounts into and even disabling them and then deleting them out after the script removes them from SCCM.

    Rough process flow.

    1. Get list.
    2. Move computer objects that are on list into new OU.
    3. Script then looks at that OU and anything in there is removed from SCCM.
    4. Script then removes objects from OU (optional, if they are in there they are easy enough to manually remove.)

    Your script does #3 nicely, but has more logic than I need. I can either set the age to 1 day (would zero work?) or remove the logic to just pluck anything from there out of SCCM.

    Where I really need help is #2. It’s probably something simple, but I haven’t had any luck. Any suggestions would be appreciated.

    Regards,

    Dennis

    • Trevor Sullivan said

      Hi Dennis,

      The code you’re after is inside the DisableAccount() function. Check out this line, where you basically have to take a computer object in a variable, and “Move” the object to a new distinguished name (which includes the OU path).

      $comp.psbase.MoveTo(“LDAP://${DisabledDn}”)

      Cheers,
      Trevor

  6. Terry Duckett said

    Is there a way to make the script, when it scans for disabled computers, to not scan the $DisabledDN OU? If the DisabledDN is under the TargetDN, then the computers in the DisabledDN are getting scanned everytime which causes the date in the description field to change.

  7. […] […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: