Author Posts

May 23, 2018 at 12:21 am

I am retrieving AD accounts and properties using a custom attribute. This is working fine, however I want to add a column to the csv called Error for accounts that do not exist. I am trying to incorporate this into one csv file. It is a pretty large dataset of roughly 800K users, any recommendations for increasing efficiency would be appreciated. Before adding the if/else script took 12 hours to run. I tried using streamwriter to improve performance but no gain. Script doesn't work in current form. I understand I am trying to add content that doesn't match any properties in the CSV, however I am not sure how to fix it.

$B = Get-Content C:\UserList.txt

foreach ($U in $B)
{
    $User = get-aduser -Filter {CA -like $U} -Properties EmailAddress,telephoneNumber,SamAccountName,sn,givenName,Initials,DisplayName,CA -Server domainname | Select-Object -Property EmailAddress,telephoneNumber,SamAccountName,sn,givenName,Initials,DisplayName,CA
     
     If ($User -eq $null)
        {

            $User.Error = "$U not found" | Export-Csv C:\Results.txt -NoTypeInformation -Append -Force

        }

    Else {$User | Export-Csv C:\Results.txt -NoTypeInformation -Append}
    

}

May 23, 2018 at 6:07 am

Hello, this is my first post so I hope it is useful. You can try something like the below.

$B = Get-Content C:\List.txt

$collection = foreach ($U in $B) {
    try {
        $user = Get-ADUser $U -Properties EmailAddress, telephoneNumber, SamAccountName, sn, givenName, Initials, DisplayName
        $properties = @{EmailAddress = $user.emailaddress
            TelephoneNumber = $user.telephonenumber
            SamAccountName = $user.samaccountname
            sn = $user.sn
            GivenName = $user.givenname
            Initials = $user.initials
            DisplayName = $user.displayname
            NotFound = $null
            }
        New-Object -TypeName PSObject -Property $properties
    }

    catch {
        $properties = @{ 
            EmailAddress = $null
            TelephoneNumber = $null
            SamAccountName = $null
            sn = $null
            GivenName = $null
            Initials = $null
            DisplayName = $null
            NotFound = $U
            }
        New-Object -TypeName PSObject -Property $properties

    }
  
}         

$collection | Export-Csv C:\output.csv -NoTypeInformation


May 23, 2018 at 6:17 am

Hello, this is my first post so I hope it is useful. You can try something like the below:

$B = Get-Content C:\UserList.txt

$collection = foreach ($U in $B) {
    try {
        $user = Get-ADUser $U -Properties EmailAddress, telephoneNumber, SamAccountName, sn, givenName, Initials, DisplayName
        $properties = @{EmailAddress = $user.emailaddress
            TelephoneNumber = $user.telephonenumber
            SamAccountName = $user.samaccountname
            sn = $user.sn
            GivenName = $user.givenname
            Initials = $user.initials
            DisplayName = $user.displayname
            NotFound = $null
            }
        New-Object -TypeName PSObject -Property $properties
    }

    catch {
        $properties = @{ 
            EmailAddress = $null
            TelephoneNumber = $null
            SamAccountName = $null
            sn = $null
            GivenName = $null
            Initials = $null
            DisplayName = $null
            NotFound = $U
            }
        New-Object -TypeName PSObject -Property $properties

    }
  
}         

$collection | Export-Csv C:\UserList.csv -NoTypeInformation


May 23, 2018 at 4:34 pm

$User.Error = "$U not found" | Export-Csv C:\Results.txt -NoTypeInformation -Append -Force

This will be why this doesn't work. You need to split this line in two:

$User.Error = "$U not found"
$User | Export-Csv C:\Results.txt -NoTypeInformation -Append -Force

Now, as to the other quandary... your main issue here is that you're opening and closing this file several times a second. There's absolutely no need for that. Just compile your objects into an array, and have the cmdlet build the file all at once before saving to disk. -Append exists for when you have an existing file that you want to tack on a bunch to, not really meant for building files one piece at a time and saving in between. That much disk I/O will slow everything down, no matter how you do it!

Also, please consider renaming your variables. This is going to give you some grief later. If you come back to this in a month, and already
have to scan through half the script to remember what the variable is for, you need a better name. There's nothing wrong with
verbose names; the Assembly days are dwindling, and the ability to be verbose is quite valuable.

Additionally, you'll run into trouble trying to put an object with no properties into a CSV file. CSV files need to have all objects with the same set of properties, so you'll have to go through and set them to $null in a custom object, and then tack on your error property. All objects will need an error property for it to be exported properly, as well, so we can add it in the Select-Object clause.

$UserList = Get-Content -Path 'C:\UserList.txt'
# Assign a variable to the output of the loop to gather all outputted data into a neat array
$OutputData = foreach ($User in $UserList) { 
    $PropertyList = 'EmailAddress', 'TelephoneNumber', 'SamAccountName', 'SN', 'GivenName', 'Initials', 'DisplayName', 'CA'
    # Braces for -Filter is common, but will give you a lot of grief in many, 
    # many cases. Just use a plain string; quotes, no braces.
    $UserObject = Get-Aduser -Filter "CA -like $User" -Properties $PropertyList -Server $DomainName | 
        Select-Object -Property $PropertyList, @{
            Name       = "Error"
            Expression = { $null }
        } # Adding the error as a null property. If you don't do this, the export will ignore it unless the VERY FIRST object has this property set.
     
     if ($UserObject) {
        # 'Drop' the object to output to have the loop pick it up and wrap it into an array for $OutputData variable
        # Alternate is to be explicit: Write-Output $UserObject
        $UserObject
     }
     else {
        $NullUser = @{}
        foreach ($Property in $PropertyList) {
            $NullUser[$Property] = $null
        }
        $NullUser['Error'] = "$User not found"
        # Again, output the null user as an object (it's been a simple hashtable up to now)
        [PsCustomObject] $NullUser
     }
}
# Export the whole thing at once, only one larger disk I/O event 
# instead of thousands of tiny ones (less OS file access overhead)
$OutputData | Export-Csv -Path 'C:\Results.txt' -NoTypeInformation