Cruzar un archivo de actividad contra el maestro principal para encontrar IDs que tienen movimiento pero no están registrados.
Compare an activity file against the main master file to find IDs that have movement but are not registered.
Este ejercicio es solo para aprendizaje y pruebas. No ejecutes scripts contra datos reales, ambientes de producción, carpetas compartidas, sistemas críticos o repositorios empresariales sin autorización, respaldos, pruebas previas, permisos mínimos, control de cambios y cumplimiento de los protocolos de ciberseguridad de tu organización.
This exercise is for learning and testing only. Do not run scripts against real data, production environments, shared folders, critical systems, or enterprise repositories without authorization, backups, prior testing, least-privilege permissions, change control, and compliance with your organization's cybersecurity protocols.
Detectar registros que aparecen en un archivo transaccional o de actividad, pero cuyo ID no existe en el archivo maestro. Este patrón ayuda a encontrar errores de integración, actividades asignadas a IDs inválidos o registros que quedaron fuera del maestro principal.
Detect records that appear in a transactional or activity file, but whose ID does not exist in the master file. This pattern helps find integration errors, activity assigned to invalid IDs, or records missing from the main master table.
| ID | Nombre | Status |
|---|---|---|
| 1001 | Ana Perez | Active |
| 1002 | John Smith | Active |
| 1003 | Maria Lopez | Inactive |
| 1004 | Robert Diaz | Active |
| ID | Actividad | Fecha |
|---|---|---|
| 1001 | Login | 2026-01-05 |
| 1002 | Update | 2026-01-05 |
| 9999 | Transaction | 2026-01-06 |
| 8888 | Login | 2026-01-07 |
$maestro = Import-Csv "C:\Data\Maestro.csv" | Select-Object -ExpandProperty ID
Import-Csv "C:\Data\Actividad.csv" |
Where-Object { $maestro -notcontains $_.ID }| ID | Actividad | Fecha | TipoHallazgo |
|---|---|---|---|
| 9999 | Transaction | 2026-01-06 | Orphan Record |
| 8888 | Login | 2026-01-07 | Orphan Record |
# ==========================================================
# PowerShell Advanced Topic 17
# Identify Orphan Records
# ==========================================================
$masterFile = "C:\Data\Maestro.csv"
$activityFile = "C:\Data\Actividad.csv"
$outputFile = "C:\Data\Orphan_Records_Report.csv"
# Import master and activity data
$maestro = Import-Csv $masterFile
$actividad = Import-Csv $activityFile
# Create a fast lookup list using the master IDs
$masterIDs = $maestro | Select-Object -ExpandProperty ID
# Identify activity records whose ID is not in the master file
$orphanRecords = $actividad |
Where-Object { $masterIDs -notcontains $_.ID } |
Select-Object *,
@{Name="TipoHallazgo"; Expression={"Orphan Record"}},
@{Name="FechaProceso"; Expression={Get-Date -Format "yyyy-MM-dd HH:mm:ss"}},
@{Name="ProcesadoPor"; Expression={$env:USERNAME}}
# Export report
$orphanRecords | Export-Csv $outputFile -NoTypeInformation -Encoding UTF8
# Show results
$orphanRecords | Format-Table -AutoSize
Write-Host "Orphan record validation completed."
Write-Host "Total orphan records found: $($orphanRecords.Count)"
Write-Host "Output file: $outputFile"Para archivos grandes, es mejor usar un HashSet. Esto evita búsquedas lentas cuando el archivo de actividad tiene miles o millones de registros.
For large files, it is better to use a HashSet. This avoids slow lookups when the activity file has thousands or millions of records.
# ==========================================================
# Optimized orphan record detection using HashSet
# ==========================================================
$masterFile = "C:\Data\Maestro.csv"
$activityFile = "C:\Data\Actividad.csv"
$outputFile = "C:\Data\Orphan_Records_Report_Optimized.csv"
$maestro = Import-Csv $masterFile
$actividad = Import-Csv $activityFile
# Build HashSet for fast lookup
$masterIDs = [System.Collections.Generic.HashSet[string]]::new()
foreach ($row in $maestro) {
[void]$masterIDs.Add($row.ID)
}
# Find orphan records
$orphanRecords = foreach ($row in $actividad) {
if (-not $masterIDs.Contains($row.ID)) {
$row | Add-Member -NotePropertyName "TipoHallazgo" -NotePropertyValue "Orphan Record" -Force
$row | Add-Member -NotePropertyName "FechaProceso" -NotePropertyValue (Get-Date -Format "yyyy-MM-dd HH:mm:ss") -Force
$row | Add-Member -NotePropertyName "ProcesadoPor" -NotePropertyValue $env:USERNAME -Force
$row
}
}
$orphanRecords | Export-Csv $outputFile -NoTypeInformation -Encoding UTF8
$orphanRecords | Format-Table -AutoSize
Write-Host "Optimized orphan record validation completed."
Write-Host "Total orphan records found: $($orphanRecords.Count)"
Write-Host "Output file: $outputFile"Este patrón es esencial en procesos de reconciliación. Un registro huérfano puede indicar un error de captura, una integración incompleta, un maestro desactualizado o actividad asociada a un ID que no debería existir. Detectarlo temprano evita reportes incorrectos y decisiones basadas en datos inconsistentes.
This pattern is essential in reconciliation processes. An orphan record may indicate a data entry error, incomplete integration, outdated master data, or activity tied to an ID that should not exist. Detecting it early prevents incorrect reports and decisions based on inconsistent data.