Howdy
I am trying to create a script that will export the members of 6 AD security groups. I need to include properties such as the users sAMAccountName, Department, Title, and Managers “Display Name”.
The twist for me is that I need to export all of these groups into one Excel sheet or CSV if Excel is to much trouble, then email out the report. But also, the other issue I have is that I have no idea how to list the group name that the report is generated for in a column so that it would be something like:
Group1 sAMAccountName Department Title Manager
The Group1 name is the name of the group that these users are a member of, but I don’t want the names of “all groups” the users might be a member of.
Can anyone help me with this as I’m stuck essentially from the start to end 
Hey there Jake, does this help?
$ADGroup = Get-ADGroup -Identity '%Name of AD Group%'
$ADGroupMem = Get-ADGroupMember -Identity $ADGroup
ForEach [$Member in $ADGroupMem]{
$ADUser = $Member | Get-ADUser -Properties SamAccountName,Department,Title,Manager
[PSCustomObject]@{
GroupName = $Member.Name
SAMAccountName = $ADUser.SamAccountName
Department = $ADUser.Department
Title = $ADUser.Title
Manager = $ADUser.Manager
} | Export-Csv c:\scripts\ADGroupMem.csv -Append
}
Fixed by adding the append parameter.
Hey Will,
Thank you! What I’m trying to do is list the name of the “group” that the export is done for in a column so that you can see what group the user is a member of.
But the request is to not include “all groups” the user is a member of, only the specific group name I am running the export on.
So it would be for example something like:
Group1 UserA Sales “Manager’s Display Name”
Group1 UserB Marketing “Manager’s Display Name”
Group2 UserA Sales “Manager’s Display Name”
Group3 UserA IT “Manager’s Display Name”
I have to report on 7 groups, the request is to put all the group members in a single csv file, and include the group name in the column as I noted.
The -append does help, but I’m not sure how to get the group name to show up that is specified to export the users for, and also how to run this for 7 different groups, as well as to schedule it as a task that can be emailed, or at least saved to a file share.
I think Will typo’d the GroupName and got you a little confused by the results. It should have been GroupName = $ADGroup.Name. Here’s the Will’s script again with a few minor additions:
$Groups = 'Group1','Group2','Group3','Group4','Group5','Group6','Group7'
$FileName = "ADGroupMem_$(Get-Date -Format "yyyyMMddmmss")"
$SavePath = "\\servername\share\$FileName.csv"
Foreach ($Group in $Groups){
$ADGroup = Get-ADGroup -Identity $Group
$ADGroupMem = Get-ADGroupMember -Identity $ADGroup
ForEach ($Member in $ADGroupMem){
$ADUser = $Member | Get-ADUser -Properties SamAccountName,Department,Title,Manager
if ($ADUser.Manager){
$ManagerName = ($ADUser.Manager).Split(",") | Select-Object -Index 0 | ForEach-Object {$_ -replace "CN=",""}
} else {
$ManagerName = "None listed"
}
[PSCustomObject]@{
GroupName = $ADGroup.Name
SAMAccountName = $ADUser.SamAccountName
Department = $ADUser.Department
Title = $ADUser.Title
Manager = $ManagerName
} | Export-Csv -Path $SavePath -Append -NoTypeInformation
}
}
This will allow to you put all seven group names in and run it as a scheduled task. It’ll plop the timestamped csv file out onto a share of your choice for whoever you want to share it with.
Note: $ADUser.Manager returns a super long distinguished name so I used some crude string manipulation tricks to get just the alias so I didn’t have to do another Get-ADUser query.
Hi,
Thanks, this works great, but the manager column comes out with just: lastname\
I’ve messed with the string code and can’t get it right to leave the first name, can anyone help me out!
Thanks a lot, this is a fantastic learning site!
Sounds like your Manager names have a comma in them. Try changing line 14 to this:
$ManagerName = Get-ADUser -LDAPFilter "(DistinguishedName=$($ADUser.Manager))" | Select-Object -Expand DisplayName
Hi Jack,
I know I had this working and while out sick last week someone changed it as we have to include the DisplayName of the User now. That’s not what is causing the issue as I tried to remove that.
I went back to the original script you helped me with and I end up with the managers name coming back as lastname\
Here’s the error I get after replacing Line 14 with the new line you had sent:
Select-Object : Cannot process argument because the value of argument “obj” is null. Change the value of argument “obj” to a non-null value.
At Z:\scripts\Testing\mgmtreport.ps1:14 char:95
- … r.Manager))" | Select-Object -Expand DisplayName
I am also trying to email this out and I can do that using the simple method below, but I know there are better ways to format the report, can you point me in the right direction? I’ve tried some tests exporting to excel and haven’t had any luck.
$file = “Z:\scripts\testing\mgmtreport_$((Get-Date).ToString(‘MM-dd-yyyy’)).csv”
Send-MailMessage -SmtpServer mail.caltest.local -To jake@caltest.local -From AutoReports@caltest.local -Subject “Management Group Report” -Body “Attached is the report with all group members and details as requested.” -attachment $file
My bad the returned ADObject doesn’t contain the ‘DisplayName’ property by default I should have specified to include it in the query. Here’s line 14 again:
$ManagerName = Get-ADUser -LDAPFilter "(DistinguishedName=$($ADUser.Manager))" -Properties DisplayName | Select-Object -Expand DisplayName
When I need to pretty up my reports I use HTML. If your report is simple (less than 100 lines) and exchange security lets you embed HTML inside the email body that might just be the best way to go instead of sending it as an attachment because it’s lighter and easier to read on mobile devices. Don’s got a free ebook for creating HTML reports out on penflip I suggest taking a look…
[url]https://www.penflip.com/powershellorg/creating-html-reports-in-powershell[/url]
Or if you want to stick with sending a file here’s a good article by Ed Wilson for exporting the data to an excel spreadsheet and formatting with HTML:
[url]http://blogs.technet.com/b/heyscriptingguy/archive/2014/01/10/powershell-and-excel-fast-safe-and-reliable.aspx[/url]
Let me know if you still can’t get it working.
-Filter {anr -eq $ADUser.Manager} would work too.