I recently did an article about extracting GPO results from the ADDS event log which you can find here when that script is run from your Domain controller you end up with a CSV output like this:
"TimeCreated","DC","SubjectUserName","GUID","DisplayName","VersionNumber"
"21/10/2024 09:20:58","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","1048580"
"21/10/2024 09:20:48","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","983044"
"21/10/2024 09:20:48","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","917508"
"21/10/2024 09:20:48","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","851972"
"21/10/2024 08:54:06","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","786436"
"21/10/2024 08:50:35","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","720900"
"21/10/2024 08:47:05","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","720899"
However if you notice that if you include the time you will notice that there are a block of entries that are all on the same time to the second, which from a user point of view is impossible, you cannot edit a GPO 3x in the same second, this will cause questions that either the person asking them will not understand or you get a response of the script is wrong as that is impossible - but here you can see the eventlog does show multiple entries:
"21/10/2024 09:20:48","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","983044"
"21/10/2024 09:20:48","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","917508"
"21/10/2024 09:20:48","timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy","851972"
This is down to when you edit a GPO depending on your operation you get more than a simple event ID written to the event log for the type of operation, so the date and time is not that important here, those events will be in the Domain controller if you wish to spool though and take a deeper dive.
We need to normalise this data as the goal is to be alerted to when a group policy has been changed and as this report runs daily the "date and time" is not that important, only the fact that a modification has been made, so for that I have wrote a script that can be "added" to the end of the original script to preserve that data if you wish to perform a deeper dive.
This script will remove fields we do not require and keep the report concise and factual as to the GPO objects modified, the output will then look like this:
"DC","SubjectUserName","GUID","DisplayName"
"timothy.bear.local","BEAR\test.person","D170142C-AD3F-471E-88F8-BD89DE390DAA","Default Domain Policy"
That is far easier to visually process and a can give you a quick glance of what has changed, if you are looking to use the script that can be customised to any data type in CSV then you will find this below:
Script : GPO_Normalise.ps1
# GPO Changes Processing Script
$ErrorActionPreference = 'Stop'
function Process-GPOChanges {
Write-Host "`n=== GPO Changes Processing Script ===" -ForegroundColor Cyan
Write-Host "Started at: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')`n"
try {
# Read the CSV file
Write-Host "Reading GPOChanges.csv..." -ForegroundColor Yellow
$csv = Import-Csv -Path "GPOChanges.csv"
$initialRows = $csv.Count
Write-Host "✓ Successfully loaded file with $initialRows rows" -ForegroundColor Green
# Backup Original File
Write-Host "`nBackup Original File as PreNormalise.csv " -ForegroundColor Yellow
copy GPOChanges.csv PreNormalise.csv
# Show initial columns
Write-Host "`nInitial columns:" -ForegroundColor Yellow
$initialColumns = ($csv | Get-Member -MemberType NoteProperty).Name
$initialColumns | ForEach-Object { Write-Host " - $_" }
# Remove TimeCreated and VersionNumber columns
Write-Host "`nRemoving TimeCreated and VersionNumber columns..." -ForegroundColor Yellow
$processedData = $csv | Select-Object -Property * -ExcludeProperty TimeCreated, VersionNumber
Write-Host "✓ Columns removed successfully" -ForegroundColor Green
# Show remaining columns
Write-Host "`nRemaining columns:" -ForegroundColor Yellow
$remainingColumns = ($processedData | Get-Member -MemberType NoteProperty).Name
$remainingColumns | ForEach-Object { Write-Host " - $_" }
# Remove duplicates
Write-Host "`nRemoving duplicate entries..." -ForegroundColor Yellow
$uniqueData = $processedData | Sort-Object -Property * -Unique
$finalCount = $uniqueData.Count $duplicatesRemoved = $initialRows - $finalCount
Write-Host "✓ Removed $duplicatesRemoved duplicate entries" -ForegroundColor Green
# Save the processed data
Write-Host "`nSaving processed data..." -ForegroundColor Yellow
$uniqueData | Export-Csv -Path "GPOChanges.csv" -NoTypeInformation
# Print summary
Write-Host "`n=== Processing Summary ===" -ForegroundColor Cyan
Write-Host "Initial row count: $initialRows"
Write-Host "Duplicates removed: $duplicatesRemoved"
Write-Host "Final row count: $finalCount"
Write-Host "Columns removed: TimeCreated, VersionNumber"
Write-Host "Processing completed at: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"
Write-Host "`nFile has been successfully updated!" -ForegroundColor Green
}
catch [System.IO.FileNotFoundException] {
Write-Host "`n❌ Error: GPOChanges.csv file not found!" -ForegroundColor Red
Write-Host "Please ensure the file exists in the current directory." -ForegroundColor Red
}
catch {
Write-Host "`n❌ An error occurred: $($_.Exception.Message)" -ForegroundColor Red
Write-Host "Please check the file format and permissions." -ForegroundColor Red
}
}
# Run the function
Process-GPOChanges