Solved

Powershell script Export list of all users and there group membership and export to CSV

Posted on 2013-10-25
11
27,513 Views
1 Endorsement
Last Modified: 2016-11-18
Hi

I need a script that will export all of our enabled users in AD with there group memberships.

Most of the scripts I have found are targeted at exporting a specific groups members.

Our is for auditing, they have asked us to produce a list of every user with each group they are a member of

Thanks
1
Comment
Question by:ncomper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 57

Expert Comment

by:Mike Kline
ID: 39600603
You can user

get-aduser -filter * -properties * | select-object samaccountname memberof

Thanks

Mike
0
 
LVL 5

Author Comment

by:ncomper
ID: 39600608
Hi Mike

It does not seem to like the memberof part,

any ideas?

Thanks
0
 
LVL 40

Expert Comment

by:Subsun
ID: 39600620
If you have windows 2008 R2 Domain controller or above then you can try...
Import-Module Activedirectory
Get-ADUser -Filter * -Properties DisplayName,memberof | % {
 $Name = $_.DisplayName
 $_.memberof | Get-ADGroup | Select @{N="User";E={$Name}},Name
} | Export-Csv C:\temp\report.csv -nti

Open in new window

1
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 57

Expert Comment

by:Mike Kline
ID: 39600627
I forgot the comma

get-aduser -filter * -properties * | select-object samaccountname, memberof
0
 
LVL 5

Author Comment

by:ncomper
ID: 39600637
Subsun

That worked great, is is possible to display the username on the 1st column and then the groups on columns next to it so its 1 user per line

Or is that asking to much :)
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 39600664
Yes you can, but with first method it will be easy for you to filter using users or group..

Import-Module Activedirectory
Get-ADUser -Filter * -Properties DisplayName,memberof | % {
  New-Object PSObject -Property @{
	UserName = $_.DisplayName
	Groups = ($_.memberof | Get-ADGroup | Select -ExpandProperty Name) -join ","
	}
} | Select UserName,Groups | Export-Csv C:\temp\report.csv -NTI

Open in new window

2
 
LVL 1

Expert Comment

by:SsbMs
ID: 39600672
<#
.SYNOPSIS
    Simple script to produce a listing of user's group memberships
.DESCRIPTION
    Script will create a simple listing of a user's group memberships.
    Output is in object format so you can use other Powershell cmdlet's
    with the output, such as Export-CSV, Out-File, ConvertTo-HTML, etc.
   
    Groups are presented using the friendly name, and are sorted
    alphabetically.
.PARAMETER User
    Name of the user you want to list
.INPUTS
    Pipeline
    Get-ADUser    
.OUTPUTS
    PSObject    User Name
                Group Name
.EXAMPLE
    .\Get-UserGroupMembership.ps1 -User thesurlyadmin
    List all of the groups for "thesurlyadmin"
#>

Param (
    [Parameter(Mandatory=$true,ValueFromPipeLine=$true)]
    [Alias("ID","Users","Name")]
    [string[]]$User
)
Begin {
    Try { Import-Module ActiveDirectory -ErrorAction Stop }
    Catch { Write-Host "Unable to load Active Directory module, is RSAT installed?"; Break }
}

Process {
    ForEach ($U in $User)
    {   $UN = Get-ADUser $U -Properties MemberOf
        $Groups = ForEach ($Group in ($UN.MemberOf))
        {   (Get-ADGroup $Group).Name
        }
        $Groups = $Groups | Sort
        ForEach ($Group in $Groups)
        {   New-Object PSObject -Property @{
                Name = $UN.Name
                Group = $Group
            }
        }
    }
}
0
 
LVL 15

Expert Comment

by:achaldave
ID: 39600699
Quest activeroles powershell commands would be easier to query /modify active directory objects, download and install the powrshell extension.
http://www.quest.com/powershell/activeroles-server.aspx
Run the following commands to export results to HTML file

$a = "<style>"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}"
$a = $a + "</style>"

get-qaduser -resultsize 30000 | where {$_.accountisdisabled -ne $true }|  Select-Object  name,samaccountname,{$_.memberof} | convertto-html  -head $a | out-file c:\temp\temp.html

Change the resutsize and out-file appropriate to your environment.
0
 
LVL 5

Author Closing Comment

by:ncomper
ID: 39608221
Excellent as always, thanks
0
 

Expert Comment

by:Tarun Kumar
ID: 41875442
If I need only Active users in the output then what I need to add.


Import-Module Activedirectory
Get-ADUser -Filter * -Properties DisplayName,memberof | % {
  New-Object PSObject -Property @{
      UserName = $_.DisplayName
      Groups = ($_.memberof | Get-ADGroup | Select -ExpandProperty Name) -join ","
      }
} | Select UserName,Groups | Export-Csv C:\temp\report.csv -NTI

Regards,
Tarun
0
 

Expert Comment

by:Intelli-Seeker
ID: 41893325
This forum is awesome! I figured out how to craft the script based on what you suggested and the other forum. Here is the final version of the script (with the names of the searchbase redacted).

Import-Module Activedirectory
Get-ADUser -Filter * -Properties DisplayName,EmployeeID,memberof -searchbase 'OU=Users,OU=CONTAINER,DC=DOMAIN,DC=local' | % {
  New-Object PSObject -Property @{
      UserName = $_.DisplayName
      EmployeeID = $_.EmployeeID
      Groups = ($_.memberof | Get-ADGroup | Select -ExpandProperty Name) -join ","
      }
} | Select UserName,EmployeeID,Groups | Export-Csv C:\Reports\ADreport.csv -NTI

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
This process allows computer passwords to be managed and secured without using LAPS. This is an improvement on an existing process, enhanced to store password encrypted, instead of clear-text files within SQL
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question