AD Export group members but with a twist?

This topic contains 8 replies, has 4 voices, and was last updated by Profile photo of Dan Potter Dan Potter 1 year, 8 months ago.

  • Author
    Posts
  • #21625
    Profile photo of Jake Sully
    Jake Sully
    Participant

    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 🙁

  • #21634
    Profile photo of Will Anderson
    Will Anderson
    Keymaster

    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.

  • #21684
    Profile photo of Jake Sully
    Jake Sully
    Participant

    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.

  • #21701
    Profile photo of Jack Neff
    Jack Neff
    Participant

    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.

  • #22334
    Profile photo of Jake Sully
    Jake Sully
    Participant

    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!

  • #22344
    Profile photo of Jack Neff
    Jack Neff
    Participant

    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
  • #23998
    Profile photo of Jake Sully
    Jake Sully
    Participant

    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

  • #24009
    Profile photo of Jack Neff
    Jack Neff
    Participant

    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.

  • #24019
    Profile photo of Dan Potter
    Dan Potter
    Participant

    -Filter {anr -eq $ADUser.Manager} would work too.

You must be logged in to reply to this topic.