Powershell : Normalising GPO data

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

Previous Post Next Post

نموذج الاتصال