Announcement

Collapse
No announcement yet.

Retrieve properties of all users in an OU to a CSV file

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Retrieve properties of all users in an OU to a CSV file

    I am able to retrieve all properties, but I am not able to format it properly in Excel

    get-aduser -searchbase 'ou=user accounts,dc=contoso,dc=com' -filter * -properties samaccountname, displayname, emailaddress, office, memberof |
    select samaccountname, displayname, emailaddress, office, @{name="MemberOf";expression={$_.memberof -join "`n"}} | export-csv "c:\users.csv" -notypeinformation

    Which will ge me a csv file containing samaccouname, displayname, emailaddress, office and group membership of all users in the user accounts ou. Viewing it in Notepad works fine, but if I view it in Excel, its quite cluttered.

    Notepad


    Excel 2010


    I import it into Excel like this
    1. start empty Excel worksheet
    2. click Data
    3. choose from text, then browse to c:\users.csv
    4. On the Text Import Wizard, click next on the first page
    5. Tick off for Comma, click Next, and then click Finish
    6. Choose new worksheet, click ok

    How can I format it properly in Excel, and better yet, how can I retrieve name of groups instead of their DN?

  • #2
    Re: Retrieve properties of all users in an OU to a CSV file

    Could you give an example of what you would like to see - the Excel appears to match the CSV perfectly (as one would expect)
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Retrieve properties of all users in an OU to a CSV file

      In Excel, I would like to see all groups the user is member of in the E column, while here its stretched into the A, B, C and D column too.

      I would also like to see only the name of the group, rather than the distinguished name, such as Sales instead of CN=sales,ou=...

      Comment


      • #4
        Re: Retrieve properties of all users in an OU to a CSV file

        This worked

        get-aduser -searchbase 'ou=test,ou=user accounts,dc=contoso,dc=com' -filter * -properties samaccountname, displayname, emailaddress, office, memberof |
        select samaccountname, displayname, emailaddress, office, @{n='MemberOf'; e= { ( $_.memberof | % { (Get-ADObject $_).Name }) -join "," }} | Sort-Object -Property Name | export-csv "c:\users.csv" -notypeinformation

        But I am not quite sure what this part does

        @{n='MemberOf'; e= { ( $_.memberof | % { (Get-ADObject $_).Name }) -join "," }}

        Well, I know what it does, but if someone could explain how it does it, I would appreciate it.

        Comment


        • #5
          Re: Retrieve properties of all users in an OU to a CSV file

          looking, the CSV output doens't look right.
          It looks like it's been putting line breaks or commas in where they don't below

          possibly a parsing error based on data in group names

          eitherway, glad your other outcome sorted i and thanks for posting back
          Please do show your appreciation to those who assist you by leaving Rep Point https://www.petri.com/forums/core/im.../icon_beer.gif

          Comment

          Working...
          X