How to export AD security list to Excel

Question

How can I export the security member's list from a security group to an excel sheet. I've found some code on the web but I would need an UI or a software that can do this.

I'm open to Powershell tho...

Regards,

D.

Answer 1

CSVDE will do what you're asking.

Answer 2

Below is a quick hack up of a user information script i use to get user information via powershell, and put it into an excel spreadsheet:

Please not I havn't tested these modifications, and they would need some work to do more than one group at a time

#Make ADSI Connection
$Group = [ADSI]"LDAP://<domain>/<ldap_cn_of_group"

#Setup our excel File
$Excel = new-object -comobject Excel.Application
$Workbook = $Excel.workbooks.add()
$Worksheet = $Workbook.worksheets.item(1)

# Setup a counter for our rows(Start at 2 0 wich is cell A2, the header stuff goes at 1,0 which is A1)
$Row = 2

#Create File and Header information
#sc -path ADUIout.txt -value "Group,Membership"
$Worksheet.cells.item(1,1) = "Group"
$Worksheet.cells.item(1,4) = "Group Membership"
#$Excel.visible = $TRUE

    $membership = $null
    foreach ($member in $Group.member)
    {
        $Worksheet.cells.item($Row, 1) = "$Group"
        $Worksheet.cells.item($Row, 2) = "$member"
        $Row++
    }

$Workbook.saveas("H:\ADUI.xls")
$Workbookl.save()
$Excel.quit()

Here is the original script in case anyone would like to see it, or if you wanted to take some of the concepts:

#Active Directory User information script

#Make ADSI Connection
$AD_Top = [ADSI]"LDAP://<domain>/<base_LDAP_String>"

#Setup our excel File
$Excel = new-object -comobject Excel.Application
$Workbook = $Excel.workbooks.add()
$Worksheet = $Workbook.worksheets.item(1)

# Setup a counter for our rows(Start at 2 0 wich is cell A2, the header stuff goes at 1,0 which is A1)
$Row = 2

#Create File and Header information
#sc -path ADUIout.txt -value "User Name,Container,Last Logon, Logon Count, Group membership"
$Worksheet.cells.item(1,1) = "User Name"
$Worksheet.cells.item(1,2) = "Container"
$Worksheet.cells.item(1,3) = "Last Logon Count"
$Worksheet.cells.item(1,4) = "Group Membership"
#$Excel.visible = $TRUE
#Create the Header


foreach ($Container in $AD_Top.psBase.children)
{
    if(($Container.psBase.properties.name -notmatch "Computers") -AND ($Container.psBase.properties.name -notmatch "Servers") `
        -AND ($Container.psBase.properties.name -notmatch "Domain Controllers") `
        -AND ($Container.psBase.properties.name -notmatch "EmailDistribution") `
        -AND ($Container.psBase.properties.name -notmatch "Security Groups") `
        -AND ($Container.psBase.properties.name -notmatch "Microsoft Exchange System Objects") `
        -AND ($Container.psBase.properties.name -notmatch "System") `
        -AND ($Container.psBase.properties.name -notmatch "ForeignSecurityPrincipals"))
    {
        $outputCont = $Container.psBase.properties.name
        foreach ($user in $Container.psBase.children)
        {
            $dirSearch = new-object directoryservices.directorysearcher($user)
            $LastLogon = [datetime]::fromfiletime(($DirSearch.findone().properties.lastlogon)[0])
            $Username = $User.cn
            $LogonCount = $User.logoncount
            $membership = $null
            foreach ($group in $user.memberof)
            {
                $membership =  $membership + $group.substring(3).Remove($group.substring(3).indexOf(",")) + "`n"
            }
            #$output =  "$Username,$outputCont,$LastLogon,$logonCount,$membership"
            $Worksheet.cells.item($Row, 1) = "$Username"
            $Worksheet.cells.item($Row, 2) = "$outputCont"
            $Worksheet.cells.item($Row, 3) = $LastLogon
            $Worksheet.cells.item($Row, 4) = $membership
            #ac -path ADUIout.txt -value $output
            $Row++
        }
    }
}
$Workbook.saveas("H:\ADUI.xls")
$Workbookl.save()
$Excel.quit()