Performance Issue With Out-File

This topic contains 3 replies, has 2 voices, and was last updated by  Von 1 year, 3 months ago.

  • Author
  • #42072


    I have a script that connects to eDirectory, and gives me a list of groups & group members. The script works, returning 570,515 rows of data. If I Write-Host the results to screen, it takes about 5 minutes to complete. However, I don't want results to screen, I want the results in a CSV or TXT so I can get the data loaded into a SQL table.

    When I try to Out-File the results using a $Query.SizeLimit = 100, I can get that portion of the data in about 1.5 minutes
    When I try to Out-File the results using no size limit, the script never finishes.

    This is the entire code. PI data is replaced with #

    #Setup Modules
    Import-Module ActiveDirectory
    Add-Type -AssemblyName System.DirectoryServices
    #Setup eDirectory Connection Variables
    $eDirPath = 'LDAP://####/o=####'
    $eDirUser = '########'
    $eDirPWD = '########'
    $eDirAuthType = 'None'
    #Establish eDirectory Connection and Enumerate
    $Root = New-Object System.DirectoryServices.DirectoryEntry -argumentlist $eDirPath,$eDirUser,$eDirPWD,$eDirAuthType
    $Query = New-Object System.DirectoryServices.DirectorySearcher
    $Query.SearchRoot = $Root
    #$Query.SizeLimit = 100 #limits results for testing purposes. Comment-out for full results
    $Query.Filter = "(|(ObjectClass=CCGroupApplication)(ObjectClass=CCGroupRole))"
    $SearchResults = $Query.FindAll()
    #Take all requested group names and group members, pipe them to CSV
    $CSVoutput = @() # creates an empty $CSVoutput variable
    ForEach ($Result in $SearchResults) `
       $CCGroupALL = [PSCustomObject]$Result.Properties
            ForEach ($Item in $CCGroupALL)
                    $Group = $
                    ForEach ($Member in $Item.member)
                        #Replace strips everything after the member ID
                        $Member = $Member -Replace "uid=",""
                        $Member = $Member -Replace "cn=",""
                        $Member = $Member -Replace ",ou=people",""
                        $Member = $Member -Replace ",ou=Clients",""
                        $Member = $Member -Replace ",ou=Employees",""
                        $Member = $Member -Replace ",ou=direct",""
                        #Write-Host "$Group",",","$Member"   #runs in 5 minutes
                        $CSVoutput += "$Group , $Member"    #adds each "cn , member" to the $CSVoutput variable
    $CSVoutput | Out-File C:\Users\####\Desktop\CWSiGroupMembers.csv

    The data is returned as the "GroupName , GroupMember" for each result. Like:

    GroupA , jsmith
    GroupA , sjones
    GroupB , jsmith
    GroupC , bsmith

    Ultimately, I'm hoping someone can help me get the results into a file, even if the script takes a while to complete. I'm going to schedule it to run as a nightly job in SQL Server.

  • #42208

    Max Kozlov

    I can't test this on large data but improvements can be
    1. Don't collect big data in arrays, because array += value rebuild array
    2. Use piping and native export-csv cmdlet instead manual string building

    thus, code schema may be like that:

    # [initialization...]
    # Here you must limit DirectoryEntry fields for output, this can speedup your query
    $Query.FindAll() | Foreach-Object {
     # [data preparation if needed]
     $_ | select-Object -Property property1, property2, @{n='Prperty3';e={$_.ValueForProperty3}}
    } | Export-Csv -Encoding Utf8 -Delimiter -'delimiterchar' -Path OutputPathTo\File.csv

    btw, if you Import-Module ActiveDirectory why you use DirectorySearcher ?

    Get-ADObject -LdapFilter '(filter here)'  -Property CN, Member -ResultSetSize $null |
    Select-Object CN, @{n='Members'; e={$_.Member -replace 'r1' -replace 'r2' -replace 'r3' }} |
    Export-Csv -Path OutputPathTo\File.csv
    • This reply was modified 1 year, 3 months ago by  Max Kozlov.
    • This reply was modified 1 year, 3 months ago by  Max Kozlov.
  • #42216

    Max Kozlov

    and.... I don't see your data but believe you can do just one -replace using regular expressions

    for example
    can be replaced to
    that way
    $data -replace '^cn=([^,]+),.*','$1'
    here I extract (...) all characters after cn= that must be in line beginning ^ and before , into $1 and replace full string (greedy .* after main regex) to extracted value

    • This reply was modified 1 year, 3 months ago by  Max Kozlov.
  • #42338


    Hi Max,
    Thanks for the ideas! I used the regex you suggested. I also realized that a significant number of unneeded results could be eliminated using a Where clause in the ForEach loop.

                    ForEach ($Member in $Item.member | 
                        Where {$_ -notlike 'cn=[0-9]*' -and $_ -notlike 'uid=[0-9]*'})

    The new filter I added allows the Out-File process to complete in about 4.5 minutes. I understand that my code could be better, faster, etc., but the timing is acceptable for my purposes.
    Thanks again for your help.

You must be logged in to reply to this topic.