Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

CSVDE will do what you're asking.

share|improve this answer
    
Thanks a bunch! I tried and it works fine. I also tried AD Explorer v1.3 from Sysinternals but the member's list is not displayed as I wanted. THanks! –  r0ca Mar 12 '10 at 17:04
    
AD Explorer is a great tool, but deadly slow in large domains (at least in my experience) –  squillman Mar 12 '10 at 17:44
    
CSVDE has one annoying shortcoming... You get the fields in whatever order they happen to exist on the source DC you grab them from. For dumping into Excel this is probably not an issue, but I had to go with Powershell for producing a dump that needed to be 100% in a specific format/order. –  Ryan Fisher Mar 12 '10 at 17:48

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()
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.