Your PowerGUI Community: Simplify your PowerShell related GUI and script editing needs.


group membership exporting to excel

This question is not answered
groups2.ps1
Hello, I'm attempting to output the members of all my groups in AD into an excel sheet. So far I have this (which I copied from an inventory program that I use). As of now, I'm testing only one group and I"m having two problems

First: It seems every time I run it I'm missing data from a cell. One time I will be missing the heading in row 1column 1, then next time I'll be missing the heading in row 2 column 2,then the next, all headings will be fine but no output will display or output will display with no headings.

Second: None of the output is appending to the excel file, I get one result listed several times.

Attached is my script so far.


All Replies
  • Script:

    $a = New-Object -comobject Excel.Application
    $a.visible = $True
    $RunDate  = (get-date).tostring("MM_dd_yyyy")
    $file = "c:\powershell\ad\groups.xlsx"
    $input = get-qadgroup "District A"


    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)

    $c.Cells.Item(1,1) = "Group"
    $c.Cells.Item(1,2) = "Member"

    #$d = $c.UsedRange

    $intRow = 2

    foreach ($group in $input)
    {
    $groupname = $input.name
    $members = $input.members

    $c.Cells.Item($intRow,1)  = $groupname

    foreach ($member in $members) {
    $c.Cells.Item($intRow,2) = $members

    $intRow++

    }
    }

    #$d.EntireColumn.AutoFit()

     
     
    #$b.SaveAs($file)
    #$file.Dispose()
    #$a.Quit()

    Results in excel:
    Group    Member
    District A    CN=store108,OU=Store Users,DC=qchek,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com
        CN=store108,OU=Store Users,DC=,DC=com

    This one user is all that is shown, it is in the group, but the other 12 users are different but are not outputting. I would appreciate ANY help, even a complete remod of the entire script, if you think I am doing it wrong. Thanks!

  • Hey TimTekk

    Maybe this can be of any help, you might have to change the searchroot tho!



    cls
    # Script to retrieve all groups users in an OU belongs to, including the nested groups.
    # All users in sub-OU's will also be presented.
    # Define the searchroot here, this is just an example
    $OU = 'domain.loc/users/external'

    # No change nescessary under this line, results will be presented in Excel
    $strPath="c:\temp\test002.xls"
    $a = new-object -comobject excel.application
    $a.Visible = $true
    if (Test-Path $strPath)
    {
    # Open the document
    $b = $a.WorkBooks.Open($strPath)
    $c = $b.Worksheets.Item(1)
    } else {
    # Create It
    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)
    $c.Cells.Item(1,1) = "User"
    $c.cells.item(1,2) = "Group"
    $d = $c.UsedRange
    $d.Interior.ColorIndex = 19
    $d.Font.ColorIndex = 11
    $d.Font.Bold = $True
    }
    $introw = $c.UsedRange.Rows.Count + 1
    $users = get-QADUser -SearchRoot $OU
    foreach ($user in $users){

    $use = Get-QADUser -Identity $user
    $free = $use.name

    $member = Get-QADMemberOf -Identity $user
    foreach ($memb in $member){
    $intcol = 2
    $mem = $memb.name
    $c.cells.item($introw, 1) = $free
    $c.cells.item($introw, 2) = $mem
    $introw++
    $membof = Get-QADMemberOf -Identity $memb
    foreach ($groep in $membof){
    $groepen = $groep.name
    if ($groepen -gt ''){
    $c.cells.item($introw, 1) = $free
    $c.cells.item($introw, 2) = $groepen
    $introw++}}
    }
    }Beer[/b]
  • That's close, but I'd rather search for the groups and list the members, instead of search for the users and list the groups they are a member of. It may sound weird, but it would just be easier for what I am trying to accomplish.
  • Sorry for the misunderstanding.

    I'm not abled to test it myself at the moment, but maybe this script (modded script of you) can be of any help:


    cls
    $a = New-Object -comobject Excel.Application
    $a.visible = $True
    $RunDate = (get-date).tostring("MM_dd_yyyy")
    $file = "c:\google\groups.xlsx"
    $groupname = "District A"
    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)

    $c.Cells.Item(1,1) = "Group"
    $c.Cells.Item(1,2) = "Member"
    $intRow = 2

    $c.Cells.Item($intRow,1) = $groupname
    $members = get-qadgroup $groupname | Get-QADGroupMember |select name
    foreach ($item in $members){
    $c.Cells.Item($intRow,2) = $item.Name

    $intRow++
    }

    Message was edited by: V-Apps removed one line
  • V-apps, awesome work! That's much closer to what I am trying to accomplish. My last question is, do you think I could do a search for the groups and then export them to excel. For example I want to get the members of all the groups that have the word 'district' in them....

    get-qadgroup *district*
  • You gotta try this, maybe there is an error in it, but i'm sure you will be able to correct it! :


    cls

    $a = New-Object -comobject Excel.Application
    $a.visible = $True
    $RunDate = (get-date).tostring("MM_dd_yyyy")
    $file = "c:\google\groups.xlsx"
    $groups = Get-QADGroup *district* | select name
    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)

    $c.Cells.Item(1,1) = "Group"
    $c.Cells.Item(1,2) = "Member"
    $intRow = 2
    foreach ($item in $groups){
    $c.Cells.Item($intRow,1) = $item.Name
    $members = get-qadgroup $item.Name | Get-QADGroupMember |select name
    foreach ($member in $members){
    $c.Cells.Item($intRow,2) = $member.Name
    $intRow++
    }}
  • AWESOME!! Thank you again V-apps!
  • no thanks, we are here to help each other!.
    If you need more stuff just let me know.
  • Hi,

    i am new to the world of powershell scritping but i have some background in VBS scripting around AD objects. i have a simler script to the one that v-apps posted in VBS that exports all groups with the word location in and then exports them to an excel spreadsheet but it puts each new group on its own worksheet and then name the worksheet the name of the group.

    could anyone help me convert V-apps script to do the same so i can retire my vbs script?
  • Since this thread is already marked as answered, you may want to start a new thread and reference this thread. There are many on this forum who would be glad to help you out.