locked
Compile a list of firstname, last name and their AD accounts using PowerShell RRS feed

  • Question

  • I have is a list of users First and last names on a CSV file. I'm trying to generate a list of users "first name" , "last name" and  add their "AD account" by using an if statement: If the first and last name match, then add the name to the new column.

    I've accomplished this by getting a full list of all of the users in the OU, then using excel for the comparing and formatting. However, It's racking my brain that I can't seem to figure out how to accomplish this just using PowerShell.

    Could someone point me in the right direction on how this works in Powershell?

    '

    Friday, December 15, 2017 1:16 AM

Answers

  • If you would take the time to learn PowerShell instead of doing everything by accidient and habit you would know how to write this and debug it.

    First run this:

    Import-Csv C:\Name-and-ID.csv | 
        ForEach-Object{
            Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress
        } |
        Select-Object Name, emailAddress, SamAccountName, GivenName, Surname

    Do you get output?

    If so then run this:

    Import-Csv C:\Name-and-ID.csv | 
        ForEach-Object{
            Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress
        } |
        Select-Object Name, emailAddress, SamAccountName, GivenName, Surname |
        Export-CSV C:\ADScriptUPDATE.csv -NoTypeInformation

    Do not use line extender characters.  Learn how to break lines correctly on pipes or with closures.  Always filter, forma, select and export data outside of the selection loop.


    \_(ツ)_/


    • Proposed as answer by BOfH-666 Monday, December 18, 2017 3:03 PM
    • Edited by jrv Monday, December 18, 2017 3:03 PM
    • Marked as answer by Jedd117 Monday, December 18, 2017 3:24 PM
    Monday, December 18, 2017 3:02 PM

All replies

  • Thanks for the information. Unfortunately I'm still stumped. Let me explain:
    Import-Module ActiveDirectory
    
    Import-Csv C:\fileWithColumnsOfNames | ForEach {
    Get-ADUser -Filter {(GivenName -eq $_.FirstName) -and (Surname -eq $_.LastName )} -Properties name, emailAddress, SAMAccountName | `
        Select Name, emailAddress, SamAccountName, GivenName, Surname | `
        Export-CSV C:\pathToExportedData -NoTypeInformation
    }
    

    In my mind, this is importing the CSV file, then fetching the first name (GivenName) and matching up with the "FirstName" column and same for the last name. Then selecting the name, email and account name, etc and exporting this data.
    • Proposed as answer by christopher rl Thursday, March 7, 2019 4:01 PM
    • Unproposed as answer by christopher rl Thursday, March 7, 2019 4:01 PM
    Friday, December 15, 2017 4:24 AM
  • Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress 


    \_(ツ)_/

    Friday, December 15, 2017 5:03 AM
  • I was getting an error: 

    Get-ADUser : The search filter cannot be recognized

    I changed that line to this:

    Get-ADUser -LDAPFilter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress

    This code works without any errors, but when I go to the exported file there's no data in there.


    • Edited by Jedd117 Friday, December 15, 2017 2:25 PM
    Friday, December 15, 2017 2:24 PM
  • That means no matches were found.


    \_(ツ)_/

    Friday, December 15, 2017 8:07 PM
  • Well, I thought about that too. I tested this and verified there's matches that should technically be showing up if this is searching correctly.
    Friday, December 15, 2017 8:47 PM
  • It works fine for me.  Perhaps you CSV is not built correctly.


    \_(ツ)_/

    Friday, December 15, 2017 9:11 PM
  • I've rebuilt the CSV import file as you suggested, and it's running through all of the users. It creates the file the export file, but there's no data in it (I did stop prematurely to make sure it's actually writing to the file). It's actually printing the data in the console instead of exporting this. I've verified the file is writable. Any ideas? Does this actually need to run all the way before exporting data?
    Monday, December 18, 2017 2:52 PM
  • What is the code you are using?


    \_(ツ)_/

    Monday, December 18, 2017 2:54 PM
  • Import-Csv C:\Name-and-ID.csv | ForEach {
    Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress
    
        Select Name, emailAddress, SamAccountName, GivenName, Surname | `
        Export-CSV C:\ADScriptUPDATE.csv -NoTypeInformation
    }


    • Edited by Jedd117 Monday, December 18, 2017 2:58 PM
    Monday, December 18, 2017 2:58 PM
  • If you would take the time to learn PowerShell instead of doing everything by accidient and habit you would know how to write this and debug it.

    First run this:

    Import-Csv C:\Name-and-ID.csv | 
        ForEach-Object{
            Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress
        } |
        Select-Object Name, emailAddress, SamAccountName, GivenName, Surname

    Do you get output?

    If so then run this:

    Import-Csv C:\Name-and-ID.csv | 
        ForEach-Object{
            Get-ADUser -Filter "GivenName -eq '$($_.FirstName)' -and Surname -eq '$($_.LastName)'" -Properties emailAddress
        } |
        Select-Object Name, emailAddress, SamAccountName, GivenName, Surname |
        Export-CSV C:\ADScriptUPDATE.csv -NoTypeInformation

    Do not use line extender characters.  Learn how to break lines correctly on pipes or with closures.  Always filter, forma, select and export data outside of the selection loop.


    \_(ツ)_/


    • Proposed as answer by BOfH-666 Monday, December 18, 2017 3:03 PM
    • Edited by jrv Monday, December 18, 2017 3:03 PM
    • Marked as answer by Jedd117 Monday, December 18, 2017 3:24 PM
    Monday, December 18, 2017 3:02 PM