Powershell Script - Get a .csv of Groups, Email Addresses and Members?

[JRA]

Hi all,

 

I've dug the following out and it almost does what I need:

#// Start of script 
#// Get year and month for csv export file 
$DateTime = Get-Date -f "yyyy-MM" 

#// Set CSV file name 
$CSVFile = "C:\directoryemails\adgroups"+$DateTime+".csv" 

#// Create emy array for CSV data 
$CSVOutput = @() 

#// Get all AD groups in the domain 
$ADGroups = Get-ADGroup -Filter * 

#// Set progress bar variables 
$i=0 
$tot = $ADGroups.count 

foreach ($ADGroup in $ADGroups) { 
   #// Set up progress bar 
   $i++ 
   $status = "{0:N0}" -f ($i / $tot * 100) 
   Write-Progress -Activity "Exporting AD Groups" -status "Processing Group $i of $tot : $status% Completed" -PercentComplete ($i / $tot * 100) 

   #// Ensure Members variable is empty 
   $Members = "" 

   #// Get group members which are also groups and add to string 
   $MembersArr = Get-ADGroup -filter {Name -eq $ADGroup.Name} | Get-ADGroupMember |  select Name 
   if ($MembersArr) { 
       foreach ($Member in $MembersArr) { 
           $Members = $Members + "," + $Member.Name 
       } 
       $Members = $Members.Substring(1,($Members.Length) -1) 
   } 

   #// Set up hash table and add values 
   $HashTab = $NULL 
   $HashTab = [ordered]@{ 
       "Name" = $ADGroup.Name 
       "Category" = $ADGroup.GroupCategory 
       "Scope" = $ADGroup.GroupScope 
       "Members" = $Members 
   } 

   #// Add hash table to CSV data array 
   $CSVOutput += New-Object PSObject -Property $HashTab 
} 

#// Export to CSV files 
$CSVOutput | Sort-Object Name | Export-Csv $CSVFile -NoTypeInformation 

#// End of script

 

This will spit out a list of group name, category (security/distribution), scope (local/global) and members. I'd like to make some changes, which would be:

 

- Omit groups with no email address

- Add in the group's email address

- (Tasty option) append "mailto" (make clickable for the sake of emailing) the email field

- (Tasty option) put in a section which will give me the option of removing a group/line from the .csv file should I want. Say if I wanted to make the members invisible.

 

Anyone who knows this wizardry I'll be very grateful for any help. Thanks for reading.

Edited October 10, 2020 by JRA

[fordea]

This should cover all four requests:

#// Start of script
#//List the groups you wish to exclude from csv output
$groupsToExclude = @( 
  'TestGroup1', 
  'TestGroup2'
)
#// Get year and month for csv export file 
$DateTime = Get-Date -f "yyyy-MM"
 
#// Set CSV file name 
$CSVFile = "C:\directoryemails\adgroups"+$DateTime+".csv"  

#// Create emy array for CSV data 
$CSVOutput = @()  

#// Get all AD groups in the domain that have an email address and include their mail property. Exclude any groups defined at the top
$ADGroups = Get-ADGroup -Filter "mail -like '*'" -properties mail | Where {$_.name -notin $groupsToExclude}

#// Set progress bar variables 
$i=0 
$tot = $ADGroups.count  

foreach ($ADGroup in $ADGroups) { 
   #// Set up progress bar
   $i++ 
   $status = "{0:N0}" -f ($i / $tot * 100)
   Write-Progress -Activity "Exporting AD Groups" -status "Processing Group $i of $tot : $status% Completed" -PercentComplete ($i / $tot * 100)

   #// Ensure Members variable is empty 
   $Members = "" 

   #// Get group members which are also groups and add to string 
   $MembersArr = Get-ADGroup -filter {Name -eq $ADGroup.Name} | Get-ADGroupMember |  select Name 
   if ($MembersArr) { 
      foreach ($Member in $MembersArr) {  
          $Members = $Members + "," + $Member.Name  
      } 
      $Members = $Members.Substring(1,($Members.Length) -1) 
   } 

   #// Set up hash table and add values 
   $HashTab = $NULL 
   $HashTab = [ordered]@{ 
       "Name" = $ADGroup.Name 
       "Email" = 'mailto:{0}' -f $ADGroup.mail 
       "Category" = $ADGroup.GroupCategory 
       "Scope" = $ADGroup.GroupScope  
       "Members" = $Members 
   } 

   #// Add hash table to CSV data array 
   $CSVOutput += New-Object PSObject -Property $HashTab
}

#// Export to CSV files
$CSVOutput | Sort-Object Name | Export-Csv $CSVFile -NoTypeInformation

#// End of script

 

You can change what groups to exclude from the csv at the top by adding / removing them from the $groupsToExclude array

Edited October 13, 2020 by fordea

[JRA]

This should cover all four requests:

#// Start of script
#//List the groups you wish to exclude from csv output
$groupsToExclude = @( 
  'TestGroup1', 
  'TestGroup2'
)
#// Get year and month for csv export file 
$DateTime = Get-Date -f "yyyy-MM"
 
#// Set CSV file name 
$CSVFile = "C:\directoryemails\adgroups"+$DateTime+".csv"  

#// Create emy array for CSV data 
$CSVOutput = @()  

#// Get all AD groups in the domain that have an email address and include their mail property. Exclude any groups defined at the top
$ADGroups = Get-ADGroup -Filter "mail -like '*'" -properties mail | Where {$_.name -notin $groupsToExclude}

#// Set progress bar variables 
$i=0 
$tot = $ADGroups.count  

foreach ($ADGroup in $ADGroups) { 
   #// Set up progress bar
   $i++ 
   $status = "{0:N0}" -f ($i / $tot * 100)
   Write-Progress -Activity "Exporting AD Groups" -status "Processing Group $i of $tot : $status% Completed" -PercentComplete ($i / $tot * 100)

   #// Ensure Members variable is empty 
   $Members = "" 

   #// Get group members which are also groups and add to string 
   $MembersArr = Get-ADGroup -filter {Name -eq $ADGroup.Name} | Get-ADGroupMember |  select Name 
   if ($MembersArr) { 
      foreach ($Member in $MembersArr) {  
          $Members = $Members + "," + $Member.Name  
      } 
      $Members = $Members.Substring(1,($Members.Length) -1) 
   } 

   #// Set up hash table and add values 
   $HashTab = $NULL 
   $HashTab = [ordered]@{ 
       "Name" = $ADGroup.Name 
       "Email" = 'mailto:{0}' -f $ADGroup.mail 
       "Category" = $ADGroup.GroupCategory 
       "Scope" = $ADGroup.GroupScope  
       "Members" = $Members 
   } 

   #// Add hash table to CSV data array 
   $CSVOutput += New-Object PSObject -Property $HashTab
}

#// Export to CSV files
$CSVOutput | Sort-Object Name | Export-Csv $CSVFile -NoTypeInformation

#// End of script

 

You can change what groups to exclude from the csv at the top by adding / removing them from the $groupsToExclude array

 

Gosh I don't know how you made that but you're a genius. Thank you so much it's perfect!

[bilal604]

This should cover all four requests:

#// Start of script
#//List the groups you wish to exclude from csv output
$groupsToExclude = @( 
  'TestGroup1', 
  'TestGroup2'
)
#// Get year and month for csv export file 
$DateTime = Get-Date -f "yyyy-MM"
 
#// Set CSV file name 
$CSVFile = "C:\directoryemails\adgroups"+$DateTime+".csv"  

#// Create emy array for CSV data 
$CSVOutput = @()  

#// Get all AD groups in the domain that have an email address and include their mail property. Exclude any groups defined at the top
$ADGroups = Get-ADGroup -Filter "mail -like '*'" -properties mail | Where {$_.name -notin $groupsToExclude}

#// Set progress bar variables 
$i=0 
$tot = $ADGroups.count  

foreach ($ADGroup in $ADGroups) { 
   #// Set up progress bar
   $i++ 
   $status = "{0:N0}" -f ($i / $tot * 100)
   Write-Progress -Activity "Exporting AD Groups" -status "Processing Group $i of $tot : $status% Completed" -PercentComplete ($i / $tot * 100)

   #// Ensure Members variable is empty 
   $Members = "" 

   #// Get group members which are also groups and add to string 
   $MembersArr = Get-ADGroup -filter {Name -eq $ADGroup.Name} | Get-ADGroupMember |  select Name 
   if ($MembersArr) { 
      foreach ($Member in $MembersArr) {  
          $Members = $Members + "," + $Member.Name  
      } 
      $Members = $Members.Substring(1,($Members.Length) -1) 
   } 

   #// Set up hash table and add values 
   $HashTab = $NULL 
   $HashTab = [ordered]@{ 
       "Name" = $ADGroup.Name 
       "Email" = 'mailto:{0}' -f $ADGroup.mail 
       "Category" = $ADGroup.GroupCategory 
       "Scope" = $ADGroup.GroupScope  
       "Members" = $Members 
   } 

   #// Add hash table to CSV data array 
   $CSVOutput += New-Object PSObject -Property $HashTab
}

#// Export to CSV files
$CSVOutput | Sort-Object Name | Export-Csv $CSVFile -NoTypeInformation

#// End of script

 

You can change what groups to exclude from the csv at the top by adding / removing them from the $groupsToExclude array

 

 

that is great work , just little more if we can have members ;s email address as well ?