Powershell - Export Hash of Array to CSV

Question

Hello,

I am creating various "hash of array" that I then want to export to CSV.

I just show what seems relevant to my issue.

    #...
    #feeding the variable in a foreach (get-gpo with some criteria)
    $GPO_allsettingsdisabledHASH.add($var.id,@($var.displayname,$var.CreationTime, $var.ModificationTime, $var.Owner))
    #...

    #exporting the values to my CSV
            $GPO_allsettingsdisabledHASH.GetEnumerator() | Sort value | format-table -AutoSize | Out-File "$ReportPath\_UnUsedGPOs_$domain.txt" -Append
            $GPO_allsettingsdisabledHASH.GetEnumerator() | Select Name, @{N='Domain';E={$Domain}}, @{N='Reason';E={"All Settings Disabled"}}, @{N='GUID';E={$_.Name}}, @{N='GPO';E={$_.Value}}, @{N='Remove';E={"YES"}}  | Sort -property value | Export-Csv -NoTypeInformation -Path "$ReportPath\_UnUsedGPOs_Listing.csv" -Append

The problem there is that all my array goes to only one column.

The closest I could do was using this line below and then tell excel to use comma separator WITHOUT text delimiter (")
It works but it's kind of tricking excel and my columns then don't have any name. I feel there should be a batter way of writing this but I don't know how.

$GPO_allsettingsdisabledHASH.GetEnumerator() | Select Name, @{N='Computers';E={$_.Value -join ", "}} | Export-Csv -NoTypeInformation -Path "$ReportPath\myfile.csv" -Append

Thank you for your help.

Answer 1

I haven't run this code, but it's probably a better way to deal with this (certainly better than writing the output of the Format-Table to your file!). This builds a hash-of-hashes, allowing you to name the properties:

# Build the hash
$GPO_allsettingsdisabledHASH.add($var.id,
        @{
            ID = $var.id
            DisplayName = $var.displayname
            CreationTime = $var.CreationTime
            ModificationTime = $var.ModificationTime
            Owner = $var.Owner
        }
    )
# Export the hash values
$GPO_allsettingsdisabledHASH.GetEnumerator() | 
    Sort-Object $_.Key |
        ForEach-Object{
            [PSCustomObject]$_.Value
        } |
            Export-Csv "$ReportPath\_UnUsedGPOs_$domain.txt" -NoTypeInformation

Answer 2

Gosh.
That looks so simple. I so much love hashes compared to Arrays ...

However I'm not sure I can read that really properly.
My understanding that a hash is an association between a key and a value (that could be an other hash, array etc).
But here :
@{
ID = $var.id
DisplayName = $var.displayname
CreationTime = $var.CreationTime
ModificationTime = $var.ModificationTime
Owner = $var.Owner
}

It seems to suggest that there is a key ($var.id) and multiple values associated. I that correct?

Thank you

Answer 3

Thank you for this. I have improved my script and it works so far.
I still feel there are many improvements to be done.

I copy you my whole script so you can tell me what you think about it.
Mainly I need to review the calls in my functions since I changed the structure of my hash variable $candidateGPOs, as well as my removal foreach line 142.
I also feel like my "All settings disabled" section starting line 105 could be done in a one liner but I don't find how.
I know I could have $domains dynamic but I prefer to have it static. I don't want my script to run on domains that would be created later on.
Now I'm also a bit lost on line 123 (sorting) and 129 (counting) since I never used hash of hashes.

Basically all these questions rely on how to manipulate has of hashes I suppose.

        $Timeduration = [System.Diagnostics.Stopwatch]::StartNew()
        Import-Module GroupPolicy

        #region Preparing ###
        $Date = Get-Date -Format "dd_MM_yyyy HH-mm"
        $ReportPath = "C:\temps\GPO_Reports\$Date"
        #Check backpup folder existance
        if (-Not(Test-Path -Path $ReportPath)) {New-Item -ItemType Directory $ReportPath -Force}
        $domains = @{
            'contoso.com' = Get-ADDomainController -Discover -DomainName contoso.com
        #    other child domains
        }
        #endregion

        #function list. Usage : list "Standard" "message")
        function list($type, $chaine)
        {
            switch ($type)
            {
                "title"    { write-host "----- $chaine -----" -ForegroundColor Blue -BackgroundColor white } # title message
                "success"  { write-host "SUCCESS: $chaine" -ForegroundColor Green } # succes message
                "warning"  { write-host "WARNING: $chaine" -ForegroundColor Yellow -BackgroundColor black} # warning message
                "standard" { write-host $chaine } # standard message 
                default    { write-host $chaine }
            }
            $type.ToUpper() + " --- " + $chaine | ft | out-file "$ReportPath\_UnUsedGPOs_$domain.txt" -append
        }

        #####################
        ### Start Working ###
        #####################
        foreach ($key in $domains.keys){
        #region Init Variables
            $domain = $key
            $server = $domains.$key
            $CandidateGPOs = $null
            $CandidateGPOs = @{}
        #endregion

        #region Analysis
            list "title" "Domain is $($domain)"
            $GPOlist = Get-GPO -All -Domain $Domain -server $server | Sort-Object displayname 

            ForEach ($policy in $GPOlist) {
                write-host "Working on GPO : " $policy.displayname -backgroundcolor blue -foregroundcolor white

                #Get GPO report in XML format
                [xml]$GPOreportXML = Get-GPOReport -Domain $Domain -server $server -guid $policy.id -ReportType XML

                # Test whether the GPO is linked
                If (-not ($GPOreportXML.GPO.LinksTo)) {
                    $CandidateGPOs.Add($policy.id, 
                         @{
                         ID = $policy.id
                         DisplayName = $policy.displayname
                         CreationTime = $policy.CreationTime
                         ModificationTime = $policy.ModificationTime
                         Owner = $policy.Owner
                         Domain = $domain
                         Reason = "Not Linked"
                        }
                    )
                    write-host  $policy.displayname "not linked" -foregroundcolor red
                }else{
                    # the GPO is linked
                    # Test whether the GPO's links are enabled
                    if (-not (($GPOreportXML.GPO.LinksTo | Foreach {$_.Enabled}) -eq $true) )
                    {
                        $CandidateGPOs.Add($policy.id, 
                         @{
                         ID = $policy.id
                         DisplayName = $policy.displayname
                         CreationTime = $policy.CreationTime
                         ModificationTime = $policy.ModificationTime
                         Owner = $policy.Owner
                         Domain = $domain
                         Reason = "Link Disabled"
                        }
                    )
                        write-host  $policy.displayname "links all disabled" -foregroundcolor red
                    } 
                    elseif (!$GPOreportXML.gpo.Computer.ExtensionData -and !$GPOReportXML.GPO.User.ExtensionData)
                    {
                        # test if the GPO's has no settings
                        $CandidateGPOs.Add($policy.id, 
                         @{
                         ID = $policy.id
                         DisplayName = $policy.displayname
                         CreationTime = $policy.CreationTime
                         ModificationTime = $policy.ModificationTime
                         Owner = $policy.Owner
                         Domain = $domain
                         Reason = "No Setings"
                        }
                    )
                        write-host  $policy.displayname "has no settings" -foregroundcolor red
                    }else{
                        Write-Host $policy.displayname "is ok No change." -foregroundcolor green
                    }
                }#end of IF tests
            }#end foreach gpo
        #endregion

        #region Create Final List
                #Get GPOs with all settings disabled in an ARRAY
                $GPO_allsettingsdisabled =  $GPOlist | where {$_.gpostatus -eq "allsettingsdisabled"} | Select-Object id, DisplayName, CreationTime, ModificationTime, Owner

                #Send result to global hash
                Foreach($var in $GPO_allsettingsdisabled){
                    $CandidateGPOs.add($var.id, 
                         @{
                         ID = $var.id
                         DisplayName = $var.displayname
                         CreationTime = $var.CreationTime
                         ModificationTime = $var.ModificationTime
                         Owner = $var.Owner
                         Domain = $domain
                         Reason = "All Settings Disabled"
                        }
                    )
                }
                $percentage =  ($($CandidateGPOs.count)/$($GPOlist.count)).tostring("P")
                #need to sort per domain and then per reason

        #endregion

        #region Build report
                list "warning" "Total unique GPOs to clean-up for $domain : $($CandidateGPOs.count) out of $($GPOlist.count) total. $percentage"
                list "warning" "Unlinked GPOs : XX" #find a way to group by reason = "Not linked" and count
                list "warning" "Linked but disabled GPOs : XX"
                list "warning" "Empty GPOs : XX"
                list "warning" "GPOs with All Settings Disabled : XX"
                list "standard" "Detail :" 


                $CandidateGPOs.GetEnumerator() | Sort-Object $_.Key | ForEach-Object{[PSCustomObject]$_.Value} | Export-Csv "$ReportPath\_UnUsedGPOs_Listing.csv" -NoTypeInformation

                Write-host " `r "
        #endregion

        #region Remediation AKA !! REMOVE !!
                foreach ($GPO in $CandidateGPOs)
                {
                #BAckup of the GPOs
                #Gpobackup $GPO.value $ReportPath $domain

                # This is the real thing #
                #$_.Displayname | remove-gpo
                }
        #endregion

        }#end foreach domain

        invoke-item "$ReportPath"

        #Show script processing time
        $Timedelapsed = $Timeduration.Elapsed.ToString('dd\.hh\:mm\:ss')
        $Timeduration.Stop()
        $Timedelapsed

Answer 4

The section of code beginning at line 105 could be rewritten like this (I don't see a need for $GPO_allsettingsdisabled):

#region Create Final List
#Get GPOs with all settings disabled and
#Send result to global hash

$GPOlist | 
    Where-Object {$_.gpostatus -eq "allsettingsdisabled"} |
        ForEach-Object{
            $CandidateGPOs.add($var.id, 
                @{
                    ID                  = $_.id
                    DisplayName         = $_.displayname
                    CreationTime        = $_.CreationTime
                    ModificationTime    = $_.ModificationTime
                    Owner               = $_.Owner
                    Domain              = $domain
                    Reason              = "All Settings Disabled"
                }
    )
}

Looking at the larger script, I'm not sure why you've chosen to use a hash to hold the data. You could probably use arrays PSCustomObjects (but I haven't looked in great detail at how you're using hashes and I don't see where you delete anything, add to any existing key in a hash, or access the values in the hashes using anything except GetEnumerator()).

Answer 5

Hello @otip

Hash tables only have pairs of values, a key and a corresponding item. I see two solutions. One is to append values in the item. If the key does not exist, add it with the item value. If the key does exist, make the item equal to the old value, a delimiter, and the new value.

The second solution is to make the item value an array. If the key does not exist, add the key and make the item an array with one value (the new item). If the key does exist, make the item equal to an array with all of the old item values, plus the new item (so the upper bound of the array is one greater).

Hope this helps with your query,

-----------

--If the reply is helpful, please Upvote and Accept as answer--