question

PatrickMullen-7557 avatar image
0 Votes"
PatrickMullen-7557 asked PatrickMullen-7557 commented

Get-User from CSV and export $null to CSV

Hello,

I'm trying to perform what I thought would be a simple command. I want to import a csv of users (first/last name) and export the errors to a csv. The purpose is so when I get requests to add hundreds of users to a DL I can run the csv through PS, export the user accounts that don't exist with that first/last name and send the errors back to the business so they can do the leg work of looking up the correct user names.

I searched google for hours and couldn't find what I was looking for.

Thank You

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

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

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RichMatheisen-8856 edited

Here's a possible skeleton:

 $badusers = @()
 $u = Import-CSV c:\junk\newusers.csv
    
 ForEach ($user in $u){
     $errors = @()
     [array]$x = Get-ADUser -Filter "givenname -eq $($user.firstname) -and surname -eq $($user.lastname)"
     if ($x.count -gt 1 ){
         $errors += "there are multiple users in the AD with names that match both first and last names"
     }
     elseif ($x.count -eq 1){
         $errors += "a user already exists with the same first and last name"
     }
     elseif ($x.count -eq 0){
         # do nothing -- there is no match on first and last names
     }
     if ($errors.count -gt 0){
         $errors |
             ForEach-Object{
                 $badusers += [PSCustomObject]@{
                                 FirstName = $user.firstname
                                 LastName  = $user.lastname
                                 Error     = $_
                 }
             }
         continue
     }
     # process the "good" user here
 }
 $badusers | Export-CSV c:\junk\badusers.csv -NoTypeInformation


I think the dependence on having a unique first/last name as a means of identifying an AD user is not the best approach, especially if you're managing "hundreds" of new users at a time (which seems to imply that you have multiple thousands of users in the AD). There are absolutely going to be duplicates.

5 |1600 characters needed characters left characters exceeded

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

AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered RichMatheisen-8856 commented

Hi @PatrickMullen-7557 ,

maybe this is working for you:

 Import-Module ActiveDirectory 
 $missingUser = @()
 Import-Csv -Path 'c:\temp\UserFile.csv' | ForEach-Object {
     $adUser = Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and sn -eq '$($_.LastName)'"
     if (!$aduser) {
         $missingUser = $missingUser + "$($_.FirstName),$($_.LastName)"
     }   
 }
 "FirstName,LastName" | Out-File -FilePath 'c:\temp\MissingUser.csv' -Encoding utf8
 $missingUser | Out-File -FilePath 'c:\temp\MissingUser.csv' -Encoding utf8 -Append


UserFile.csv

 FirstName,LastName
 Peter,Pan
 Mickey,Mouse
 William,Smith
 Trixi,Miller
 Brian,May
 Frodo,Baggins



(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

· 5
5 |1600 characters needed characters left characters exceeded

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

I think he's asking to identify the users in the CSV as an error if they have the same first and last names as another user in the AD. I had to read the question a few times before I understood it.

FWIW, I'll reiterate that I think treating First/Last name combinations as unique identities is a bad idea. Imagine NOT duplicating a first name if the last name is, say "Smith"!
most-common-us-surnames-1422656


0 Votes 0 ·

Hi @RichMatheisen-8856 ,
i
I had to read the question more than once as well ;-) I read it like "get user with first and lastname doesn't exist ....."

export the user accounts that don't exist with that first/last name and send the errors back to the business

I full agree with your statement "the first and lastname isn't a good approach to do this". I would recommend to use a "unique property" like the email-address.


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten


0 Votes 0 ·

Thank you for the replies thus far. I'm sorry if my question was confusing. I am trying to import the csv, which has a list of users by first(space)last name. We often get requests like these and most times the business requester that submitted it doesn't realize that a good number of the users in their list are no longer with the company or the name they supplied is a nickname and not the users actual name in AD.

I want to run the csv through PowerShell and have it export all the usernames that aren't valid into another csv. I can then send the business requester the csv of non-existent usernames and have them do the work of finding the actual AD account names of the users that weren't valid.

Again, thank you for the quick replies.

0 Votes 0 ·
Show more comments
RichMatheisen-8856 avatar image
1 Vote"
RichMatheisen-8856 answered PatrickMullen-7557 commented

Well, that makes for shorter code.

If I assume that duplicate AD users aren't a problem (although I think will be) and that a mailNickName is placed into the "FirstName" column of the CSV then you should be able to use that to shorten the list of "bad" users by adding an additional condition to the filter..

 Import-CSV c:\junk\newusers.csv |
     ForEach-Object {
         [array]$x = Get-ADUser -Filter "(givenname -eq $($_.firstname) -and surname -eq $($_.lastname)) -or (mailNickname -eq $($_.firstname))"
         if ($x.count -eq 0) {
             [PSCustomObject]@{
                 FirstName = $_.firstname
                 LastName  = $_.lastname
                 Error     = "no user exists that matches either the first and last name, or whose mailNickname matches the first name"
             }
         }
      } | Export-Csv c:\junk\badusers.csv -NoTypeInformation
· 1
5 |1600 characters needed characters left characters exceeded

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

Thanks Rich. I'll test it tomorrow. It will come in handy frequently I'm sure. I'll comment or accept the answer tomorrow. Thanks again.

0 Votes 0 ·