ncomper
asked on
Powershell script Export list of all users and there group membership and export to CSV
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
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
ASKER
Hi Mike
It does not seem to like the memberof part,
any ideas?
Thanks
It does not seem to like the memberof part,
any ideas?
Thanks
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
I forgot the comma
get-aduser -filter * -properties * | select-object samaccountname, memberof
get-aduser -filter * -properties * | select-object samaccountname, memberof
ASKER
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 :)
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 :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<#
.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 ,ValueFrom PipeLine=$ 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
}
}
}
}
.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.
List all of the groups for "thesurlyadmin"
#>
Param (
[Parameter(Mandatory=$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
}
}
}
}
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:pea chpuff;}"
$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,{$_.me mberof} | convertto-html -head $a | out-file c:\temp\temp.html
Change the resutsize and out-file appropriate to your environment.
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:pea
$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,{$_.me
Change the resutsize and out-file appropriate to your environment.
ASKER
Excellent as always, thanks
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
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
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
get-aduser -filter * -properties * | select-object samaccountname memberof
Thanks
Mike