none
exports users info RRS feed

  • Question

  • Hi Experts

    I have display names in CSV file in the below format.

    Names
    Tim A
    Rob V

    i want to import this csv file and get their userprincipalname,samaccountname, email address, office location and export them to csv file, experts please help with the syntax.
    Thursday, January 9, 2020 11:12 AM

Answers

  • I assume by display name you mean the Relative Distinguished Name of the users (the value of the cn attribute). But you might mean the value of the displayName attribute. In either case, it does not uniquely identify the user in the AD domain. The sAMAccountName, called the "pre-Windows 2000 logon name" in ADUC, would. A script should account for the possibility that no name is found, one name is found, or more than one is found. I have used something similar to below using PowerShell:

    # Read CSV into array.
    $Users = Import-CSV -Path Users.csv
    
    # Results for new CSV.
    $Results = @()
    
    # Enumerate user names in array
    ForEach ($User In $Users)
    {
        $Name = $($User.Names)
        $ADUser = Get-ADUser -Filter {Name -eq $Name} -Properties EmailAddress, Office, City
        If (-Not $ADUser) {"User $Name not found."}
        ElseIf ($ADUser.Count -gt 1) {"More than one user $Name found."}
        Else
        {
            # One user found with RDN in the CSV.
            $Results += New-Object PsObject - Property @{
                sAMAccountName = $ADUser.sAMAccountName
                UPN = $ADUser.userPrincipalName
                Email = $ADUser.EmailAddress
                Office = $ADUser.Office
                Location = $ADUser.City
            }
        }
    }
    # Export to a new CSV file.
    $Results | Export-CSV .\Users2.csv -NoTypeInformation

    I retrieved both Office and City above, because either could be considered location. The attribute names are physicalDeliveryOfficeName and l (for location), respectively. The above are the corresponding PowerShell property names. The PowerShell Name property is the RDN, the value of the cn attribute. If you meant the display name shown in ADUC, then substitute DisplayName.

    All default properties exposed by Get-ADUser are retrieved without requesting them. So I only listed the extended properties in the -Properties parameter. Otherwise they would not be retrieved. The default and extended properties supported by Get-ADUser (and the corresponding attribute names) are documented here:

    https://social.technet.microsoft.com/wiki/contents/articles/12037.active-directory-get-aduser-default-and-extended-properties.aspx


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    • Edited by Richard MuellerMVP Thursday, January 9, 2020 8:30 PM Changed $User.Name to $User.Names
    • Marked as answer by Glenn Maxwell Monday, January 20, 2020 5:09 PM
    Thursday, January 9, 2020 12:51 PM

All replies

  • I assume by display name you mean the Relative Distinguished Name of the users (the value of the cn attribute). But you might mean the value of the displayName attribute. In either case, it does not uniquely identify the user in the AD domain. The sAMAccountName, called the "pre-Windows 2000 logon name" in ADUC, would. A script should account for the possibility that no name is found, one name is found, or more than one is found. I have used something similar to below using PowerShell:

    # Read CSV into array.
    $Users = Import-CSV -Path Users.csv
    
    # Results for new CSV.
    $Results = @()
    
    # Enumerate user names in array
    ForEach ($User In $Users)
    {
        $Name = $($User.Names)
        $ADUser = Get-ADUser -Filter {Name -eq $Name} -Properties EmailAddress, Office, City
        If (-Not $ADUser) {"User $Name not found."}
        ElseIf ($ADUser.Count -gt 1) {"More than one user $Name found."}
        Else
        {
            # One user found with RDN in the CSV.
            $Results += New-Object PsObject - Property @{
                sAMAccountName = $ADUser.sAMAccountName
                UPN = $ADUser.userPrincipalName
                Email = $ADUser.EmailAddress
                Office = $ADUser.Office
                Location = $ADUser.City
            }
        }
    }
    # Export to a new CSV file.
    $Results | Export-CSV .\Users2.csv -NoTypeInformation

    I retrieved both Office and City above, because either could be considered location. The attribute names are physicalDeliveryOfficeName and l (for location), respectively. The above are the corresponding PowerShell property names. The PowerShell Name property is the RDN, the value of the cn attribute. If you meant the display name shown in ADUC, then substitute DisplayName.

    All default properties exposed by Get-ADUser are retrieved without requesting them. So I only listed the extended properties in the -Properties parameter. Otherwise they would not be retrieved. The default and extended properties supported by Get-ADUser (and the corresponding attribute names) are documented here:

    https://social.technet.microsoft.com/wiki/contents/articles/12037.active-directory-get-aduser-default-and-extended-properties.aspx


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    • Edited by Richard MuellerMVP Thursday, January 9, 2020 8:30 PM Changed $User.Name to $User.Names
    • Marked as answer by Glenn Maxwell Monday, January 20, 2020 5:09 PM
    Thursday, January 9, 2020 12:51 PM
  • Hi i am getting the below error

    Get-ADUser : Variable: 'Name' found in expression: $Name is not defined.
    At C:\script.ps1:11 char:15
    + ...   $ADUser = Get-ADUser -Filter {Name -eq $Name} -Properties EmailAddr ...
    +                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [Get-ADUser], ArgumentException
        + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Comm
       ands.GetADUser

    Thursday, January 9, 2020 6:47 PM
  • Sorry, I see now that the field name in your CSV file is "Names", not "Name". I corrected the code in my post, although I have not tested it.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Thursday, January 9, 2020 8:32 PM
  • Hi,

    Thank you for posting in our forum.

    According to my knowledge, you can try the script method to post in the script forum

    Script forum link:https://social.technet.microsoft.com/Forums/Windows/en-US/home?forum=winserverpowershell

    Thank you for your understanding and support

    Best wishes
    Vicky


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, January 10, 2020 7:19 AM
  • Hi,

    Just want to confirm the current situations.

    Please feel free to let us know if you need further assistance.

    Best Regards,

    Vicky

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, January 13, 2020 1:40 AM
  • Below script worked for me
    $Users = Get-Content C:\list.csv
    $Results = ForEach ($User in $Users)
    {
       Get-ADUser -Filter "DisplayName -eq '$User'" -Properties DisplayName,EmailAddress,Userprincipalname,title,description,co,personalTitle,EmployeeID,Office,City | 
    Select DisplayName,EmailAddress,Userprincipalname,title,description,co,personalTitle,EmployeeID,Office,City
    } 
    $Results | Export-CSV -Path C:\output.csv -NoTypeInformation
    

    Wednesday, January 15, 2020 12:09 AM
  • @Richar i will check your code and will get back to you
    Wednesday, January 15, 2020 12:19 AM
  • Hi,

    Just checking in to see if the information provided was helpful. Please let us know if you would like further assistance.

    Best Regards,

    Vicky


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, January 16, 2020 2:24 AM
  • Hi,

    Just want to confirm the current situations.

    Please feel free to let us know if you need further assistance.

    Best Regards,

    Vicky


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, January 20, 2020 1:29 AM