none
Get-ADUser infomration by querying AD after first pulling GivenName and Surname from a CSV file

    Question

  • I have been trying to do something very simple for several hours, and have now got pretty fed up as nothing works and am asking for help. I have tried various iterations by looking at Powershell Help examples and cannibalising others scripts from online, but currently I have:

    $users = Import-CSV "C:\temp\new3.csv" -Header GivenName,Surname

    $UserInfo = $Users | foreach $_ {Get-ADUser -Filter {"(GivenName -eq '$_.GivenName')'

    -and (Surname -eq '$_.Surname')"} -Properties *}'

    | Select SAMAccountName, DistinguishedName, emailaddress |` export-csv -path C:\Temp\alluserresults.csv -force

    Can anyone tell me why it does not work? Something to do with the way I am trying to filter on the givenname and surname together.

    The error is on parsing the query "  + CategoryInfo          : ParserError: (:) [Get-ADUser], ADFilterParsingException
        + FullyQualifiedErrorId : ActiveDirectoryCmdlet:Microsoft.ActiveDirectory.Management.ADFilterParsingException,Microsoft.ActiveDirectory.Management.Commands.GetADUser"

    Sadly that is not amazingly helpful to me. So if anyone can help, that would be great.

    Wednesday, December 5, 2018 11:51 PM

All replies

  • Every one of your guesses was completely wrong.

    Import-CSV C:\temp\new3.csv |
        ForEach-Object {
            Get-ADUser -Filter "GivenName -eq '$($_.GivenName)' -and Surname -eq '$($_.Surname)'" -Properties DistinguishedName, emailaddress
        } |
        Select SAMAccountName, DistinguishedName, emailaddress |`
        Export-Csv C:\Temp\alluserresults.csv 

    Before confusing yourself even more take this beginners tutorial to get started with correct information.

    1. Microsoft Virtual Academy - Getting Started with Microsoft PowerShell
    2. PowerShell Documentation
    3. PowerShell Style Guidelines


    \_(ツ)_/

    Thursday, December 6, 2018 12:33 AM
    Moderator
  • You might make a little step back and start with learning the very basics of Powershell from scratch. You cannot properly learn a technology by guessing or sneaking into other peoples code. I'm sure it will pay off for you in the future.

    $UserList = Import-Csv -Path 'C:\temp\new3.csv' -Header GivenName,Surname
    
    $Result = Foreach($User in $UserList){
        $GivenName = $User.GivenName
        $Surname = $User.Surname
        Get-ADUser -Filter "GivenName -eq '$GivenName' -and Surname -eq '$Surname'" -Properties SAMAccountName, DistinguishedName, emailaddress
    } 
    $Result |
        Select-Object -Property SAMAccountName, DistinguishedName, emailaddress |
            Export-Csv -Path 'C:\Temp\alluserresults.csv' -NoTypeInformation


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH_666 Thursday, December 6, 2018 12:34 AM
    Thursday, December 6, 2018 12:33 AM
  • Hmmm.  Why does everyone insist on using two code sections when only one is required?  It is like using a double negative.  The effect and efficiency of the PowerShell design are completely erased.  You might as well use VBScript.

    Anyway - that's my take for today.


    \_(ツ)_/

    Thursday, December 6, 2018 12:36 AM
    Moderator
  • Well thanks for looking jrv, but the server did not like your code either:

    Get-ADUser : The search filter cannot be recognized
    At line:3 char:9
    +         Get-ADUser -Filter "GivenName -eq '$($_.GivenName)' -and Surn ...
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
        + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    Any other thoughts? Looks much the same as other errors I have been getting all day.



    • Edited by GoranV19 Thursday, December 6, 2018 12:55 AM information
    Thursday, December 6, 2018 12:46 AM
  • Well thanks for looking jrv, but the server did not like your code either:

    Get-ADUser : The search filter cannot be recognized
    At line:3 char:9
    +         Get-ADUser -Filter "GivenName -eq '$($_.GivenName)' -and Surn ...
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
        + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    Any other thoughts? Looks much the same as other errors I have been getting all day.



    What is in your CSV file?  It looks like it is not a CSV file.


    \_(ツ)_/

    Thursday, December 6, 2018 12:59 AM
    Moderator
  • James,

    Obviously I don't have your experience and sometimes is "working" just good enough. But as usual is your code more sophisticated and lean and efficient. I whish I had your brain.  ;-) :-D


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''



    • Edited by BOfH_666 Thursday, December 6, 2018 1:03 AM
    Thursday, December 6, 2018 1:01 AM
  • If your CSV file does not contains headers you should add the according parameter to the code. You could also try my code example.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH_666 Thursday, December 6, 2018 1:18 AM
    Thursday, December 6, 2018 1:08 AM
  • James,

    Obviously I don't have your experience and sometimes is "working" just good enough. But as usual is your code more sophisticated and lean and efficient. I whish I had your brain.  ;-) :-D


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''



    Don't take it too seriously.  I was just trying to shake up your memory circuits so you think differently about code.  Your code is not wrong or even bad.  It just misses some things that would accelerate your approach.

    Here is another mental stimulant: https://en.wikipedia.org/wiki/Functional_programming

    It may seem a stretch but try it on.  Leave it in the back of your brain.  In time it will change how you see coding.


    \_(ツ)_/

    Thursday, December 6, 2018 1:10 AM
    Moderator
  • It IS a CSV file with just two columns. I have already gone down that route. I created a fresh csv and pasted in the values but it made no difference. I also tried get-content with a text file containing the same values.
    Thursday, December 6, 2018 12:18 PM
  • Could you post a small excerpt of your CSV file, please? Just a few lines - and formatted as code as well please.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH_666 Thursday, December 6, 2018 12:24 PM
    Thursday, December 6, 2018 12:22 PM
  • You might make a little step back and start with learning the very basics of Powershell from scratch. You cannot properly learn a technology by guessing or sneaking into other peoples code. I'm sure it will pay off for you in the future.

    $UserList = Import-Csv -Path 'C:\temp\new3.csv' -Header GivenName,Surname
    
    $Result = Foreach($User in $UserList){
        $GivenName = $User.GivenName
        $Surname = $User.Surname
        Get-ADUser -Filter "GivenName -eq '$GivenName' -and Surname -eq '$Surname'" -Properties SAMAccountName, DistinguishedName, emailaddress
    } 
    $Result |
        Select-Object -Property SAMAccountName, DistinguishedName, emailaddress |
            Export-Csv -Path 'C:\Temp\alluserresults.csv' -NoTypeInformation


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    When I tried your code I got this error:

    Get-ADUser : The search filter cannot be recognized
    At line:6 char:5
    +     Get-ADUser -Filter "GivenName -eq '$GivenName' -and Surname -eq ' ...
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
        + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    Id appreciate any further input.

    Thursday, December 6, 2018 12:30 PM
  • Just do this for a quick diagnostic.

    Import-CSV C:\temp\new3.csv |
        ForEach-Object {
            Write-Host $_.GivenName  $_.Surname
            if($_.GivenName -and $_.Surname){
                Get-ADUser -Filter "GivenName -eq '$($_.GivenName)' -and Surname -eq '$($_.Surname)'" -Properties DistinguishedName, emailaddress
            } else {
                Write-Host 'Blank entries'
            }
        } 


    \_(ツ)_/

    Thursday, December 6, 2018 12:30 PM
    Moderator
  • When I tried your code I got this error:

    Get-ADUser : The search filter cannot be recognized
    At line:6 char:5
    +     Get-ADUser -Filter "GivenName -eq '$GivenName' -and Surname -eq ' ...
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [Get-ADUser], ADException
        + FullyQualifiedErrorId : ActiveDirectoryServer:8254,Microsoft.ActiveDirectory.Management.Commands.GetADUser

    Id appreciate any further input.

    It seems most likely that you do not have a CSV or your CSv has blank entries.  You have to take some time and try to understand how each piece works and what that piece is technically.  We cannot see your system and cannot guess at what we cannot see.

    Use the diagnostic code I posted above and/or post your CSV file.


    \_(ツ)_/

    Thursday, December 6, 2018 12:34 PM
    Moderator
  • Could you post a small excerpt of your CSV file, please? Just a few lines - and formatted as code as well please.

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    As below:

    GivenName Surname


    Mark Shepherd
    Jon Miles
    Jamie Collins

    It is just two columns.

      
    • Edited by GoranV19 Thursday, December 6, 2018 12:36 PM Information
    Thursday, December 6, 2018 12:35 PM
  • OK, so your CSV file does not have headers and you don't use the standard delimiter. You will have to change your code to adapt this. Please have in mind that we cannot see your screen and we don't know your environment. Do you use a space or a tab as delimiter?

    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''

    Thursday, December 6, 2018 12:39 PM
  • Could you post a small excerpt of your CSV file, please? Just a few lines - and formatted as code as well please.


    Live long and prosper!

    (79,108,97,102|%{[char]$_})-join''


    As below:

    GivenName Surname


    Mark Shepherd
    Jon Miles
    Jamie Collins

    It is just two columns.

      

    This is not a Csv file.  It is just a text file.  You will need to make it a CSV if you want the code to work.

    To test a Csv just import it and look at the results

    Import-Csv filename.csv

    When you get the file built correctly you will see it listed as an object collection.

    Note that, in the real world, a name can contain spaces so using space as a delimiter will not work correctly.


    \_(ツ)_/


    • Edited by jrvModerator Thursday, December 6, 2018 12:46 PM
    Thursday, December 6, 2018 12:45 PM
    Moderator
  • The CSV should have a header line defining the field names (comma delimited). Then each subsequent line needs a value for each field, comma delimited. For example:

    GivenName,Surname
    Fred,Johnson
    Samuel,Wilson
    Sally,Cannon
    Mary,McCann


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Thursday, December 6, 2018 1:05 PM
  • 

    MY CSV Input Sample 

    GivenName,Surname
    john,Smith
    Robin,Hood

    Script Used

    $Users = import-csv -Path C:\temp\Book1.csv -Encoding UTF8 foreach ($user in $users) {

    get-aduser -filter * -Properties SAMAccountName, DistinguishedName, emailaddress | ? {$_.GivenName -match $user.GivenName -and ($_.Surname -match $user.Surname)} | select SAMAccountName, DistinguishedName, emailaddress | export-csv C:\temp\results.csv -NoTypeInformation -Append }

    Results

    SAMAccountName,DistinguishedName,emailaddress
    SmithJ,"CN=John Smith,OU=Users,DC=emea,DC=dc,DC=net",John.Smith@domainname.co.uk
    HoodR,"CN=Robin Hood,OU=Users,DC=emea,DC=dc,DC=net",Robin.Hood@domainname.co.uk

    Just quickly done this and Works for me!

    • Edited by ktitchard Thursday, December 6, 2018 2:34 PM
    Thursday, December 6, 2018 2:09 PM
  • 
    $Users = import-csv -Path C:\temp\Book1.csv -Encoding UTF8
    
    foreach ($user in $users) {
    
             get-aduser -filter * -Properties SAMAccountName, DistinguishedName, emailaddress | ? {$_.GivenName -match $user.GivenName -and ($_.Surname -match $user.Surname)} | select SAMAccountName, DistinguishedName, emailaddress | export-csv C:\temp\results.csv -NoTypeInformation -Append
    
            }
    Just quickly done this and Works for me!


    .. your domain controller must be happy ..
    Thursday, December 6, 2018 2:14 PM

  • You know what, all I did was to clear the spaces out of the csv file, export-csv to a new file and it worked! I wrote a small script yesterday that was mostly my own (I had to borrow from others online on how to filter on two separate properties together) which did not work, and the one I posted was after about 50 bits of cannibalisation from other lines of code found online which also failed to work. If the originating file was sorted first, I could have saved myself half day. Just goes to show to look at the smallest things first. Thanks a lot.
    • Proposed as answer by jrvModerator Thursday, December 6, 2018 9:05 PM
    Thursday, December 6, 2018 2:29 PM
  • 

    $Users = import-csv -Path C:\temp\Book1.csv -Encoding UTF8
    
    foreach ($user in $users) {
    
             get-aduser -filter * -Properties SAMAccountName, DistinguishedName, emailaddress | ? {$_.GivenName -match $user.GivenName -and ($_.Surname -match $user.Surname)} | select SAMAccountName, DistinguishedName, emailaddress | export-csv C:\temp\results.csv -NoTypeInformation -Append
    
            }

    Just quickly done this and Works for me!

    .. your domain controller must be happy ..

    How do you mean? I would usually set a searchbase
    Thursday, December 6, 2018 2:32 PM
  • 

    $Users = import-csv -Path C:\temp\Book1.csv -Encoding UTF8
    
    foreach ($user in $users) {
    
             get-aduser -filter * -Properties SAMAccountName, DistinguishedName, emailaddress | ? {$_.GivenName -match $user.GivenName -and ($_.Surname -match $user.Surname)} | select SAMAccountName, DistinguishedName, emailaddress | export-csv C:\temp\results.csv -NoTypeInformation -Append
    
            }

    Just quickly done this and Works for me!

    .. your domain controller must be happy ..

    How do you mean? I would usually set a searchbase

    Ure receiving whole set of AD users each loop ..   filter accounts before pipeline
    Thursday, December 6, 2018 2:53 PM
  • This filter should work and be faster.  You can use -like and add a * if you need to.  -match is not supported (regex).  The doublequotes and singlequotes are required in this order.  The $( ) is required to interpret a $object.property within a string.

    get-aduser -filter "givenname -eq '$($user.givenname)' -and surname -eq '$($user.surname)'"

    • Edited by JS2010 Tuesday, December 11, 2018 2:09 PM
    Thursday, December 6, 2018 3:16 PM
  • 

    MY CSV Input Sample 

    GivenName,Surname
    john,Smith
    Robin,Hood

    Script Used

    $Users = import-csv -Path C:\temp\Book1.csv -Encoding UTF8 foreach ($user in $users) {

    get-aduser -filter * -Properties SAMAccountName, DistinguishedName, emailaddress | ? {$_.GivenName -match $user.GivenName -and ($_.Surname -match $user.Surname)} | select SAMAccountName, DistinguishedName, emailaddress | export-csv C:\temp\results.csv -NoTypeInformation -Append }

    Results

    SAMAccountName,DistinguishedName,emailaddress
    SmithJ,"CN=John Smith,OU=Users,DC=emea,DC=dc,DC=net",John.Smith@domainname.co.uk
    HoodR,"CN=Robin Hood,OU=Users,DC=emea,DC=dc,DC=net",Robin.Hood@domainname.co.uk

    Just quickly done this and Works for me!

    That cannot work as posted and will return all users for each row in the CSV file.  The original code is correct but the CSV is not.  REad the above discussion to see what is at issue.


    \_(ツ)_/

    Thursday, December 6, 2018 9:04 PM
    Moderator

  • You know what, all I did was to clear the spaces out of the csv file, export-csv to a new file and it worked! I wrote a small script yesterday that was mostly my own (I had to borrow from others online on how to filter on two separate properties together) which did not work, and the one I posted was after about 50 bits of cannibalisation from other lines of code found online which also failed to work. If the originating file was sorted first, I could have saved myself half day. Just goes to show to look at the smallest things first. Thanks a lot.

    Always start by inspecting each component you have contributed. You will get better at this with experience


    \_(ツ)_/

    Thursday, December 6, 2018 9:06 PM
    Moderator
  • Hi,

    Was your issue resolved?

    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.

    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.

    If no, please reply and tell us the current situation in order to provide further help.

    Best Regards,

    Lee


    Just do it.

    Tuesday, December 11, 2018 6:36 AM
    Moderator