Active Directory

Export Active Directory groups and members to a CSV file (with email addresses)

Greetings! 👀 After a comment on my initial post asking for user email addresses in the output, I ended up getting a bit confused for 4 hours while trying to achieve the goal (it was a Friday night so several beers were involved) 🍻 !!

When I started seeing the dreaded pages of red errors in my results I soon realised I was not thinking that objects other than users can be members of a group. Of course! So I need to cater for computers, nested groups and users with no email address.

The result is below and from initial testing it seems to work well. Key points:

  1. As with the original script, the CSV will output AD groups and members.
  2. Where a group has no members, the group name is output with ‘No Members’ in the members column (and also now in the EmailAddress column).
  3. The CSV has an ‘EmailAddress’ column added:
    • Where the member is a user and has an email address, the address is displayed.
    • Where the member is a user and does not have an address, ‘No Email Address’ is displayed.
    • Where the member is a computer, ‘Computer Object’ is displayed.
    • Where the member is a group, ‘Nested Group’ is displayed.

Voilà mes amis ! Code is below – as usual please comment if it helped or you made it better or it didn’t work for you ✌😃🤞. Thanks for coming, until nek tiya !

# export active directory groups and members to csv (also output empty groups with 'No Members' value)
# assumes run on 2012 R2 or newer domain controller or import of ActiveDirectory module
# 2022-04-02 - added logic to output email address column, catering for other object types that do not have addresses.

$allgroups = Get-ADGroup -Filter *

$result = foreach ( $group in $allgroups ) {

    $hash = @{GroupName=$group.SamAccountName;Member='';EmailAddress=''}
    $groupid = $group.DistinguishedName
    
    if ( $members = Get-ADGroupMember $groupid ) {
            
         foreach ( $member in $members ) {
            
                if ( $member.objectClass -eq 'user' ) {
                    $memberemail = (Get-ADUser -Properties mail $member.distinguishedName).mail
                        if ( $memberemail -ne $null ) {
                            $hash.Member = $member.Name
                            $hash.EmailAddress = $memberemail
                            New-Object psObject -Property $hash
                        }
                        else {
                            $memberemail = "No Email Address"
                            $hash.Member = $member.Name
                            $hash.EmailAddress = $memberemail
                            New-Object psObject -Property $hash
                        }       
                }       
                        else {                
                            if ( $member.objectClass -eq 'group' ) {
                                $memberemail = "Nested Group"
                                $hash.Member = $member.Name
                                $hash.EmailAddress = $memberemail
                                New-Object psObject -Property $hash
                            }
                            if ( $member.objectClass -eq 'computer' ) {
                                $memberemail = "Computer Object"
                                $hash.Member = $member.Name
                                $hash.EmailAddress = $memberemail
                                New-Object psObject -Property $hash
                            }
                        }
            }
    }
        else {
        $emailaddress = "No Members"
        $displayname = "No Members"
        $hash.Member = $displayname
        $hash.EmailAddress = $emailaddress
        New-Object psObject -Property $hash
    }
}

$result | Export-Csv -Path C:\temp\ActiveDirectoryGroupsAndMembers.csv -NoTypeInformation

# End

Remotely trigger delta AD Connect sync!

How often do you RDP to the AD Connect server to run a Delta Sync?

Yes I know, quite often right? And that is only once you find out which server it is running on. Especially If you are in new environments a lot or someone moved it since last time… sheesh thanks for telling us Dave!! 😭🤣

This script can be run from any Windows 10, 2016 or later endpoint… it will attempt to get the servername from AD then connect remotely and run a delta sync (we do some checks and have some messaging if things fail).

🙂👍 🙂

NOTE: Update 13/12/21 – when finding the AD Connect server, if you’ve already had more than one and someone hasn’t deleted the old computer account, both names will be returned causing the script to fail. Just run that bit of code first and delete any old accounts from the domain (or just replace the code with the server name)

First thing we do is run the following commands in an elevated PowerShell prompt to add the AD PowerShell module:

Install-PackageProvider Nuget -Force #justbecause

For Windows 10/11:
Add-WindowsCapability –online –Name Rsat.ActiveDirectory.DS-LDS.Tools~~~~0.0.1.0

For Windows 2016/2019:
Install-WindowsFeature RSAT-AD-PowerShell -Confirm:$false

Next, let’s make this easy to run with elevated rights by copying the script text into notepad and saving it into the c:\_scripts folder as “Force AD Connect Sync.ps1”

Then create a “Force AD Connect Sync.cmd” on your desktop with the following in it:

start powershell.exe -ExecutionPolicy Bypass -File "c:\_scripts\Force AD Connect Sync.ps1"

Now we can right-click on the cmd file and click ‘Run as Administrator”. Does the trick and time is life!

To find the server we use a method from easy365manager, and the link for enabling remoting is from faqforge. Thanks peoples!

Easy365manager:
https://www.easy365manager.com/how-to-identify-your-azure-ad-connect-server/

Faqforge:
https://www.faqforge.com/windows/create-powershell-session-remote-computer/

Here is the script – let me know if it worked or if it sucked and how you made it better! Until next time! Cheers, Simon 🍺 …oh and PS – if you want a great rundown on AD Connect, check out Adam’s post:

https://adamtheautomator.com/azure-ad-connect/#Install_Azure_AD_Connect

# force a delta sync to Azure AD

# load AD module
Try {
    Import-Module ActiveDirectory
}
    Catch {
        Write-Warning "Encountered a problem importing AD module."
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }
Write-Host -ForegroundColor Green "AD module loaded successfully."
Write-Host

Try {
    $ADConnectServer = Get-ADUser -LDAPFilter "(description=*configured to synchronize to tenant*)" -Properties description | % { $_.description.SubString(142, $_.description.IndexOf(" ", 142) - 142)}
}
    Catch {
        Write-Warning "Encountered a problem obtaining name of AD Connect server."
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }

Write-Host -ForegroundColor Green "Found AD Connect server $ADConnectServer!  Testing connection..."
Write-Host

Try {
    $session = New-PSSession -ComputerName $ADConnectServer -Authentication Default
    Enter-PSSession $ADConnectServer
}
    Catch {
        Write-Warning "Cannot connect to $ADConnectServer, please check remote connectivity." 
        Write-Warning "ref - https://www.faqforge.com/windows/create-powershell-session-remote-computer/"
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }

Write-Host -ForegroundColor Green "Connected to $ADConnectServer - Forcing a delta sync... one moment!"
Write-Host

Try {
    Start-ADSyncSyncCycle -PolicyType Delta
}
    Catch {
        Write-Warning "The command failed - either a sync is already in progress," 
        Write-Warning "or you are not a member of the 'ADSyncAdmins' group on the AD Connect server."
        Write-Host
        Read-Host "Press Enter to exit..."
        Exit
    }

Write-Host -ForegroundColor Green "Sync started successfully!"
Write-Host
Read-Host "Press Enter to exit..."

# clean up
Exit-PSSession
Remove-PSSession $session

Export Active Directory groups and members to CSV file

UPDATE 2022-04-02 – if you would like email addresses with the output, check out my new post at: https://www.howdoiuseacomputer.com/index.php/2022/04/02/export-active-directory-groups-and-members-to-a-csv-file-with-email-addresses/

# export active directory groups and members to csv (also output empty groups with 'No Members' value)
# assumes run on domain controller or import of ActiveDirectory module

$allgroups = Get-ADGroup -Filter *

$result = foreach ( $group in $allgroups ) {

    $hash = @{GroupName=$group.SamAccountName;Member=''}
    $groupid = $group.distinguishedname
    
    if ( $members = Get-ADGroupMember $groupid ) {
            
            foreach ( $member in $members ) {

                $hash.Member = $member.Name
                New-Object psObject -Property $hash
            }
    }
    else {
        $displayname = "No Members"
        $hash.Member = $displayname
        New-Object psObject -Property $hash
    }
}

$result | Export-Csv -Path C:\temp\ActiveDirectoryGroupsAndMembers.csv -NoTypeInformation

# End