Created
February 10, 2026 17:10
-
-
Save Torxsmind/1a686e3c8070b5fd56aa86b6a3857854 to your computer and use it in GitHub Desktop.
Get Stale AD users
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .SYNOPSIS | |
| Exports AD user last-logon data (per-DC) to Excel for stale-account analysis. | |
| .DESCRIPTION | |
| Queries all domain controllers for each user’s LastLogon (non-replicated) and LastLogonTimestamp (replicated), then computes | |
| MostRecentLogon + DaysSinceLastLogin and exports results to a timestamped Excel workbook. | |
| .PREREQUISITES | |
| - RSAT ActiveDirectory module available (Get-ADUser / Get-ADDomainController). | |
| - ImportExcel module installed (Export-Excel). | |
| - Account can read AD user attributes and query each DC. | |
| - Output folder exists and is writable (default: C:\temp). | |
| .OUTPUT | |
| C:\temp\ad_stale_Users_v01_<timestamp>.xlsx (worksheet: MasterSheet, table: UserData). | |
| #> | |
| #pre-requsites | |
| # Ensure ImportExcel module is available Import-Module ImportExcel | |
| # Ensure the folder for the Excel file exists | |
| # Setup | |
| $timestamp = Get-Date -Format "yyyy-MM-dd_HHmmss" | |
| $excelPath = "C:\temp\ad_stale_Users_v01_$timestamp.xlsx" | |
| $sheetName = "MasterSheet" | |
| $tableName = "UserData" | |
| $testing = $True | |
| $testqty = "15" | |
| $ADDOB = Get-Date "1999-04-09 22:50:00" | |
| function Convert-ADTimestamp { | |
| param ( | |
| [long]$timestamp, | |
| [datetime]$fallbackDate | |
| ) | |
| if (-not $timestamp -or $timestamp -eq 0) { return $fallbackDate } | |
| try { | |
| $converted = [DateTime]::FromFileTime($timestamp) | |
| if ($converted -lt (Get-Date "1901-01-01")) { return $fallbackDate } | |
| return $converted | |
| } catch { return $fallbackDate } | |
| } | |
| # Get user objects from Active Directory | |
| if ($testing) { | |
| Write-Host "Testing mode enabled: retrieving $testqty random user objects..." | |
| $allUsers = Get-ADUser -Filter * -Properties SAMAccountName, DisplayName, Description, Enabled, userPrincipalName, CanonicalName, whenCreated, lastLogonTimestamp, admincount | | |
| Get-Random -Count $testqty | |
| } else { | |
| Write-Host "Production mode: retrieving all user objects..." | |
| $allUsers = Get-ADUser -Filter * -Properties SAMAccountName, DisplayName, Description, Enabled, userPrincipalName, CanonicalName, whenCreated, lastLogonTimestamp, admincount | |
| } | |
| $userTable = @{} | |
| foreach ($user in $allUsers) { $userTable[$user.SamAccountName] = $user } | |
| $domainControllers = Get-ADDomainController -Filter * | |
| # Alternative command incase get-ADComputer does not work | |
| # $domainControllers = Get-ADComputer -Filter * -SearchBase "OU=Domain Controllers, DC=domain, DC=net" | |
| foreach ($dc in $domainControllers) { | |
| $dcName = $dc.Name | |
| Write-Host "Querying DC: $dcName" | |
| foreach ($sam in $userTable.Keys) { | |
| try { | |
| $user = Get-ADUser -Identity $sam -Server $dcName -Properties LastLogon, LastLogonTimestamp | |
| $lastLogon = Convert-ADTimestamp -timestamp $user.LastLogon -fallbackDate $ADDOB | |
| $lastLogonTimestamp = Convert-ADTimestamp -timestamp $user.LastLogonTimestamp -fallbackDate $ADDOB | |
| $userTable[$sam] | Add-Member -MemberType NoteProperty -Name "${dcName}_LastLogon" -Value $lastLogon -Force | |
| $userTable[$sam] | Add-Member -MemberType NoteProperty -Name "${dcName}_LastLogonTimestamp" -Value $lastLogonTimestamp -Force | |
| } catch { | |
| Write-Warning "Failed to retrieve LastLogon values for $sam from ${dcName}: $_" | |
| } | |
| } | |
| } | |
| $excelData = foreach ($user in $userTable.Values) { | |
| $row = [ordered]@{ | |
| SamAccountName = $user.SamAccountName | |
| DisplayName = $user.DisplayName | |
| Enabled = $user.Enabled | |
| MostRecentLogon = $null | |
| DaysSinceLastLogin = $null | |
| DaysSinceGroup = $null | |
| Admincount = $user.admincount | |
| CanonicalName = $user.CanonicalName | |
| Description = $user.Description | |
| userPrincipalName = $user.userPrincipalName | |
| distinguishedName = $user.distinguishedName | |
| whenCreated = $user.whenCreated | |
| } | |
| $mostRecentLogon = $null | |
| foreach ($dc in $domainControllers) { | |
| $dcName = $dc.Name | |
| foreach ($prop in @("${dcName}_LastLogon", "${dcName}_LastLogonTimestamp")) { | |
| $logonTime = if ($user.PSObject.Properties[$prop]) { $user.$prop } else { $null } | |
| if (-not $logonTime -or $logonTime -lt (Get-Date "1901-01-01")) { $logonTime = $ADDOB } | |
| if (-not $mostRecentLogon -or $logonTime -gt $mostRecentLogon) { $mostRecentLogon = $logonTime } | |
| $row[$prop] = $logonTime | |
| } | |
| } | |
| if (-not $mostRecentLogon -or $mostRecentLogon -lt (Get-Date "1901-01-01")) { $mostRecentLogon = $ADDOB } | |
| $row["MostRecentLogon"] = $mostRecentLogon | |
| $row["DaysSinceLastLogin"] = [math]::Ceiling(((Get-Date) - $mostRecentLogon).TotalDays) | |
| $days = $row["DaysSinceLastLogin"] | |
| $row["DaysSinceGroup"] = switch ($days) { | |
| { $_ -le 90 } { "0000-0090 Days"; break } | |
| { $_ -le 180 } { "0091-0180 Days"; break } | |
| { $_ -le 365 } { "0181-0365 Days"; break } | |
| { $_ -le 730 } { "0366-0730 Days (1-2 Y)"; break } | |
| { $_ -le 1095 } { "0731-1095 Days (2-3 Y)"; break } | |
| default { "1096+ Days (3-∞ Y)" } | |
| } | |
| [PSCustomObject]$row | |
| } | |
| # Export to Excel | |
| $excelData | Export-Excel -Path $excelPath -WorksheetName $sheetName -AutoSize -TableName $tableName ` | |
| -TableStyle Medium5 | |
| Write-Host "Export complete: $excelPath" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment