Export to CSV using Stream Writer

user_1782980 0
Viewed 1526

The code below exports all the permissions set on the directories of a file server. As there are more than 1,000 permission on the file server my code is searching, I am using StreamWriter to speed up the export/writing of these permissions to a CSV. The code below runs accordingly and gets all permission however, the problem is that the results are not being written to a CSV as intended using StreamWriter. Any idea why this is?

$current_date = Get-Date -UFormat "%Y%m%d"
$directory_to_search = dir -Path "C:\Temp\test_folder\*\*\*" -Force |
                       where {$_.Attributes -match'Directory'}
$file_to_stream_results = New-Object System.IO.StreamWriter "C:\Temp\test_folder\server_permissions_$current_date.csv"
$count_of_directories = $directory_to_search.length.ToString()

$Report = @()

for ($i=0; $i -lt $directory_to_search.Length; $i++) {
    $acl = Get-Acl -Path $directory_to_search[$i].FullName
    for ($j=0; $j -lt $acl.Access.Count; $j++) {
        if (!($acl.Access[$j].IdentityReference -eq "BUILTIN\Administrators") -and !($acl.Access[$j].IdentityReference -eq "NT AUTHORITY\SYSTEM")) {
            $Report += New-Object PsObject -Property @{
                'FolderName'       = $directory_to_search[$i].FullName
                'AD Group or User' = $acl.Access[$j].IdentityReference
                'Permissions'      = $acl.Access[$j].FileSystemRights
            }
        }
    }
}

$Report | Export-Csv -Path $file_to_stream_results -Encoding "utf8" -NoTypeInformation

$file_to_stream_results.Close();

It's worth adding that I have worked the below functioning example for Stream Writer into my code above. I have tried to use $file.Writeline("$i" + ",") but it didn't work in my code.

$directory = "C:\Temp\test_folder"

$file = New-Object System.IO.StreamWriter "$directory\1000_values_to_file.csv"

Write-Output $file

$file_length = 1000
for ($i=0; $i -lt $file_length; $i++) {
    Write-Output $i
    $file.Writeline("$i" + "," )
}
$file.Close();
powershell
2 Answers

First and foremost, your code will become more readable if you replace the for loops with foreach loops.

With that said, Export-Csv does not work with StreamWriters. Use either one or the other. If you want to use a StreamWriter you must build your output lines yourself.

$file = New-Object IO.StreamWriter "C:\Temp\test_folder\server_permissions_$current_date.csv"
$file.WriteLine('FolderName,AD Group or User,Permission')
foreach ($dir in $directory_to_search) {
    $acl = Get-Acl -Path $dir.FullName
    foreach ($ace in $acl.Access) {
        if (!($ace.IdentityReference -eq "BUILTIN\Administrators") -and !($ace.IdentityReference -eq "NT AUTHORITY\SYSTEM")) {
            $file.WriteLine(('{0},{1},{2}' -f $dir.FullName, $ace.IdentityReference, $ace.FileSystemRights))
        }
    }
}
$file.Close()

If you want to use Export-Csv don't append to an array in a loop.

$Report = foreach ($dir in $directory_to_search) {
    $acl = Get-Acl -Path $dir.FullName
    foreach ($ace in $acl.Access) {
        if (!($ace.IdentityReference -eq "BUILTIN\Administrators") -and !($ace.IdentityReference -eq "NT AUTHORITY\SYSTEM")) {
            New-Object PsObject -Property @{
                'FolderName'       = $dir.FullName
                'AD Group or User' = $ace.IdentityReference
                'Permissions'      = $ace.FileSystemRights
            }
        }
    }
}
$Report | Export-Csv ...
user_16301710

Is it true that using StreamWriter should speed up the execution of your script? Did you check it with the Measure-Command cmdlet?

$my = [PSCustomObject]@{Files = 0}
$mc = Measure-Command {
  $excluded_accounts = 'BuiltinAdministratorsSid', 'LocalSystemSid' |
    ForEach-Object { new-object System.Security.Principal.SecurityIdentifier (
                       [System.Security.Principal.WellKnownSidType]::$_, $null
                     ) } |
    ForEach-Object { $_.Translate([System.Security.Principal.ntaccount]).value }
  $current_date = Get-Date -UFormat %Y%m%d
  $file_to_stream_results = "C:\Temp\test_folder\server_permissions_${current_date}.csv"
  dir -Path "${env:windir}" -Recurse -Force -PipelineVariable ls -ErrorAction Ignore |
    where { $_.PSIsContainer } | select -First 10000 |
    ForEach-Object {
      Get-Acl -Path $_.FullName -ErrorAction Ignore -OutVariable acl | Out-Null
      if ( $? ) {
        $my.Files++
        $acl.Access
      }
    } |
    where { $_.IdentityReference.Value -notin $excluded_accounts } |
    ForEach-Object { [PSCustomObject]@{'FolderName'       = $ls.FullName
                                       'AD Group or User' = $_.IdentityReference
                                       'Permissions'      = $_.FileSystemRights} } |
    Export-Csv -Path $file_to_stream_results -Encoding utf8 -NoTypeInformation
}
$my.Files
[System.IO.File]::ReadAllLines($file_to_stream_results).Count - 1
$mc.ToString()

Output

9967
34348
00:01:40.1683791

Using StreamWriter:

$my = [PSCustomObject]@{Files = 0}
$mc = Measure-Command {
  $excluded_accounts = 'BuiltinAdministratorsSid', 'LocalSystemSid' |
    ForEach-Object { New-Object System.Security.Principal.SecurityIdentifier (
                       [System.Security.Principal.WellKnownSidType]::$_, $null
                     ) } |
    ForEach-Object { $_.Translate([System.Security.Principal.ntaccount]).Value }

  $current_date = Get-Date -UFormat %Y%m%d
  $file_to_stream_results = "C:\Temp\test_folder\server_permissions_${current_date}.csv"
  $directory_to_search = dir -Path "${env:windir}" -Recurse -Force -PipelineVariable ls -ErrorAction Ignore |
      where { $_.PSIsContainer } | select -First 10000
  $file = $null
  try {
    $file = New-Object IO.StreamWriter $file_to_stream_results
    $file.WriteLine('"FolderName","AD Group or User","Permission"')
    foreach ( $dir in $directory_to_search ) {
        try {
          $acl = Get-Acl -Path $dir.FullName
          foreach ($ace in $acl.Access) {
              if ( $ace.IdentityReference.Value -notin $excluded_accounts ) {
                $file.WriteLine(('"{0}","{1}","{2}"' -f $dir.FullName, $ace.IdentityReference, $ace.FileSystemRights))
              }
          }
          $my.Files++
        }
        catch {}
    }
  }
  finally {
    if ( $file ) {
      $file.Close()
      $file.Dispose()
    }
  }
}
$my.Files
[System.IO.File]::ReadAllLines($file_to_stream_results).Count - 1
$mc.ToString()

Output

9967
34348
00:01:03.8205373

But if replace the Get-Acl and Export-Csv cmdlets with the [System.IO.Directory]::GetAccessControl function and the Set-Content cmdlet, respectively, then there is almost no difference.

$my = [PSCustomObject]@{Files = 0}
$mc = Measure-Command {
  $excluded_accounts = 'BuiltinAdministratorsSid', 'LocalSystemSid' |
    ForEach-Object { new-object System.Security.Principal.SecurityIdentifier (
                       [System.Security.Principal.WellKnownSidType]::$_, $null
                     ) } |
    ForEach-Object { $_.Translate([System.Security.Principal.ntaccount]).value }
  $current_date = Get-Date -UFormat %Y%m%d
  $file_to_stream_results = "C:\Temp\test_folder\server_permissions_${current_date}.csv"
  dir -Path "${env:windir}" -Recurse -Force -PipelineVariable ls -ErrorAction Ignore |
    where { $_.PSIsContainer } | select -First 10000 |
    ForEach-Object {
      try {
        $acl = [System.IO.Directory]::GetAccessControl($_.FullName)
        $my.Files++
        $acl.Access
      }
      catch {}
    } |
    where { $_.IdentityReference.Value -notin $excluded_accounts } |
    ForEach-Object -Begin {'"FolderName","AD Group or User","Permission"'} `
        -Process { '"{0}","{1}","{2}"' -f $ls.FullName, $_.IdentityReference, $_.FileSystemRights } |
    Set-Content -Path $file_to_stream_results -Encoding UTF8
}
$my.Files
[System.IO.File]::ReadAllLines($file_to_stream_results).Count - 1
$mc.ToString()

Output

9967
34348
00:01:06.7530857
user_33504280
Related