Styjun

When I export my group data from exchange/AD I am left with the full domain name, including CN=, OU=, OU=, ect. Is there something I can change in my script to get it to export just the name, instead of having to go into excel and search and replace what I dont want?

#Written by Tekwhat 10-26-17
write-host "Group Memberships"
#Settings for file ouput
$fLocation = "D:Exchange ReportsO365 Reports"

import-module activedirectory

#Get OU
$OU = Read-Host -Prompt "Input the OU name to search: (0202 - Dev Bank)"

#create File to write report to:
$fName = $fLocation+$OU+" Group Memberships.csv"
$test = test-path $fName
 if ($test -eq $True)
 
 write-host "Removing Old File..." -ForeGroundColor Red
 Remove-Item $fName
 
 #Else
 #
 #New-Item $fName -type file
 #
Write-host "Creating New File..." -ForeGroundColor darkgreen
New-Item $fName -type file


$SearchBase = "OU=$OU,OU=Hosted Exchange Customers,DC=Domain,DC=local"
$ADGroups = Get-ADGroup -Filter * -SearchBase $SearchBase | % [ADSI]("LDAP://$_") 
 
$Members = @()
 
foreach ($ADGroup in $ADGroups)
              $Members += $ADGroup.Member 
 

$Members | Select-Object AdGroup, Member | Export-Csv -Path $fname -NoType
write-host "Your file is located at " $fname -ForegroundColor DarkGreen

The export of above gives something like this:

"All Employees","CN=Test X User,OU=OUname,OU=Hosted Exchange
Customers,DC=Domain,DC=local"

Which I have to go into excel and search replace "CN=" and "OU=OUname,OU=Hosted Exchange
Customers,DC=Domain,DC=local" to have usable data.

I apologize if I am assuming incorrectly, but I believe you are having trouble with the output of the member property. You may use something like the following:

$SearchBase = "OU=$OU,OU=Hosted Exchange Customers,DC=Domain,DC=local"
$ADGroups = Get-ADGroup -Filter * -SearchBase $SearchBase -Properties Member,CN

$Members = @()
foreach ($ADGroup in $ADGroups)
 $Members += $ADGroup.Member 

One potential effect of the code above is that you will have an duplicate ADGroup names for each member. If that is not intended, then things will need to be changed.

I maintained a majority of your code, so I will explain what I changed:

($_ | Select-String -pattern "(?<=CN=).*?(?=,OU=|,DC=)").matches.value

• 
  $_ refers to the current pipeline object from $ADGroup.Member
  


• 
  Select-String selects a string from the pipeline input based on a regex match in this case. The regex text is passed to the -pattern parameter.


• 
  "(?<=CN=).*?(?=,OU=|,DC=)" is the regex (regular expression).
  
  
  • 
    (?<=CN=): (?<=) is a positive lookbehind mechanism that looks for the string CN= behind the current position in the match.
  
  
  • 
    .*? matches as few as possible characters (.) except newline characters
  
  
  • 
    (?=,OU=|,DC=) uses the positive lookahead mechanism ((?=)) to find string ,OU= or (|) the string ,DC=.
  
  


• 
  .Matches is the Matches property of the Select-String output. It contains other properties that are useful for specifying regex capture groups or select from multiple matches. One such property is value, which contains the actual string match.


• The lookahead and lookbehind mechanisms do not capture any matches. They are merely positional to control where you are in the match sequence.

It seems like you are over engineering this, based on your end goal. Correct me if I am wrong. Yet, in looking at your code, and your end goal, this would be my take on it.

No need for Write-Host (except for the color stuff) just to send to the screen (Write-Output) is the default, so you don't specifically have to write that out either, but that's a style choice.

"Create OU based Group Memberships Report"

$fLocation = 'C:Temp'

# no need to do this as modules are auto imported since v3, but it does not hurt to have it here
# Import-Module -Name ActiveDirectory

# Use a GUI to provide an OU list to select from.
$OUName = Get-ADOrganizationalUnit -Filter '*' | 
Select-Object -Property Name, DistinguishedName | 
Out-GridView -Title 'Select the OU name to search' -PassThru

# Set a file name to use. 'spaces in files names are just bad', so remove them
$fName = "$(($OUName).Name)_OU_ADGroup_Membership.csv" -replace ' '

# Remove any report of the same name
If(Test-Path -Path "$fLocation$fName")
 
 Write-Warning -Message "A previous version of the report file is in the destination folder. Removing the file!"
 Remove-Item -Path "$fLocation$fName" -Force


# Collect OU, AD group and user data and create a new CSV file
Get-ADOrganizationalUnit -Identity $OUName.DistinguishedName | 
ForEach 
 ForEach
 $ADGroup = $PSItem.Name
 Get-ADGroupMember -Identity $ADGroup 


Write-Host "Your new report file is licated here: $fLocation$fName" -ForegroundColor Yellow
Import-Csv -Path "$fLocation$fName"

#Results

WARNING: A previous version of the report file is in the destination folder. Removing the file!
Your new report file is licated here: C:TempLabUsers_OU_ADGroup_Membership.csv

GroupName Name SamAccountName
--------- ---- --------------
... 
TestUsers Test User001 testuser001
...