Powershell - Export Hash of Array to CSV

Question

question

otip-1132 asked Dec 6, '21 RichMatheisen-8856 commented Dec 9, '21

Powershell - Export Hash of Array to CSV

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.

windows-server-powershell

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-12-06T15:49:33.35Z

RichMatheisen-8856 answered Dec 6, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-12-06T16:01:59.59Z

otip-1132 answered Dec 6, '21 RichMatheisen-8856 commented Dec 6, '21

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 · Dec 06, 2021 at 08:39 PM

That's correct. I just chose to add the "ID" as a value in the hash AND as the key that identifies the value. It just made coding shorter (or simpler -- or both).

BTW, if the order in which the hash values (the ones in the hash stored as the value) are stored is important, just add "[ordered]" in front of the has declaration.

0 ·

Answer 3 · 2021-12-07T10:28:09.747Z

otip-1132 answered Dec 7, '21 otip-1132 published Dec 7, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2021-12-07T19:39:05.737Z

RichMatheisen-8856 answered Dec 7, '21 RichMatheisen-8856 commented Dec 8, '21

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()).

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

otip-1132 · Dec 08, 2021 at 06:09 PM

There are two simple answers
1 - I had the need to "erase" to have only one line with key being the guid of the gpo even if it answers true to all checks. Linked, disabled link, settings but disabled. I only want to identify the guid has "can be removed" so this is why I used hash and not array otherwise I could have had 4 lines for the same guid.
2 - I never really understood what is a custompsobjects
3 - I could want to target a specific guid later. Although indeed I don't do it.
Thanks again.

0 ·

RichMatheisen-8856 otip-1132 · Dec 08, 2021 at 07:48 PM

A PSObject or PSCustomObject is a Powershell defined object with user defined property names and values. It's a convenient way to carry around a set of values. In essence, it's no different to any other object you're using. For example, when you use Get-ChildItem the value returned is a series of Microsoft-defined objects

A good example of a common use fro a PSObject (or PSCustomObject) is when there's a mismatch between the name of an object's property anf the property name expected by cmdlet on the other end of a pipe. Let's say that the cmdlet that creates the object uses a property name "Name" but the receiving cmdlet expects a property called "Identity". If you try "CmdLet1 | Cmdlet2" you get an error from CmdLet2 saying it couldn't find a value of "Identity". You can adjust this by doing "CmdLet1 | Select @{n=Identity;e={$<underbar>.Name}} | CmdLet2". That "Select" produces a PSObject with a property "Identity" and its value from the "Name" property of the original object. Now CmdLet2 can find a property named "Identity".

There are many uses for the object types PSObject and PSCustomObject.

1 ·

Answer 5 · 2021-12-08T17:54:49.797Z

LimitlessTechnology-2700 answered Dec 8, '21 otip-1132 edited Dec 9, '21

Hello @otip-1132

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--

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

otip-1132 · Dec 08, 2021 at 06:07 PM

I could do that by I don't really need to go that far. I think. Only one reason for removal is enough. => I changed my mind, I'll try to do it. :)

0 ·

Answer 6 · 2021-12-09T10:50:37.42Z

otip-1132 answered Dec 9, '21 RichMatheisen-8856 commented Dec 9, '21

Thank you for the explanation. I'm still very beginner I think.

I have one last trouble :

Exception calling "Add" with "2" argument(s): "Item has already been added. Key in dictionary: '0acc6d38-492a-4d34-a07a-f97dbd73e361' Key being added:
'0acc6d38-492a-4d34-a07a-f97dbd73e361'"
At XXXX\GPO-List_Unused_gposV2.ps1:112 char:13

it relies to this piece of code when the the GPO was already identified in the previous IF (Let say the GPO is not linked AND all settings are disabled).
Can I have .add replace the existing record with new one for the same key. Not sure I'm clear sorry :/

  $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"
                  }
      )
  }

Maybe I could move that section to the beginning of the foreach GPO , after

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

If I identify that all settings are disable, I do a continue to get out of the foreach ?

Other than that it does what I want so far.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 · Dec 09, 2021 at 07:52 PM

If you don't care to know if the key exists in the hash and just want to add a new Key/Value to the hash or replace the Value of an existing Key, try changing:

 $CandidateGPOs.add($var.id,
    
 To this:
    
 $CandidateGPOs[$var.id] =

The Perl language uses the term "autovivification". The most simple form of it is when you have a hash and you set a value of a key that did not exist before.

0 ·

question