KB47997: How to extract user and group information from the Cloud Active Directory to a CSV file

From time to time, a Cloud customer may request a list of their users and associated groups, along with relevant information like whether the account is enabled, whether the password is expired, etc. This information can be extracted from the Active Directory and exported to a comma separated values (CSV) file via the below Windows PowerShell script:
 
Sample Code/Error
Import-Module ActiveDirectory
$report = @()
$allMSTRUsers = Get-ADUser -Filter * -searchBase "LDAP_SEARCH_BASE" -properties *
foreach ($user in $allMSTRUsers){
    $groups = $null
    foreach ($group in $user.memberOf){
        if($groups){ $groups += "; "}
        $groups += (Get-ADGroup $group).Name
        }
       
    $current = New-Object PSObject
    $current | Add-Member -MemberType NoteProperty -Name SamAccountName -Value $user.SamAccountName
    $current | Add-Member -MemberType NoteProperty -Name DisplayName -Value $user.DisplayName
    $current | Add-Member -MemberType NoteProperty -Name Enabled -Value $user.Enabled
    $current | Add-Member -MemberType NoteProperty -Name LockedOut -Value $user.LockedOut
    $current | Add-Member -MemberType NoteProperty -Name PasswordExpired  -Value $user.PasswordExpired
    $current | Add-Member -MemberType NoteProperty -Name PasswordLastSet -Value $user.PasswordLastSet
    $current | Add-Member -MemberType NoteProperty -Name EmailAddress -Value $user.EmailAddress  
    $current | Add-Member -MemberType NoteProperty -Name PasswordNeverExpires -Value $user.PasswordNeverExpires
    $current | Add-Member -MemberType NoteProperty -Name LastLogonDate -Value $user.LastLogonDate
    $current | Add-Member -MemberType NoteProperty -Name LastBadPasswordAttempt -Value $user.LastBadPasswordAttempt
    $current | Add-Member -MemberType NoteProperty -Name whenChanged -Value $user.whenChanged
    $current | Add-Member -MemberType NoteProperty -Name DistinguishedName -Value $user.DistinguishedName
    $current | Add-Member -MemberType NoteProperty -Name Groups -Value $groups
    $report += $current
    }
    $report | Export-Csv C:\OUTPUT_FILE_NAME.csv -NoTypeInformation
 
 To use the above script, take the following steps:

1. Replace "LDAP_SEARCH_BASE" in the above script with the appropriate search base for the customer. For example, if customer C603 requests the extract, the search base would be:
    
   
   Sample Code/Error
   OU=C603,OU=Compute,OU=London,DC=mstrci,DC=com
   
    
   
2. Replace "OUTPUT_FILE_NAME" in the above script with the desired file name of the extract. You can also specify the exact path of the file, but it must be a location that already exists. The default output is to the C drive.
3. If some of the properties are not required for the extract, they can be commented out by adding a # in front of the line. For example:
    
   
   Sample Code/Error
   #$current | Add-Member -MemberType NoteProperty -Name LastLogonDate -Value $user.LastLogonDate
   
    will leave out the user's last logon date from the extract.
    
   
4.  
   If additional attributes are required, they can be found by going to the Active Directory Administrative Center, going to the properties for a particular user for that customer, and selecting Profile > Attribute Editor. A list of all available attributes will be shown.
    
   
5.  
   Save the script to a filename like "SCRIPT_NAME.ps1", where ps1 is the extension for PowerShell scripts.
    
   
6. Run "Active Directory Module for Windows PowerShell" as an administrator.
7. Use the command line to navigate to the script location and run the script by using .\SCRIPT_NAME.ps1.
8. After the process is completed, the resultant CSV file will populate in the location specified in step 2.

Conversely, to get an extract of each group and users in that group, use the modified script below:
 
Sample Code/Error
Import-Module ActiveDirectory
$report = @()
$allMSTRGroups = Get-ADGroup -Filter * -searchBase "LDAP_SEARCH_BASE" -properties *
foreach ($group in $allMSTRGroups){
    $users = $null
    foreach ($user in $group.Members){
        if($users){ $users += "; "}
        $users += (Get-ADUser $user).Name
        }
      
    $current = New-Object PSObject
    $current | Add-Member -MemberType NoteProperty -Name Groups -Value $group.Name   
    $current | Add-Member -MemberType NoteProperty -Name Users -Value $users
    $report += $current
    }
    $report | Export-Csv C:\OUTPUT_FILE_NAME.csv -NoTypeInformation
 
Note: For the above script, the LDAP search base should have "OU=Groups" appended. For example, the search base for C603 would become:
 
Sample Code/Error
OU=Groups,OU=C603,OU=Compute,OU=London,DC=mstrci,DC=com