script to export groups in a specific way

Hi everyone, we have a big domain with different clients with several users, groups, … etc. AD server = Srv 2012R2.

Now we have to make a plan of our AD infrastructure of the users and groups. Yes we have exported everything to csv files, but that seems not enough.

So we are looking for some script which can be used to do the following.

  1. export all AD groups and place them in a excel file. BUT every group has to be in a separated sheet in that specific workbook (of the AD groups).

  2. and they want in every sheet the members of that AD group.

Does anyone know if there is a kind of script which corresponds with our wish? Or how I can “write” it myself?

Many thanks for your help!!!

KR, Kurt

If it really has to be Excel you could try the cool module by Doug Finke ImportExcel.
But actually I would recommend something else. What would be wrong with CSV files? You could create on file for each group. That would make using these data much easier than Excel files.

As Olaf stated, ImportExcel is awesome!
Something like this should work…
| Get-ADGroup -Properties members |
foreach-Object -Begin {
$file = ‘c:\temp\Groups.xlsx’
} -Process {
$psitem.members | Export-Excel -Path $file -WorkSheetname $PSItem.Name


Wes’s script will get you most of the way there, you should also understand (and this is the case in every directory i’ve seen) that you will encounter nested ad groups.

instead of using get-adgroup, or get-adgroupmember, i would suggest the function from below. it will take a group as its input, and then generate a report of all objects that are a member of the group, and if the member is a group, it will gather those users as well